Tim Thiessen
tim at irwingreenhouses.com
Fri Jan 16 11:17:53 CST 2004
Never mind. I figured it out. You have to set the parameters in the Query Parameter Dialong box. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Tim Thiessen Sent: Friday, January 16, 2004 10:44 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Grouping Query 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