How do I start thinking about this multiple criteria query?

[Nate74]

Embarrassingly, I'm not even to the point where I can ask a specific question about the query(ies) I think I need.

Here's where I'm starting from and where I want to go... maybe it will make enough sense for somebody to point me in the right direction.

I have sales data that contains line items for every item sold over the past X number of years. For each line, there are six key attributes that I'm concerned with.

For simplicity here's a scaled down example of the data for each line.

PART, PART_FAM, TRANS_DATE, FAM_GROUP, TRANS_AMT, OVS_CODE

For each attribute, there are at least five possibilities.

I have been asked to find monthly sales trends on about 20 unique combinations of these various attributes. An example might be, the monthly sales totals for:


TRANS_DATE = 1/1/06 to 1/31/06
PART_FAM = PIN
FAM_GROUP = 01xx
OVS_CODE = 2

Then a SUM for the TRANS_AMT.


I've set up a query that can give me the information I'm looking for one month at a time, but I want to believe there is a way to have Access do some of the grunt work, rather than me having to change the variables one by one and copy/paste each result into my new file.

Is there some reading or previous posts I could review that might get me thinking about this in the right way?

Thanks in advance!

 

[Jon K]

Type/Paste this Totals Query in the SQL View of a new query (replacing with the correct table name):-

SELECT PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"mmm yyyy") AS [Month], Sum(TRANS_AMT) AS MonthlySales
FROM [TableName]
GROUP BY PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"mmm yyyy"), Format([TRANS_DATE],"yyyymm")
ORDER BY PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"yyyymm");


Run the query.
Since you have given us only the field names and one line of results, the query may not return exactly what you wanted. But it should give you an idea of how you can get the results you wanted.

I have been asked to find monthly sales trends on about 20 unique combinations of these various attributes.

If you need to filter some groups, you can add a Having Clause in the SQL statement e.g.

SELECT PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"mmm yyyy") AS [Month], Sum(TRANS_AMT) AS MonthlySales
FROM [TableName]
GROUP BY PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"mmm yyyy"), Format([TRANS_DATE],"yyyymm")
Having FAM_GROUP in ("0100", "0101") and OVS_CODE in (2, 3)
ORDER BY PART_FAM, FAM_GROUP, OVS_CODE, Format([TRANS_DATE],"yyyymm");

Hope it helps.
.

 

[Nate74]

Jon,
Thank you for taking the time to reply. This will take me a while to work through but again, I really do appriciate you taking the time to help.
Nate