looking for sql query to extract output from the table

[learning_grexcel]

Hi,
I have a data with around 2,00,000 rows. The data is similar to the following :
item name >> date >> qty purchased
pen >> 1.03.2011 >> 50
pencil >> 02.03.2011 >> 70
pen >> 05.03.2011 >> 80
pencil >> 04.03.2011 >> 50
etc.
Now I want purchase during the month for particular items like the following :
pen >> 01.03. 2011 >> 50
>> 05.03.2011 >> 80
Total for March >> 130
pencil >> 02.03.2011 >> 70
pen >> 04.03.2011 >> 50
Total for March >> 120

Could you please tell me how to use SQL query to get the desired output?

 

[xenou]

Access isn't going to give you a "total for March" type of solution under normal circumstances. However, you should be able to group by months and products.

Something like:

SELECT 
    MyProductID, 
    Sum(MyQtySold) As SumOfQtySold, 
    Format(MyTransactionDate,"yyyy-mm") As MonthOfSale
FROM
   MyTransactionsTable
GROUP BY 
    MyProductID,
    Format(MyTransactionDate,"yyyy-mm")

You must use the real field names - I've made these up for you in absence of the actual details of your table structure.


ξ

 

[learning_grexcel]

Thanks friends
But here the new column "month of sale" doesn't show up day. It shows only month and year. Moreover, it shows only total sum of each items say (1) pen 130 (2) pencil 120.

I want breakdown of each date also along with total sum
Could you please help with this?

 

[xenou]

Some options are:

1. create an Access report (you probably can get something halfway decent even with the wizards).
2. create a daily total as above, then report it in Excel with subtotals.
3. use a pivot table in Access (not my favorite thing to do but possible).
4. use a form so you can have subtotal controls for the months.

I imagine there's probably other choices too. I'd probably use Excel for the reporting, or just have two queries - a daily summary and a monthly summary.

 

[learning_grexcel]

Thanks again. I will look into these options. I'm completely new so I have to work hard.

BTW: I don't need actually subtotal. What you have shown is enough for me but I want individual date to be seen in output and the grand total should be shown at the bottom of each item like the following

Pen 1st July 20
Pen 2nd July 30
Pen 3rd July 40
Total 90

Whereas, your code just gives me grand total but it doesn't explain how it came.