Need Help:SQL Query...

[subratabera]

Hello all SQL experts...

Here is a problem for you. I hope and very much expect that you can solve it easily...

Here is the data...(Items are sold at diff. times. Thats why they are separated.)

Date --- ID --- ShopNo --- Item --- Qty Sold
23-Jan-07 --- SH --- SH1 --- Apple --- 348
23-Jan-07 --- SH --- SH1 --- Apple --- 386
23-Jan-07 --- SH --- SH1 --- Apple --- 370
23-Jan-07 --- SH --- SH1 --- Grape --- 720
23-Jan-07 --- SH --- SH1 --- Grape --- 56
23-Jan-07 --- SH --- SH1 --- Grape --- 268
23-Jan-07 --- ML --- ML1 --- Apple --- 846
23-Jan-07 --- ML --- ML1 --- Apple --- 762
23-Jan-07 --- ML --- ML1 --- Apple --- 576
23-Jan-07 --- ML --- ML1 --- Grape --- 22
23-Jan-07 --- ML --- ML1 --- Grape --- 333
23-Jan-07 --- ML --- ML1 --- Grape --- 997
24-Jan-07 --- SH --- SH1 --- Apple --- 721
24-Jan-07 --- SH --- SH1 --- Apple --- 978
24-Jan-07 --- SH --- SH1 --- Apple --- 773
24-Jan-07 --- SH --- SH1 --- Grape --- 565
24-Jan-07 --- SH --- SH1 --- Grape --- 350
24-Jan-07 --- SH --- SH1 --- Grape --- 175
24-Jan-07 --- ML --- ML1 --- Apple --- 795
24-Jan-07 --- ML --- ML1 --- Apple --- 804
24-Jan-07 --- ML --- ML1 --- Apple --- 132
24-Jan-07 --- ML --- ML1 --- Grape --- 188
24-Jan-07 --- ML --- ML1 --- Grape --- 234
24-Jan-07 --- ML --- ML1 --- Grape --- 873
25-Jan-07 --- SH --- SH1 --- Apple --- 802
25-Jan-07 --- SH --- SH1 --- Apple --- 747
25-Jan-07 --- SH --- SH1 --- Apple --- 468
25-Jan-07 --- SH --- SH1 --- Grape --- 229
25-Jan-07 --- SH --- SH1 --- Grape --- 758
25-Jan-07 --- SH --- SH1 --- Grape --- 798
25-Jan-07 --- ML --- ML1 --- Apple --- 611
25-Jan-07 --- ML --- ML1 --- Apple --- 785
25-Jan-07 --- ML --- ML1 --- Apple --- 450
25-Jan-07 --- ML --- ML1 --- Grape --- 317
25-Jan-07 --- ML --- ML1 --- Grape --- 596
25-Jan-07 --- ML --- ML1 --- Grape --- 961

I want the output as follows...(Date wise total sold qty)

Date --- ID --- ShopNo --- Item --- TotalQtySold
23-Jan-07 --- SH --- SH1 --- Apple --- 1104
23-Jan-07 --- SH --- SH1 --- Grape --- 1044
23-Jan-07 --- ML --- ML1 --- Apple --- 2184
23-Jan-07 --- ML --- ML1 --- Grape --- 1352
24-Jan-07 --- SH --- SH1 --- Apple --- 2472
24-Jan-07 --- SH --- SH1 --- Grape --- 1090
24-Jan-07 --- ML --- ML1 --- Apple --- 1731
24-Jan-07 --- ML --- ML1 --- Grape --- 1295
----------
----------
----------

I am also attaching an MSACCESS document which contains the above table. It will be very much helpful if you use its query feature to solve the problem.

Thanks in advance...

Subrata Bera.

P.S. -There will be no primary key in the table.

 

[mod-the-pc]

Here's the query

 

[subratabera]

Thankyou very much @mod-the-pc. You are really great. You have solved a big problem for me. I am searching for this answer for quite a long time...

Thankyou once again.

Subrata Bera

 

[ilugd]

I don't have access on my sys, but just out of curiosity sake, I guess it would have been a group by with sum. Am I right mod-the-pc?

 

[gaurav_indian]

Yes.Group by clause will be used for this query.

 

[mod-the-pc]

I don't have access on my sys, but just out of curiosity sake, I guess it would have been a group by with sum. Am I right mod-the-pc?

Yes, Perfect!

 

[subratabera]

Actually I am working on a database project (for my own use) which needs such query...I am trying to solve that problem for quite a long time but I am very weak in Access and SQL. But thanks to this forum and @mod-the-pc I got my answer...

 

[ilugd]

whats so difficult about access. Whatever language you are using for the project would be much more tougher. I would say sql is much easier. Have you tried reading a book on sql that explains the concepts?

 

[subratabera]

Actually I am designing the project entirely in ACCESS. This is a simple database project which gives me various results and prepare reports based on various queries. That's it...

BTW I am reading some books on ACCESS and SQL and hopefully learn them(as per my requirements) soon...