Table name as a parameter

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Ok, I'm fairly new to MS Access and have 12 tables I need to reference in a
query. Each table has the same type of information, just a different month
(i.e. each table is a capture of Monthly sales for a set group of items for
that month). What I was wanting to do is pass the table name as a parameter
the user defines to compare 1 table to another table. I need to be able to
compare a table the user defines to another table the user defines. Any
suggests is greatly appreciated.
 
The basic problem is your design. You should not have 12 tables, one for
each month. You should have one table with a field that identifies the
month. Your current design is going to make everything in your database more
difficult.

But, to answer your question, you will have to build your query
programmatically.
 
You can use a UNION query to read in all 12 tables at once. Look it up in
Access HELP. In this query, you can insert a new column containing 1 thru 12
to identify each table's data. Then you can read this union query in another
query or in VB code to perform your comparisons.

-Dorian
 
Make just ONE table, add ONE field, the date_time for the MONTH,


OneTableData
TheMonth Somefield SomeOtherField
2007.01.01 "haha" 123
2007.01.01 "hoho" 234
2007.02.01 "hihi" 345



Instead of two tables:

Jan2007
SomeField SomeOtherField
"haha" 123
"hoho" 234


and

Feb2007
SomeField SomeOtherField
"hihi" 345




Doing so, you are back in business, since now, the parameter is on a value
for a known field (TheMonth = #02/01/2007#, as example, the parameter is
the value #02/01/2007# ), rather than on SQL object name (table name or
field name).


You can always try to use 'ad hoc' ( 'just for the purpose' ) queries, if
the CONTEXT where you want to use it can accept a string representation of
an SQL statement: " SELECT * FROM " & supplyATableNameHere & " WHERE
.... "




Hoping it may help,
Vanderghast, Access MVP
 
OK, so I added a column called month to each Table to indentify which records
belong to which table when I created a union query to combine all 12 tables
into one. Couple quick questions

Table 1 contains these information (example)
Sub Desc. Can-Buy Did-Buy Month(new Column
for the month)
970 a book 500 30 Jan
980 pens 300 100 Jan

Table 2 contains this information (example)
Sub Desc. Can-Buy Did-Buy Month(new Column
for the month)
970 a book 530 150 Feb
980 pens 209 103 Feb

Table 3,4,5,6,7,8,9,10,11,12 etc. the only data that changes is the Can-buy,
did-buy amts for each Sub. Month changes between Table, but all tables are
linked threw a union.

Now that I have a union with all 12 tables

I want to design a query that looks at the union of all 12 tables and show
this:

Sub | Desc | 1st Months Can Buy | 1st Months Did Buy |2nd Months Can | 2nd Did

1st Month and 2nd Month will be inputed by the user using a parameter.

Is this possible to do with a query? I've tried with no success
 
OK, so I added a column called month to each Table to indentify which records
belong to which table when I created a union query to combine all 12 tables
into one. Couple quick questions

Table 1 contains these information (example)
Sub Desc. Can-Buy Did-Buy Month(new Column
for the month)
970 a book 500 30 Jan
980 pens 300 100 Jan

Table 2 contains this information (example)
Sub Desc. Can-Buy Did-Buy Month(new Column
for the month)
970 a book 530 150 Feb
980 pens 209 103 Feb

Table 3,4,5,6,7,8,9,10,11,12 etc. the only data that changes is the Can-buy,
did-buy amts for each Sub. Month changes between Table, but all tables are
linked threw a union.

Now that I have a union with all 12 tables

I want to design a query that looks at the union of all 12 tables and show
this:

Sub | Desc | 1st Months Can Buy | 1st Months Did Buy |2nd Months Can | 2nd Did

1st Month and 2nd Month will be inputed by the user using a parameter.

Is this possible to do with a query? I've tried with no success





- Show quoted text -
Click to expand...


The reason it has been suggested that you put the effective month on
each of the tables is so that you create a single table by appending
them all to one table as opposed to a union query.

But be that as it may whether you use the union query or the single
resulting table the what you want to do next is something like this.


create a sum query using the wizard and the combined table (or Union
query)
group by sub and Desc (puting them in the sequence that you want.

next field has action sum
field is: 1st Months Can Buy: iif(month = "Jan",[Tableorqueryname]!
[Can-buy],0)

next field has action sum
field is 1st Months Did Buy: iif(month = "Jan",[Tableorqueryname]!
[Did-buy],0)

etc through all the rest of the months.


=================

Almost any of the other approaches - using individual queries or even
joining the tables will have the problem that some months may not have
all matching records so you would either only end up with records that
had matching for EVERY month or if the first was used as the control
and used a left join or outer join then if the first month did not
have every possibility of sub and description some would be missing.

The Simplest way to get ALL sub/desc combinations no mater which month
did NOT have an entry is using the combined table or the union query.

Ron
 
Back
Top