1000s of queries

[carl.e]

Hi all first time posting on here. Have been learning access slowly and working to get better at it. My boss which I have been learning from has been using access for quite some time. We are both a bit stumped when it comes to the best way to do this database. We are trying to do a year by year month by month breakdown of our sales broken down by state. Ex. compare January 2011 sales for MO with January 2012 sales for MO. Since we will be emailing this report out we need to export it into excel. I do not mind doing more work now to save time down the line but I am having trouble coming up with an efficient way to set up this database.

What I am thinking is to create a bunch of separate queries. One that pulls in Jan 2011 sales for MO. Then one that pulls them in for Feb 2011 MO. ect. Then use the "external data" function in excel to link the queries to the right document. The main problem I am running into is I will end up with 1224 queries 12months x 2 years x 51 states (including dc). My main question is, is there a way to code something using vba or sql (not real good with either but can learn) so that I don't have to go in and edit or recreate the query 1224 times.

If someone can think of a better way to get this setup I am also open to all suggestions. The only other thing I can think of would be to create a query that returns Jan 2011 for all states and link that to their respectful excel document and then filter it by the state. The main problem with that is I will have to go through and filter 24 tabs per document x the 51 documents. Which gives me the same amount of work as creating the query.

If someone has worked through a similar situation at some point or happens to know of a better way to do this please let me know.

Thanks in advance.


Carl

[pbaldy]

You want to look at what are called parameter queries. You have one query; it gets parameters from the user. Most of us use a form to gather user input, so your query would look to the form for the state and date range. Presuming two text boxes for the date and a combo for the state, your date criteria looks like:

Between Forms!FormName.FirstTextbox And Forms!FormName.SecondTextbox

the state would look like:

Forms!FormName.ComboName

[June7]

Why does the email need to be Excel document? Will recipient need data to manipulate? Or will a PDF of report serve?

You want each month of each year for each state on separate sheets of workbook?

VBA code could be used to export filtered query to workbook. This could be automated in a loop to create 1,224-sheet workbook. Or dump all the data to one sheet and let recipient filter as they wish, unless the number of records will exceed 1,048,576 (Excel 2010 row limit).

[carl.e]

Thanks for the quick response. I have thought of having it prompt me every time but then I am manually entering the state and dates each time. Right now I have a dates table and just tell it the month to run it for and hard code it in instead of having it ask each time. I will be running this report every month so having to key all that in every month isn't ideal. If I am understanding you wrong then I am sorry but from the little bit I have played with parameter queries that is what I know.

[carl.e]

It doesn't have to be in an excel document a PDF will work also. I do want each month of each year on separate sheets of the workbook, and in a perfect world have 51 different workbooks. Right now we have all the raw data in one big group the main issue I am having is the best way to get it all filtered down. I do think VBA is probably the best option but need to read up more on how to make what I want work. If you or anyone that is good with VBA could point me in the right direction I would appreciate it.
Thanks,
Carl

[June7]

Might not need any code. Build a report that uses Grouping & Sorting functionality. Output to PDF. A 1,224-page PDF will probably be a large file.

[ssanfu]

I agree with Paul and June. Depending you your table structure and data, I can see one form, one query and one report.
If you want a separate report for each state or each state and each mth/year, maybe some code would be involved.

[qa12dx]

assuming u know group by, sum date format functions....

form1 - combo1.month1, combo2.year1
combo3.month2, combo4.year2
command_button-open.qry3

qry1 - date, state, sale, month:date_formated_month, year:date_formated_year(Criteria based on form1.combo1&2)
qry2 - date,state, sale, month:month_value_qry1, year:year_value_qry1 (for these two fields u can just pull the value from the date combo1&2 in form u r using to see report)(Criteria based on form1.combo3&4)
qry3 - state, month, sale.qry1, sale.qry2 (join qry1 and qry2 on month and year)

all these queries would be agregate queries, ie, groupby, sum
hope it helps