column for month todate, year todate and comparative number from last year

[edwardcga]

I am trying to write a query to extract product sales data. Columns are product code, qty, current month sales, ytd sales for current year and comparative number for last year. How do I write the query to pull data for current year and comparative period for last year. I will have a pararameter for user to enter the month end date e.g Mar 31, 2012 as 03/31/2012.
Also, anyone can show me what criteria should be for Month to date and year to date for current year and last year. I am new to ACCESS and know basic query.

Thanks

Edward

[Mihail]

Is not clear for me why you store the sales month while you can store the sales date ?
If you use date then you can show everything you wish:
- Sales before a certain date;
- Sales between certain dates;
- Sales in a certain year, month, day or, if you store dates including the time, even sales in a certain hour, minute, second;

and so on.

Take a look here before go forward with your database:
http://bytes.com/topic/access/insigh...ble-structures

[edwardcga]

Thanks for your reply. The scenario I described is the report column. The Table do have field for Invoice date, invoice no., unit price etc and linked to other related tables. I want to make a report to show sales by product showing month sales, YTD sales for current year and comparative period for last year. I would like to know how to write the query to feed data for the report.

Thanks

[Mihail]

I make this new post because no one else answer you.

I think I can see a solution for your problem but is based a lot on VBA and only a little bit on SQL (queries). Maybe because I have good skills in VBA but almost zero in SQL.

But first of all I need to know exactly what you have or what you can do (the questions are not about your skills; there are about your database).

1) Can you define a query ? (let's name it qProducts) with this fields: Product_ID , Product_Name
2) Can you define a query ? (let's name it qSales) with this fields: Product_ID , DateOfSale , Quantity

If you are able to do that inform me and we'll try together to develop a strategy.

[edwardcga]

@Mihail
Thanks for your response and patience. Yes I know how to define basic query. For 1) I will write the query as:
select PRODUCT_ID, PRODUCT_NAME FROM DATABASE_NAME WHERE PRODUCT_ID ="EXAMPLE"

2. SELECT PRODUCT_ID, DATEOFSALE, QUANTITY FROM DATABASE WHERE PRODUCT_ID ="SAMPLE"

In fact I have substantial experience in using crystal report using parameter,defining formulaes, use subreports etc. but rarely do reports in ACCESS. My experience with ACCESS mainly creation of database, define query for exporting data to excel for further analysis, I always want to advance my skill in ACCESS but mostly end up in writing crystal report as a result.

[Mihail]

I must repeat:
It is not about your skills. Is about how your database has been designed.
And from what I see in your last post you have a big problem here.
Take a very closer look to this: http://bytes.com/topic/access/insigh...ble-structures in order to understand why.

The IDs usually are long integers not texts.
Also they are the primary keys for tables.

So, the Product_ID can not be "EXAMPLE" or "SAMPLE" or, if is, you are in big trouble.

Can you post the database ?
See here http://bytes.com/forums/feedback/915...ase-other-work how to.