Query Using Variable Table Sources

[McCarthy_MF]

I've got a reasonably simple application that I want to dumb down further for
a novice user. We recieve quarterly data in three Excel files of which we
combine into a single tab and inport and keep as a separate table, "just in
case" we want to look at history.

Currently we name the tables "FYnnQn" and then the former user created
separate reports from each quarterly table.

I'd like to have a query ask for user input of "FYnnQn" and then report off
of that, instead of growing the number of reports and forcing the new user to
copy and edit reports.

Each quarter's data has the same fields, but does not have a period marker to
distinguish it from another period, except for the title of the table as we
currently are working. If there were an easy to append a single table and
automagically place a period marker, then the query/report would be easy.

So I'm looking some way to do either
1) a way to query variable table names
or
2) automagically adding the quarter period marker to the records during the
inport/append table process.

Or if there is an even smarter way, I'd welcome that input.


Thanks in advance.

Mike

 

[Guest]

--
Dave Hargis, Microsoft Access MVP

 

[Guest]

Oppps , hit the post button by mistake on the previous post, sorry.

One table with period identifier is definitly the correct way to do it.

You can create a form that allows the user to enter the period information
in a text box. Then add a calcuated field to your query that pulls that
value from the form and populates a field in your table:

Quarter: [Forms]![SomeFormName]![txtQuarter]

Or, if you are not using any forms and just need a way for the user to enter
it, do it like this:

Quarter: [Enter Quarter]

Then when you run the query, it will prompt for the value.

 

[McCarthy_MF via AccessMonster.com]

Thanks for your help. Buliding a single table is best, but it sure is messy
since they were inconsistant in the begining.

Mike

Oppps , hit the post button by mistake on the previous post, sorry.

One table with period identifier is definitly the correct way to do it.

You can create a form that allows the user to enter the period information
in a text box. Then add a calcuated field to your query that pulls that
value from the form and populates a field in your table:

Quarter: [Forms]![SomeFormName]![txtQuarter]

Or, if you are not using any forms and just need a way for the user to enter
it, do it like this:

Quarter: [Enter Quarter]

Then when you run the query, it will prompt for the value.

I've got a reasonably simple application that I want to dumb down further for
a novice user. We recieve quarterly data in three Excel files of which we

[quoted text clipped - 24 lines]

Mike