Newbie - Sum the total

[Alan]

I am using Accss not SQL.

ProductA sold 2 sets yesterday and 1 set today, Product B sold 3 sets
yesterday and 2 sets today. By using "while not rs.eof" I can only show the
records like that :

ProductA 2
ProductA 1
ProductB 3
ProductB 2

How to sum the total amount and display to screen like that :
ProductA 3
ProductB 5

Thks !

[mhb]

Hi,

Use this SELECT statement:

Select ProductNo, sum(Quantity) from <YourTableName> group by ProductNo

Regards

"Alan" <alan@neind.net> wrote in message
news:ukDFLwcbEHA.3596@tk2msftngp13.phx.gbl...

> I am using Accss not SQL.
>
> ProductA sold 2 sets yesterday and 1 set today, Product B sold 3 sets
> yesterday and 2 sets today. By using "while not rs.eof" I can only show

the

> records like that :
>
> ProductA 2
> ProductA 1
> ProductB 3
> ProductB 2
>
> How to sum the total amount and display to screen like that :
> ProductA 3
> ProductB 5
>
> Thks !
>
>

[Jeff Cochran]

On Tue, 20 Jul 2004 04:07:50 +0800, "Alan" <alan@neind.net> wrote:

>I am using Accss not SQL.
>
>ProductA sold 2 sets yesterday and 1 set today, Product B sold 3 sets
>yesterday and 2 sets today. By using "while not rs.eof" I can only show the
>records like that :
>
>ProductA 2
>ProductA 1
>ProductB 3
>ProductB 2
>
>How to sum the total amount and display to screen like that :
>ProductA 3
>ProductB 5

Do it in your qyery:

SELECT Products, Sum(QtySold) GROUP BY Products

Or whatever column names you have. Look in the online help for the
SUM function.

Jeff

[Alan]

Thks very much !

SQL = "Select ProductID , Sum(Qty) As DDD From WInvoice GROUP BY
roductID" -- No problem.

Meanwhile I need to display all the field, so I change to :

Mon = 7
SQL = "Select * , Sum(Qty) As DDD From WInvoice GROUP BY ProductID Having
month = " & Mon
While not rs.eof
A = rs("ProductID")
B = rs("Name") -- Each ProductID has a name in my mdb
C = rs("DDD")
D = rs("Date")
Wend

Is it possible to do this? Thanks very much !


"mhb" <mhb_shrf@hotmail.com> ¦b¶l¥ó
news:%23Z8vPFdbEHA.3728@TK2MSFTNGP10.phx.gbl ¤¤¼¶¼g...

> Hi,
>
> Use this SELECT statement:
>
> Select ProductNo, sum(Quantity) from <YourTableName> group by ProductNo
>
> Regards
>
> "Alan" <alan@neind.net> wrote in message
> news:ukDFLwcbEHA.3596@tk2msftngp13.phx.gbl...

> > I am using Accss not SQL.
> >
> > ProductA sold 2 sets yesterday and 1 set today, Product B sold 3 sets
> > yesterday and 2 sets today. By using "while not rs.eof" I can only show

> the

> > records like that :
> >
> > ProductA 2
> > ProductA 1
> > ProductB 3
> > ProductB 2
> >
> > How to sum the total amount and display to screen like that :
> > ProductA 3
> > ProductB 5
> >
> > Thks !
> >
> >

>
>

[Bob Barrows [MVP]]

Alan wrote:

> Thks very much !
>
> SQL = "Select ProductID , Sum(Qty) As DDD From WInvoice GROUP BY
> roductID" -- No problem.
>
> Meanwhile I need to display all the field, so I change to :
>
> Mon = 7
> SQL = "Select * , Sum(Qty) As DDD From WInvoice GROUP BY ProductID
> Having month = " & Mon
>
> Is it possible to do this? Thanks very much !
>
>

No, not as written. Here's why:

Say you have a table with two columns containing the following 4 rows of
data:

Col1 Col2
1 28
1 33
2 5
2 8

Now you decide to create a grouping query:
select Col1 From table Group By Col1
You would get these results:
1
2

Now you decide to add Col2:
select Col1,Col2 From table Group By Col1
Here are the results:
1 ?
2 ?

What do you replace the ?'s with? Do you see the problem?

Bottom line: You have to tell the query engine how to aggregate Col2 (min,
max,sum, avg,count, etc.) so that you wind up with a single row for each
value in Col1.

Even if Col2 contains the value 5 in every row, you still have to tell the
engine how to aggregate it. The query parser does not know what's in your
table: all it can see is that there is a column in the select list that
needs aggregation.


HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Alan]

Dear Bob, Ths for yr help .

Now, my problem is that, how to merge this two query together:

Month = 7
CID = A-Company

SQL = "Select * From Invoice where month = " & Month
SQL = SQL & " And CustomerID ='" & CID & "'"

First, I need to select the records of A-Company in July, Then

SQL = "Select ProductID , Sum(Qty) As DDD , First(ProductName) As EEE From
Invoice GROUP BY ProductID "

ProductID ProductName Qty
While not rs.eof
<%=rs("ProductID")%> <%=rs("EEE")%> <%=rs("DDD")%>

In my mdb, the data is as below
Product A 2 Harddisk July A-Company
Product A 5 Harddisk July A-Company
Product A 1 Harddisk June A-Company
Product B 3 cd-rom July A-Company
Product B 2 cd-rom July B-Company
Product B 2 cd-rom June B-Company

I hope the result should like that :
For A-Company , July
Product A 7 Harddisk July A-Company
Product B 3 cd-rom July A-Company

How to do that ? Pls help me



"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> ¦b¶l¥ó
news:eox48tkbEHA.1732@TK2MSFTNGP09.phx.gbl ¤¤¼¶¼g...

> Alan wrote:

> > Thks very much !
> >
> > SQL = "Select ProductID , Sum(Qty) As DDD From WInvoice GROUP BY
> > roductID" -- No problem.
> >
> > Meanwhile I need to display all the field, so I change to :
> >
> > Mon = 7
> > SQL = "Select * , Sum(Qty) As DDD From WInvoice GROUP BY ProductID
> > Having month = " & Mon
> >
> > Is it possible to do this? Thanks very much !
> >
> >

>
> No, not as written. Here's why:
>
> Say you have a table with two columns containing the following 4 rows of
> data:
>
> Col1 Col2
> 1 28
> 1 33
> 2 5
> 2 8
>
> Now you decide to create a grouping query:
> select Col1 From table Group By Col1
> You would get these results:
> 1
> 2
>
> Now you decide to add Col2:
> select Col1,Col2 From table Group By Col1
> Here are the results:
> 1 ?
> 2 ?
>
> What do you replace the ?'s with? Do you see the problem?
>
> Bottom line: You have to tell the query engine how to aggregate Col2 (min,
> max,sum, avg,count, etc.) so that you wind up with a single row for each
> value in Col1.
>
> Even if Col2 contains the value 5 in every row, you still have to tell the
> engine how to aggregate it. The query parser does not know what's in your
> table: all it can see is that there is a column in the select list that
> needs aggregation.
>
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>