Query using changing table names

[Wonderifican]

Hello,
I am using Access 2003 (limitations where i work on versions that can be used) and trying to create a create a query from a table names that change each month.
For example, table called EOM Costed Jan19 will be called EOM Feb19 and EOM Mar19 and so on. Of course the month and year is at the end of each table name.
The table structure remains the same each month, just that every month there is a new one. I would like to create a query that will prompt for the table name and another prompt for a user ID that can be run from any station as the database is stored on the network. I would also like to include about 8 fields from said table, those fields will be the same each time I run it.

I am not a programmer though I see quite a few VB codes in the program already, so it is not strange, just not savvy much on VB.



I can do basic queries and join tables, but this is out of my realm.


I should add that I am using Windows 7 with office 2007, but i can create the query in office 2003 access which is where the database was created on a 'work' pc.

Thanks for any help.

[June7]

Why is there a new table each month? Where is this data derived from?

This cannot be done with query object. Table and field names cannot be dynamic.

I expect will need VBA.

[CJ_London]

You need to clarify where these tables are stored. As June says, you will have to use dynamic queries built in VBA. The only other option is to rename the table as 'thisMonth' or similar. But that won't work if you want to compare 'thisMonth' with 'lastMonth'.

One thing you can do to simplify adjusting your query each month is to use aliasing of the table name - then you only need to change it once in the FROM part of the sql. e.g. instead of

SELECT [EOM Costed Jan19].fld1, [EOM Costed Jan19].fld1
FROM [EOM Costed Jan19]

use

SELECT thisMonth.fld1, thisMonth.fld1
FROM [EOM Costed Jan19] AS thisMonth

[Wonderifican]

The tables are stored in the same database. it's old and i can't explain why they set it up to have different names for each month. It has forms and other features built into it and I don't have a programmer to make any changes now. The data is brought into the Access database by running a few buttons built in and bam, tables are created and reports generated. That is all I can do.

The end goal is to have a report each month for any particular user ID with the 8 or so fields. I can then export into Excel as needed. Thanks

[CJ_London]

OK, so more complicated in that you already have reports being generated. As already stated you cannot 'prompt' for a table name in a query you need to use vba

I presume your database is a .mdb, not a .mde otherwise you are on to a non starter. Please confirm. It would also help to know what the query looks like so suggest copy paste the sql (go to query design and select the sql window)

[Wonderifican]

is it .mdb
Here is a query I created to return data from one table EOM MONTH AUG2019.

SELECT [EOM MONTH AUG2019].UserId, [EOM MONTH AUG2019].[UserName], [EOM MONTH AUG2019].Pub, [EOM MONTH AUG2019].[Pub Name], [EOM MONTH AUG2019].Type, [EOM MONTH AUG2019].TypeDesc, [EOM MONTH AUG2019].FunctionDesc, [EOM MONTH AUG2019].Quantity, [EOM MONTH AUG2019].[Unit Cost], [EOM MONTH AUG2019].TotalCost
FROM [EOM MONTH AUG2019]
WHERE ((([EOM MONTH AUG2019].UserId)=[Like 077 or 331]));

[June7]

Should not have table for each month. Data should be imported into a single table. That is real solution to this problem.

[CJ_London]

don't disagree, but OP says the db is old and works for them

[June7]

I wonder for how long and how many tables there are or do they delete them?

It works, apparently with some inconvenience now trying to eliminate.

How are queries, forms, reports currently dealing with changing table name?

So either redesign data structure or write VBA to handle the variable name or continue to manually change query SQL statement.

[davegri]

Attachment 40011

Wonder, see attached DB. If you're willing to use a form here's a way:



You could use comboboxes instead of textboxes to show the allowable names.