Same query, different table.

[Ambre]

Hi,

I have a bunch of queries that I need to run against a data table to produce various analyses.

Every two weeks I get a fresh set of data, same structure, more rows.

I import this data into the database with a different table name because I need to retain the existing table(s) for trend analysis.

Is there a way to parameterise these queries so that at run time they each ask for the table name to query? A drop down list of table names would be great.

Ambre

 

[DCrake]

Why can't you append new data only and employ some type of batch input number to segregate different import routines? To answer your question though, no you can't.

Also what are you going to do when you have hundreds of tables? how much data are you importing?

 

[FoFa]

You would have to resort to VBA to update your query after asking for the table name.

 

[gemma-the-husky]

do it the OTHER way.

Rename the EXISTING tables, and import the new data into the STANDARD table names.

Then you can use all your existing queries against the new tables.

============
or something of that sort - it may be better to delete all the items in the existing tables, then you retain all your indexes etc.

==========
basically this sort of simple code will rename a table

tabledefs("sometable").name = newname

store a list of tables you want to rename in another table, and iterate this to pick up all the "volatile" tables.

===========
this is far easy than trying to change a query in code.

 

[FoFa]

Just a thought, why not just keep the data in one table, and add an import date column (or even multiple columns depending on your needs) to keep it all separated?
Typically we setup a single import table (per structure) and use that, than use a query to move the data out with additions (like Import date) in the final resting place.
Some we break apart by year to keep the size down to a manageable level.

 

[Ambre]

Hi,

Thanks to everyone who has replied.

I think I will go down the route of using a single table with an identifier on each record to indicate the batch of data it belongs to.

This still leaves me with the problem of selecting which 'batch' I want to analyse.

I have explored the idea of setting the criterium for selecting the batch to be the result of an entry in a form. eg. [Date] = [Forms]![frmGetDate].[txtDate] in my query. (My Date field is a styalised text field YYYYMMDD).

Being inexperienced with ACCESS, I am struggling to get this to work. When I run my query it prompts me to supply an entry for parameter '[Forms]![frmGetDate].[txtDate]', whereas I was hoping the query would recognise that it needs to run the macro that opens the form.

I fear I am getting into the realms of VBA.

Still hacking,
Ambre

 

[alice005]

You would have to resort to VBA to update your query after asking for the table name.

I agree with you. Ok

 

[gemma-the-husky]

Hi,

Thanks to everyone who has replied.

I think I will go down the route of using a single table with an identifier on each record to indicate the batch of data it belongs to.

This still leaves me with the problem of selecting which 'batch' I want to analyse.

I have explored the idea of setting the criterium for selecting the batch to be the result of an entry in a form. eg. [Date] = [Forms]![frmGetDate].[txtDate] in my query. (My Date field is a styalised text field YYYYMMDD).

Being inexperienced with ACCESS, I am struggling to get this to work. When I run my query it prompts me to supply an entry for parameter '[Forms]![frmGetDate].[txtDate]', whereas I was hoping the query would recognise that it needs to run the macro that opens the form.

I fear I am getting into the realms of VBA.

Still hacking,
Ambre

don't do that - if a date is a date, then make it a date type field.


anyway, i think you are on the right track

but what you need is in the [date] column of your query, just put, in the criteria row this bit.

[Forms]![frmGetDate]![txtDate]

this isnt a macro - its expecting you to have an open form called frmgetdate with a field called txtdate. the form MUST still be open. You cannot open the form, enter the date, and then close it. And on fiurther reflection, I think it needs a bang ! (not a dot) to identify the field.
then the query should select items where your [date] is equal to the date on the form.

===========
and note that some users will also warn you against using a field called [date] - its an access reserved word, and may bite you along the way. because you have it, be careful to include the [] brackets every time.


=============
AND when its working, really think about using true dates, instead of the constructed date you have. Access is full of usefull features for handling dates, all of which you are losing.

 

[Ambre]

Thank you for the tips. Such a lot to learn!

The queries do now work if I have the form open first as you pointed out.

Getting there. Sloooooooooooooooooowly