Pivotables, date controls and other stuff

[dazedandconfused]

Can someone please, please help me on here?

If there are any aspiring `idiot´s guide` writers out there, now is the time to put some practice in!

I have almost completed my database, I just need to get the reporting section done.

I need to produce a monthly report on our casework which basically means totalling certain fields each month, which are exportable to access so I can produce comparison graphs.

I know that you cannot ´total´ text fields except for when you produce a form based on a pivotable. Is there anyway of producing a pivotable from a user-selected date range, and then being able to export that pivotable to Excel for the monthly report? Also, can I put a user-selected field filter in the pivotable?

So for example, my user wants to know how many cases there were for each subject in the month of March i.e. how many health enquiries, how many benefit enquiries, how many pension enquiries and so on. Each of these different types of enquiry are coded in a field-list called ´subject´. Therefore, can I produce a pivotable based on the selection of a date range and the field called ´subject´?

I am against a desperate deadline to get this done now. If anyone can spend time spelling this out in little words for me that would be great. This is the first time I´ve used Access and I ain´t done half bad, but this is completely stumping me!

Thanks in advance.

 

[Dennisk]

1) Create a form with From and To Dates enter some data.

2) Create a Filter Query that refers to the From and Two Date.

3) Use the filtered data as a recordset to create the Pivot Table.

4) Ensure that at least one of the queries involed in a Pivot table has the parameter properties set (otherwise the Xtab will produce and error).

5) When all is ok, create a button on the form to execute a transferSpreadsheet using the Xtab query.

 

[dazedandconfused]

Thanks for that. I´ll try and work out how to do it now.

Can I use a Macro for steps 3,4 and 5?

 

[Dennisk]

the only code involved is step 5, and I think that can be executed from a macro.

 

[dazedandconfused]

I think what I mean is...

Can a user click on a button saying ´monthly report´, be prompted to enter a date range and then have all the stuff produced and automatically exported to Excel?

You have to remember that I need to study the manual to try to fathom how to do all the other stuff as well!

But please be patient and thanks for your help...