Table name as a parameter in a query

[Duncs]

On a Monday morning, I extract data from one of our sales systems
which is then imported into an Access database, and placed in a table
tblSalesData_ddmmyy. I then query against last weeks sales data and
this new sales data within Access, in order to produce overall sales
figures.

Both last weeks and this weeks tables are identical in structure, so
every week when I run the queries, I need to alter them to reflect the
new table names. For example, last week the query would use the
tables tblSalesData_241108 & tblSalesData_011208. This week, it will
use the tables tblSalesData_011208 & tblSalesData_081208. The query
is the same, regardless of what week it runs. The only thing that
changes, is the names of the tables.

So, my question is, can the query be modified to take the table names
as parameters, and then run the query against those two tables?

TIA

Duncs

 

[Stefan Hoffmann]

hi,

which is then imported into an Access database, and placed in a table
tblSalesData_ddmmyy.

Instead of storing data as metadata, add a field containing the import
date to your table.


mfG
--> stefan <--

 

[Duncs]

hi,


Instead of storing data as metadata, add a field containing the import
date to your table.

mfG
--> stefan <--

Stefan,

Many thanks for your response. The only problem that I can see with
this, is that I would end up with duplicate entries in the table.
Because of how our sales reporting works, I look for all transactions
in the current quarter that have a status of 'COMPLETED'. So, a sale
returned last week as completed, would show up in this weeks extract.
This would then in turn appear in the table twice, against two
different dates.

The idea of keeping each week separate, was so that we would have
greater control over the information.

TIA

Duncs

 

[Stefan Hoffmann]

hi,

Many thanks for your response. The only problem that I can see with
this, is that I would end up with duplicate entries in the table.

You need to expand your constraints and add the import date to them.
This is imho necessary as your combination of two tables for one
evaluation raises the same issue.

The idea of keeping each week separate, was so that we would have
greater control over the information.

I strongly recommend to think about this strategy.

Otherwise you use a query, e.g. qrySalesData as

SELECT * FROM tblSalesData_241108
UNION ALL
SELECT * FROM tblSalesData_011208

and later on you can modify it using

CurrentDb.QueryDefs.Item("qrySalesData").SQL = newSqlStatement



mfG
--> stefan <--