Newbie about grouping query

[Alan]

In my mdb, the data is as below :

ProductID Qty Name Month CID
------------------------------------------------------------------------
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

When I select "A-Company" and "July" from listbox and submit,
I hope the result should return like this :

A-Company. July
ProductID Qty Name
--------------------------------------
Product A 7 Harddisk (Grouped, the qty will sum up
automatically)
Product B 3 cd-rom

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

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

Then grouping the ProductID and sum the qty, also need to display the name :

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

Then use the script below :

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

It sound perfect, is it possible to merge two query together ?
How to do that ? Thks for everyone who help me out.

[Aaron [SQL Server MVP]]

SELECT ProductID, SUM(Qty), Name FROM tablename
WHERE Month='July' AND CID='A-Company'
GROUP BY ProductID, Name
ORDER BY ProductID

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




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

> In my mdb, the data is as below :
>
> ProductID Qty Name Month CID
> ------------------------------------------------------------------------
> 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
>
> When I select "A-Company" and "July" from listbox and submit,
> I hope the result should return like this :
>
> A-Company. July
> ProductID Qty Name
> --------------------------------------
> Product A 7 Harddisk (Grouped, the qty will sum up
> automatically)
> Product B 3 cd-rom
>
> First, I need to select the records of A-Company in July :
>
> SQL = "Select * From Invoice where month = " & Month
> SQL = SQL & " And CID ='" & CID & "'"
>
> Then grouping the ProductID and sum the qty, also need to display the name

:

>
> SQL = "Select ProductID , Sum(Qty) As DDD , First(Name) As EEE From
> Invoice GROUP BY ProductID "
>
> Then use the script below :
>
> ProductID Name Qty
> --------------------------------------------------------------------------

--

> ----------
> While not rs.eof
> <%=rs("ProductID")%> <%=rs("EEE")%> <%=rs("DDD")%>
>
> It sound perfect, is it possible to merge two query together ?
> How to do that ? Thks for everyone who help me out.
>
>