Querying Multiple Tables

[hermanko]

Hi all,

I am still quite new at Access so if there is a solution that does not
require intense coding in VB i'd prefer that soln, unless VB is
absolutely necessary.

Description:

I am a separate table set up to store data for each individual
department (or Business Unit). they are all the same structure with
same fields. As more data comes into each table, there are likihoods of
duplication of records so i am creating this database to sort out
duplicates WITHIN each individual Business Unit.... as well as have the
capability to check ACROSS different (user-selected) business units for
duplications.

Problem:

I have a Temp table set up with the same structure that will be used to
run my queries from. From a form that has a Combo box, the user will
select which Business Unit (or BU) to check for duplicate records. I
would like the queries to be able to read the selected text (as it is
the same string as each BU's Table Name), as the table to use in the
query. Is it possible to do this so that the same query can be used
repeatedly based on what the user selects from the combo box?
Basically, i am thinking that if i have dozens of BU's sending me data,
I dont want to create the "same" query over and over for each BU. I
want a query that can contain a variable to represent which Tables to
use. so, i am not sure if what I want to do is possible since when i
try to create a query I can only select existing tables from the lists
and not use an expression to point to my stored selection from the
combo box.

If there is a way, what's the logical solution to make this work?
Thanks
Herman

 

[Tom Ellison]

Dear Herman:

It would be far better to have just one table for all departments. If you
add just one column to the tables you have which uniquely identifies each
department, then this would be equivalent to what you have.

You can create the appearance of this with a UNION query. In doing so, you
can and should add the missing column for the department name to this query.
In general, it would look like this:

SELECT "Dept1" AS DeptName, *
FROM SomeDeptTable
UNION ALL
SELECT "Dept2" AS DeptName, *
FROM AnotherDeptTable

Repeat for additional departments.

If you would just create a table that looks like what this query produces,
you would be in the clear.

You should also have a table just listing each department.

Your combo box can filter to the DeptNames in the above.

Tom Ellison

 

[Joshua A. Booker]

Herman,

Dozens of tables with the same fields sounds like a nightmare. Especially
when it comes to Cross-Business Unit Reporting. And what if you simply want
to add a field? You'll be doing in dozens of tables which will likely
affect many dozens of queries. It's like a snowball effect.

The right way is to use a single table for all BUs. Simply add a
BusinessUnit field to the table.

You could have an additional table which contains one record per BU to use
to lookup options for the BU field.

Primary keys and unique indexes in your table design will restrict
duplicates at the time of data entry. So checking for dups is often not
necessary.

HTH,
Josh

 

[hermanko]

Thank you both for your knowledge and expertise. I was thinking about
how if i had so many tables, it would start to become extremely
difficult to manage. I have changed my database so that all the
imported raw data will get appended to ONE main table.

Now I just set up different queries to suit my needs, all based on the
underlying 1 table.

Thanks for the advice!
herman