Multiple Tables with Identical Fields

[jones1973]

I'm creating a db using Access 2003 and have run into a little problem. I read and hear that creating multiple tables is not a good idea, but I have five users that will have to update the db throughout the day (often at the same time). So I've created a table for each user, each with identical fields. Now I need to create a query which pulls information from every table. Surely this is possible, with my limited skills and knowledge however, this has become incredibly confusing.

An example:

Let's assume User 1 table contains fields "name" "date" and "commission" and Users 2, 3, 4, 5 have similar tables each with the fields "name" "date" and "commission" Is it possible for a query to only have those three fields, but pull the data from all five tables?

Thanks,

Kevin

 

[dhookom]

I don't think this is a good idea to have separate tables. Also, 2 of your 3 field names are not recommended since every object has a Name property and date is a function. These are both reserved words.

However you should be able to use a union query;

SELECT [name], [date], [commission], 1 as TableNum
FROM table1
UNION ALL
SELECT [name], [date], [commission], 2
FROM table2
UNION ALL
SELECT [name], [date], [commission], 3
FROM table3
UNION ALL
-- etc --
SELECT [name], [date], [commission], X
FROM tableX;

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

Access is not an intuitive program. There are very exact protocols to follow in creating relational databases and their tables. This process is known as Normalization. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Also, I suggest taking courses and buying many books before venturing out into the Access world.

 

[lespaul]

as long as you set it up correctly for multi-users, Access will work fine with 5 simultaneous users...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[GingerR]

Make one table. That's what Access if for.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html