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-

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!

"Mari" wrote:

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

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mari" <(E-Mail Removed)> wrote in message
news:9A8CD7D1-0BAF-4A1E-B0F9-(E-Mail Removed)...
>
> 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-

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-


!!!

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Mari" <(E-Mail Removed)> wrote in message
> news:9A8CD7D1-0BAF-4A1E-B0F9-(E-Mail Removed)...
> >
> > 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-
>
>
>