Table as Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I know VBA and SQL, but I'm new in Access.
I would like to do a dynamic query, but don't know how, so if anybody could
help me I appreciate it.

What I have: many tables with the same fields, but different data.
What I want: One query where you can choose the table from a list of tables,
combo box, anything.

Any Idea?

Thanks, Marcelo.
 
Build your SQL statement in VBA using the table names picked by the user from
a combo box that lists the relevant table names on the form?

If your table names are Table1, Table2, etc, you want a SQL string something
like this:

Select Table1.* from Table1 etc.
Select Table2.* from Table2 etc.

so you could build it like this:

Dim strSQL as String
strSQL = "Select " & TableName & ".* from " & TableName

This begs a more fundamental design question, however. Is there a pressing
reason why you have many tables with the same field names? In general, one
would use just one such table, with an extra field that contains the unique
data related to the current name of each of the tables. That is, instead of
having a table for each month - June, July, August, etc. - just have a single
table but add a DataMonth field that contains June, July, or August for each
entry. Just an idea...
 
Thanks Brian, It's perfect.

About your question, I'm using all these tables because I receive one of
each customer every month. I can join all of then, that's a good idea.

By the way, how can I join then, I mean, how can I add the data from two or
more tables in just one?

Thank you again!

Marcelo


"Brian" escreveu:
 
Do a backup first...

First of all, pick one table to be used as the master. Let's say you start
with May. Add an extra field for the month (assuming that each of your
existing tables was for a separate month). Don't call it "Month", though,
because this is a reserverd word in Access, like "Date". Call it MonthID or
something.

Run an update query for the master table to update its MonthID entry to
"May" . You should now see that all the records in the table have "May" in
the MonthID.

(You may want to actually store the first day of each month in the MonthID,
or next year's May entries will look like they are in the same month as this
year's May entries.)



Now, create an append query that appends * from your June table to the
master table. In the query builder, add another field that is just "June" ,
and set this to append to the MonthID field. Run the query. You should now
see May & June records in the table, with the respective MonthID entry.

Now, change two things in the query: draw the recrods from the July table
(still appending to the master table) and change the "June" to "July" in the
field entry for MasterID. Keep doing this until you are done with all the
excess tables.

When you are done, everything will be in one table, but each line will have
a MonthID that will identify which month's entry this is for.

Hint: if you want to ensure just ONE entry per customer per month, you can
go to the master table's design view, highlight the Customer and MonthID
fields together, and click the Primary Key button (assuming you don't have or
need another primary key). This will prevent more than one entry in this
table per month for a given customer.

You can still use an AutoNumber field to have a single entry that is unique
for each record; the AutoNumber field does not have to be the primary key.

Once you have all of this working, delete the individual month-related
tables, since all the information will be in the master table. It will be a
lot simpler to manage this way, and you can quite simply filter by the month.
 
Brian, just perfect! Thanks a lot!

Actually I have some Queries from all these tables, and this Append method
do not permit to append queries, at least I couldn't do this.

Any idea?


"Brian" escreveu:
 
Brian, I found my answer. What I would like to do was a UNION operation.

Thanks for all help!

"Brian" escreveu:
 
Back
Top