[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
>