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