Sharks in Access needed to answer this question :)

[David_Skov]

I have the following tables, queries and reports:

Table1 (with all data I use to create the queries and reports)

Table2 (Build upon 5 append queries)

Query1 (Retrieves data from table1 so that I can manipulate the data in table1

5 Append queries (Query2-Query6) that retrieves data from query1 and I manually update whenever new data is entered into table1. Ofcourse I delete table2 data before I append

Cross_tab_query (Retrieves the data from table2

Finally I use the Cross_tab_query to build my report upon.

Query1 has the follwing coloumns:

Market, SegmentID, Oms (revenue), Period

The periode is given by year and month number, so july 2004 is: 200407

In Query1 I set Criteria to '<200408' if I would like my report to show data from before August 2004.

If I do that, I have to delete data in table2 and refresh QWuery2-Query6. Then open my report to see the results.

What I would really like is to be able to open my report and then choose the period I would like to see and then table2 data is automatically deleted. When data deleted, Query2-Query6 is updated. Then my report shows the result.

Is that possible? If Yes (which I think it is) then how?

In advance tx ray:

 

[Andrew Fergus]

Hi David, I don't think I'm a shark but I think I can answer your question.

What I would really like is to be able to open my report and then choose the period I would like to see and then table2 data is automatically deleted. When data deleted, Query2-Query6 is updated. Then my report shows the result.

Is that possible? If Yes (which I think it is) then how?

Yes this is possible.

Rather than open the report and choose the period, create a dialogue box to choose the period, and use the period you selected to get the data you want in your report.

How you ask?

Create the dialogue box by creating a new form with 3 main objects - a box (either an unbound text box or a drop down list based on the existing periods) to enter / select the period, an OK button and a cancel button. Attach a macro to the cancel button to close the dialogue box. Attach a 2nd macro to the OK button to do the tasks you want done (see below).

The OK macro will run a new query (that you need to create) that will delete all of the records in table 2, then run the 5 queries you mentioned (the queries will filter the data based on the period you entered or selected in the dialogue box) & then open the report.

You might want to set "echo" and "setwarnings" to "no" at the start of the macro and reset both to "yes" at the end of the macro.

How do you get the form to pass the period through to the queries?

Have a read of this article - about half way down there is a section titled Creating a form to supply parameters to a query which will help you. Basically, you need to enter something like this into the criteria section under the "period" fields in the append queries :

<=[Forms]![Your Dialogue Box Form Name]![Your Dialogue Box Variable Name]

This will pass the value from the dialogue box through to the queries. But remember to use your actual form & variable names.

HTH, Andrew.

 

[David_Skov]

Ok will look into this asap