Return Sum in one field for all records with another field in comm

[Guest]

Hi there,

I have a query where each record is a merchandise order. Columns include
"ModelNo." and "#Units" (and order date, PO#, etc). I want to make another
column called "Total Units" where, for any given record, it will return the
total number of units ordered for the same MODEL as the current record.

like this:

MODEL UNITS TOTAL UNITS
105A 5 24
105A 9 24
105A 4 24
105A 6 24
108B 3 6
108B 3 6
312A 2 2

Can I do this with an expression in that column? Please be kind of specific,
as I'm not too savvy yet. Also, I am afraid of VB code(!).



Also, I have a quick question:

I created a query with the simple query wizard. The wizard asked me if I
wanted a "detail" or "summary" query. I originally chose "Detail" but now I
want to change it to "summary", since there is not "Totals" row in the design
grid. Is it possible to change after the query is made? I can not find the
property that seems to change this.


Thank you Thank you Thank you!

m-

 

[Guest]

ACTUALLY, I WAS ABLE TO FIGURE OUT THE FIRST PROBLEM FROM ANOTHER POST BY
DOING AN ADDITIONAL QUERY, GROUPING UNITS BY MODEL NO.

HOWEVER, I WOULD LIKE TO KNOW IF THERE IS A WAY TO GET THE DESIRED RESULT
WITHOUT THE ADDED QUERY, AND ALSO MY 2ND QUESTION PLEASE.

THANKS SO MUCH!

 

[Allen Browne]

Type this into a fresh column in the Field row of your query:
TotalUnits: (SELECT Sum([Units]) FROM Table1 AS Dupe
WHERE Dupe.Model = Table1.Model)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

To answer your 2nd question, depress the Totals button on the toolbar in
query design. The icon is an upper case sigma.

 

[Guest]

ok Allen, there is a BIIIIIIIG smile on my face. You absolutely rock!!
Both your answers were perfect and easy and I'm feelin' goood!

Thank you from the bottom of my heart!
m-


!!!

Type this into a fresh column in the Field row of your query:
TotalUnits: (SELECT Sum([Units]) FROM Table1 AS Dupe
WHERE Dupe.Model = Table1.Model)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

To answer your 2nd question, depress the Totals button on the toolbar in
query design. The icon is an upper case sigma.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query where each record is a merchandise order. Columns include
"ModelNo." and "#Units" (and order date, PO#, etc). I want to make another
column called "Total Units" where, for any given record, it will return
the
total number of units ordered for the same MODEL as the current record.

like this:

MODEL UNITS TOTAL UNITS
105A 5 24
105A 9 24
105A 4 24
105A 6 24
108B 3 6
108B 3 6
312A 2 2

Can I do this with an expression in that column? Please be kind of
specific,
as I'm not too savvy yet. Also, I am afraid of VB code(!).

Also, I have a quick question:

I created a query with the simple query wizard. The wizard asked me if I
wanted a "detail" or "summary" query. I originally chose "Detail" but now
I
want to change it to "summary", since there is not "Totals" row in the
design
grid. Is it possible to change after the query is made? I can not find the
property that seems to change this.


Thank you Thank you Thank you!

m-