Grouping in form?

[BrockLanders]

Hi all,
Is it possible to do a grouping in Forms like Reports? For example, I have daily sales data that I'd like to sum by the week, while showing all the daily detail as well and then have a grand total at the bottom. Getting the grand total is not a problem (Sum statements in footer), but I cannot figure out how to sum my data weekly, like in a Weekly section in a report.
The reason I'm not using a report is because reports are designed to fit on a certain screen or paper size. My data contains many fields that I simply cannot cram into one page on a report.

Thanks in advance for any help.

 

[PHV]

If you don't need an updatable recordset then use an union query based on 3 SELECT clauses (detail,week aggregate,grand total)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

No, I don't need an updatable recordset. I don't quite understand how to construct the Union query though. SHould i first make three Select queries and then combine them in one union query and use that as my control source in the form?

Thanks much.

 

[PHV]

Could you post the SQL code of the detail query with the fields type (text,num or date) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

This code would give me all the potential data:

SELECT [HEB July 05].Store, Sum([HEB July 05].SalesQty) AS SumOfSalesQty, Sum([HEB July 05].SalesWhsl) AS SumOfSalesWhsl, tblDates.Format_date
FROM [HEB July 05] INNER JOIN tblDates ON [HEB July 05].EndDate = tblDates.Mainframe_date
GROUP BY [HEB July 05].Store, tblDates.Format_date;

Thanks

 

[PHV]

What are the data types of Store, EndDate and Format_date ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

Oops, sorry about that. Store and EndDate are Text and Format_date is Date. EndDate is yyyymmdd so for grouping purposes I connected it to the table tblDates.

Thanks

 

[PHV]

You may try something like this:
SELECT H.Store, Sum(H.SalesQty) AS SumOfSalesQty, Sum(H.SalesWhsl) AS SumOfSalesWhsl, D.Format_date & '' As Period, H.EndDate & '0' AS SortId
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store, D.Format_date, H.EndDate
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), Format(D.Format_date,'yyyy \Weekww'), Max(H.EndDate) & '1'
GROUP BY H.Store, Format(D.Format_date,'yyyy \Weekww')
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), 'Grand Total', Max(H.EndDate) & '2'
GROUP BY H.Store
ORDER BY 1, 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

When I try to run the query i get the following error:
"Syntax error (missing operator) in query expression 'Max(H.EndDate) & '1'
GROUP BY H.Store'."
I've looked at the code a million times and can't find any syntax errors. Any ideas?

Thanks for all your help with this PHV!

 

[PHV]

Perhaps this ?
You may try something like this:
SELECT H.Store, Sum(H.SalesQty) AS SumOfSalesQty, Sum(H.SalesWhsl) AS SumOfSalesWhsl, D.Format_date & '' As Period, H.EndDate & '0' AS SortId
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store, D.Format_date, H.EndDate
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), Format(D.Format_date,'yyyy \Weekww'), Max(H.EndDate & '1')
GROUP BY H.Store, Format(D.Format_date,'yyyy \Weekww')
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), 'Grand Total', Max(H.EndDate & '2')
GROUP BY H.Store
ORDER BY 1, 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

PHV, I tried the new code and got the same error. Is this query suppossed to give me the three sets of numbers, detail, weekly, monthly?

Thanks again

 

[PHV]

Can you please post the EXACT sql code you tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BrockLanders]

I just changed the two fields that are being summed - SalesQty and SalesWhsl to other number fields.

SELECT H.Store, Sum(H.BegInvQty) AS SumOfBegInvQty, Sum(H.BegInvWhsl) AS SumOfBegInvWhsl, D.Format_date & '' As Period, H.EndDate & '0' AS SortId
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store, D.Format_date, H.EndDate
UNION SELECT H.Store, Sum(H.BegInvQty), Sum(H.BegInvWhsl), Format(D.Format_date,'yyyy \Weekww'), Max(H.EndDate & '1')
GROUP BY H.Store, Format(D.Format_date,'yyyy \Weekww')
UNION SELECT H.Store, Sum(H.BegInvQty), Sum(H.BegInvWhsl), 'Grand Total', Max(H.EndDate & '2')
GROUP BY H.Store
ORDER BY 1, 5;

 

[PHV]

OOps, sorry. (Lack of 2 FROM clauses, my bad):
SELECT H.Store, Sum(H.SalesQty) AS SumOfSalesQty, Sum(H.SalesWhsl) AS SumOfSalesWhsl, D.Format_date & '' As Period, H.EndDate & '0' AS SortId
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store, D.Format_date, H.EndDate
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), Format(D.Format_date,'yyyy \Weekww'), Max(H.EndDate) & '1'
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store, Format(D.Format_date,'yyyy \Weekww')
UNION SELECT H.Store, Sum(H.SalesQty), Sum(H.SalesWhsl), 'Grand Total', Max(H.EndDate) & '2'
FROM [HEB July 05] AS H INNER JOIN tblDates AS D ON H.EndDate = D.Mainframe_date
GROUP BY H.Store
ORDER BY 1, 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886