[AccessD] Grouping Query

Tim Thiessen tim at irwingreenhouses.com
Fri Jan 16 10:44:29 CST 2004


Thanks Stephen, I was not aware of the Partition Function.  It works great.

One other question.  I am trying to use date parameters in the beginning
query that looks at a form with date selector text boxes.  The crosstab
query then gives an error that it does not recognize the text boxes as a
valid field name or expression.  The beginning query runs just fine using
this criteria.  If I use the expression "Between #1/1/03# And #12/31/03#" as
the criteria, the crosstab runs just fine.  Any ideas?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stephen Bond
Sent: Thursday, January 15, 2004 5:59 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Grouping Query


Tim, try adding a field to the query and use the Partition function.  This
will do the grouping for you nicely.  New field looks something like this:

  PaidGroup: Partition([DatePaid],0,31,7)

then your CrossTab query would have the 'PaidGroup' field as the column
heading.

HTH

Stephen Bond 
Otatara, South Island, New Zealand 



> -----Original Message-----
> From: Tim Thiessen [mailto:tim at irwingreenhouses.com]
> Sent: Friday, 16 January 2004 10:30 a.m.
> To: accessD at databaseadvisors.com
> Subject: [AccessD] Grouping Query
> 
> 
> Hi Group,
> 
> I have a query that returns the following:
> 
> PaymentTerms		DatePaid	CountOfPaid
> COD			1			5
> COD			2			3
> COD			4			8
> COD			14			4
> COD			16			12
> Net 10-EOM		1			3
> Net 10-EOM		3			10
> Net 10-EOM		5			15
> Net 10-EOM		10			21
> 
> DatePaid is the day of the month in which payments are made.  
> CountOfPaid is
> a count of the number of payments made on particular date.  
> What I need to
> do is to be able to group this data into the following:
> 
> PaymentTerms		Paid1-6		Paid7-13	
> Paid14-20	Paid21-27	Paid28-31
> COD			16		16		0	
> 	0		0
> Net10-EOM		28		21		0	
> 	0		0
> 
> I know this is a cross tab query but how do I get the 
> grouping in the first
> place?
> 
> Tim Thiessen
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list