Multiple group levels and aggregate function for text field

[Guest]

I just can't find a solution online for multiple groups

Here is the structure

TABLE1

MODEL / COMMENTS

12345 / memotextaa
11035 / memotextbb
11035A / memotextc
52954 / memotextddd
62753 / memotexteee
62753L / memotextf
62753EJ / memotext
21167 / memotexthh
999068 / memotexti
752093 / memotextj
99999623 / memotext


TABLE2

MODEL / DELIVER BY / UNITS / BUYER

12345 / 11-25-06 / 100 / Maxwells
12345 / 12-28-06 / 200 / Maxwells
11035 / 02-14-07 / 150 / Maxwells
11035 / 01-25-07 / 200 / Acme
11035 / 03-25-07 / 200 / Jackson
11035 / 11-29-06 / 400 / Acme
11035 / 04-29-07 / 200 / Maxwells
11035 / 01-25-07 / 150 / Maxwells
11035A / 06-25-07 / 200 / Maxwells
11035A / 01-29-07 / 100 / Acme
52954 / 11-25-06 / 200 / Maxwells
62753 / 11-29-06 / 300 / Acme
62753 / 11-25-06 / 900 / Stonewall
62753L / 02-28-07 / 500 / Maxwells
62753EJ / 11-25-06 / 100 / Maxwells
21167 / 02-14-07 / 100 / Maxwells
21167 / 02-18-07 / 100 / Maxwells
999068 / 11-26-06 / 100 / Maxwells
752093 / 12-05-06 / 800 / Maxwells
9999623 / 11-25-06 / 100 / Maxwells


I currently have a query that calculates sum units per model:

sumUnits: Format((SELECT Sum([units]) FROM table2 AS Dupe WHERE Dupe.model =
table2.model),"#,#00;0;0")

QUERY

MODEL / COMMENTS / DELIVER BY / SUM UNITS BY MODEL / UNITS / BUYER

12345 / memotextaa / 11-25-06 / 300 / 100 / Maxwells
12345 / memotextaa / 12-28-06 / 300 / 200 / Maxwells
11035 / memotextbb / 02-14-07 / 1300 / 150 / Maxwells
11035 / memotextbb / 01-25-07 / 1300 / 200 / Acme
11035 / memotextbb / 03-25-07 / 1300 / 200 / Jackson
11035 / memotextbb / 11-29-06 / 1300 / 400 / Acme
11035 / memotextbb / 04-29-07 / 1300 / 200 / Maxwells
11035 / memotextbb / 01-25-07 / 1300 / 150 / Maxwells
11035A / memotextc / 06-25-07 / 300 / 200 / Maxwells
11035A / memotextc / 01-29-07 / 300 / 100 / Acme
52954 / memotextdd / 11-25-06 / 200 / 200 / Maxwells
62753 / memotextee / 11-29-06 / 1200 / 300 / Acme
62753 / memotextee / 11-25-06 / 1200 / 900 / Stonewall
62753L / memotextf / 02-28-07 / 500 / 500 / Maxwells
62753EJ / memotext / 11-25-06 / 100 / 100 / Maxwells
21167 / memotexthh / 02-14-07 / 200 / 100 / Maxwells
21167 / memotexthh / 02-18-07 / 200 / 100 / Maxwells
999068 / memotexti / 11-26-06 / 100 / 100 / Maxwells
752093 / memotextj / 12-05-06 / 800 / 800 / Maxwells
9999623 / memotext / 11-25-06 / 100 / 100 / Maxwells




I also have a report grouped by Model sorted ascending, then sorted by due
date ascending
All field controls are in the model header and nothing in detail section so
that
the report shows only the first due order per model.

CURRENTREPORT

MODEL / COMMENTS / DELIVER BY / SUM UNITS BY MODEL / UNITS / BUYER

12345 / memotextab / 11-25-06 / 300 / 100 / Maxwells

11035 / memotextgh / 01-25-07 / 1300 / 200 / Maxwells

11035A / memotexto / 06-25-07 / 300 / 200 / Maxwells

52954 / memotextqr / 11-25-06 / 200 / 200 / Maxwells

62753 / memotextuv / 11-25-06 / 1200 / 900 / Stonewall

62753L / memotextw / 02-28-07 / 500 / 500 / Maxwells

62753EJ / memotext / 11-25-06 / 100 / 100 / Maxwells

21167 / memotextxy / 02-14-07 / 200 / 100 / Maxwells

999068 / memotextb / 11-26-06 / 100 / 100 / Maxwells

752093 / memotextc / 12-05-06 / 800 / 800 / Maxwells

9999623 / memotext / 11-25-06 / 100 / 100 / Maxwells


I need the report to also return records of the first due date for each of
multiple buyers.
The buyer with the earliest due date should show at the top and the model
shows only once.



MODEL / COMMENTS / DELIVER BY / SUM UNITS BY MODEL / UNITS / BUYER
12345 / memotextab / 11-25-06 / 300 / 100 / Maxwells

11035 / memotextgh / 01-25-07 / 1300 / 200 / Maxwells (largerst units for
the duplicate dates)
--------memotextij / 11-29-06 / 1300 / 600 / Acme
--------memotextgh / 03-25-07 / 1300 / 200 / Jackson

11035A / memotexto / 06-25-07 / 300 / 200 / Maxwells
---------memotextp / 01-29-07 / 300 / 100 / Acme

52954 / memotextqr / 11-25-06 / 200 / 200 / Maxwells

62753 / memotextuv / 11-25-06 / 1200 / 900 / Stonewall
--------memotextst / 11-29-06 / 1200 / 300 / Acme

62753L / memotextw / 02-28-07 / 500 / 500 / Maxwells

62753EJ / memotext / 11-25-06 / 100 / 100 / Maxwells

21167 / memotextxy / 02-14-07 / 200 / 100 / Maxwells

999068 / memotextb / 11-26-06 / 100 / 100 / Maxwells

752093 / memotextc / 12-05-06 / 800 / 800 / Maxwells

9999623 / memotext / 11-25-06 / 100 / 100 / Maxwells



I have tried using aggregate functions in a query, but am stumped due to
erratic format of the text-based model number.

I know this is a complicated question. sorry!

m-

 

[Guest]

Your test data provided doesn't match up to the report sample you gave.
But I'll try to help answer your question as best I can understand.

I recreated the tables you describe and imported in the test data given.

I didn't understand your first query format so I created 2 queries as follows.
The first provides the sum and the second populates the report.

QuerySumUnits:

SELECT Sum(Units) AS SumUnits, ModelID
FROM ModelDelivery
GROUP BY ModelID;

QueryReportData:

SELECT Models.ModelID, Models.Comments, ModelDelivery.DeliverBy,
QuerySumUnits.SumUnits, ModelDelivery.Units, ModelDelivery.Buyer
FROM (ModelDelivery RIGHT JOIN Models ON ModelDelivery.ModelID =
Models.ModelID) LEFT JOIN QuerySumUnits ON ModelDelivery.ModelID =
QuerySumUnits.ModelID;

I created a report using query QueryReportData as datasource.
Then I defined the following under sorting and grouping:

Group on Model (grouping level 0);
Group on Buyer (grouping level 1); and
sort on Delivery by Date (ascending).

The first 2 have display headers property set to yes.

In the Model ID header section, I added text boxes to display
ModelID, DeliverBy Date, Comments, SumUnits, Units, Buyer
as you stated in your problem description.

Then in the Buyer header section, I added add text boxes to display
DeliverBy Date, Comments, SumUnits, Units, Buyer
-- omit ModelID text box and align other boxes under corresponding
box in Model Header section. In my opinion, you do not need
the comments, or SumUnits in this section as it is also redundant data.

Then I created event procedure on format for both header sections as follows:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
rowcount = 0
End Sub

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
rowcount = rowcount + 1
If rowcount > 1 Then
Me.GroupHeader1.Visible = True
Else
Me.GroupHeader1.Visible = False
End If
End Sub

Here is sample of report based on test data you provided.

Model Delivery Report


ModelID Deliver By Comments SumUnits Units Buyer

11035 11/29/2006 memotextbb 1300 400 Acme

3/25/2007 memotextbb 1300 200 Jackson
1/25/2007 memotextbb 1300 150 Maxwells

11035A 1/29/2007 memotextc 300 100 Acme

6/25/2007 memotextc 300 200 Maxwells

12345 11/25/2006 memotextaa 300 100 Maxwells

21167 2/14/2007 memotexthh 200 100 Maxwells

52954 11/25/2006 memotextddd 200 200 Maxwells

62753 11/29/2006 memotexteee 1200 300 Acme

11/25/2006 memotexteee 1200 900 Stonewall

62753EJ 11/25/2006 memotextG 100 100 Maxwells

62753L 2/28/2007 memotextf 500 500 Maxwells

752093 12/5/2006 memotextj 800 800 Maxwells

999068 11/26/2006 memotexti 100 100 Maxwells

99999623 11/25/2006 memotextK 100 100 Maxwells


Monday, November 13, 2006

This is not 100% solution since you need data sorted by Delivery Date and
this is sorted by Model and Buyer but maybe you can tweek to correct that
part.

Hope this helps...

 

[Guest]

I never thanked you for your help Joy. Thank you for your time and effort.
The most important thing I wanted to do was to list the Minimum date of Each
buyer Per Model on the report.

I did eventually find my answer in cascading queries. I ran a query to find
the minimum date from my existing query (that joined the two tables). then I
did a third query containing fields from my first query and second query,
inner joining the "model" fields in the first two queries, and inner joining
"deliverBy" from the first query and "minOfDeliverBy" from the second.

Hope you had a great holiday, and thanks again!

m-