AutoNumber with multiple tables

[njfrlng]

I have 3 tables

Employees, Contractors, and Visitors


I have a form where I want to be able to select any person from any of the above tables.(Which is based off another table. I just want to be able to select any person from the above 3 tables and store their name/id in the 4th table)

I've created a union query where the names are pulled, along with their ID number.

However.... Since they are 3 different tables, all set to auto number, when I do the union I get repeat ID's. So when if I select two people with the same ID i get an error.

Is it possible to assigned unique auto number ids to the 3 tables? (E001 for employee table, C001 - for Contractors, V001 - for visitors)

Or is there another way to accomplish this?

 

[pbaldy]

In the UNION you can add a field:

SELECT Field1, Field2, "Emp" As Source
FROM Employees
...

You could also concatenate that with the ID field if you wanted.

"Emp" & IDFieldName As Whatever

 

[njfrlng]

kinda of confused...

If you could show me where I'd add that, it would be greatly appreciated!

Heres what I currently have:

SELECT tblContractors.ContractorID AS ID, tblContractors.[Last Name], tblContractors.[First Name], tblContractors.Department
FROM tblContractors;
UNION ALL SELECT tblEmployeInformation.EmployeeID AS ID, tblEmployeInformation.[Last Name], tblEmployeInformation.[First Name], tblEmployeInformation.DepartmentID
FROM tblEmployeInformation;

 

[plog]

My guess is you should probably have 1 table a level above that named 'People' where each unique person has a PeopleID. That would probably be the correct structure and save you from your duplicate issue with your UNION query.

Then instead of those 3 tables you listed, you would have 1 table named something like 'Roles' which has at least 2 fields--a foreign key for PeopleID and then a field to designate their role (i.e. Employee, Contractor, Visitor).

 

[njfrlng]

My guess is you should probably have 1 table a level above that named 'People' where each unique person has a PeopleID. That would probably be the correct structure and save you from your duplicate issue with your UNION query.

Then instead of those 3 tables you listed, you would have 1 table named something like 'Roles' which has at least 2 fields--a foreign key for PeopleID and then a field to designate their role (i.e. Employee, Contractor, Visitor).

I was thinking that too, was hoping not as Im so far into this database haah

 

[MSAccessRookie]

I have 3 tables

Employees, Contractors, and Visitors


I have a form where I want to be able to select any person from any of the above tables.(Which is based off another table. I just want to be able to select any person from the above 3 tables and store their name/id in the 4th table)

I've created a union query where the names are pulled, along with their ID number.

However.... Since they are 3 different tables, all set to auto number, when I do the union I get repeat ID's. So when if I select two people with the same ID i get an error.

Is it possible to assigned unique auto number ids to the 3 tables? (E001 for employee table, C001 - for Contractors, V001 - for visitors)

Or is there another way to accomplish this?

Why do you need to create a fourth Table that contains information already available in the other three? I sense a possible Normalization issue here.

You have three Tables (Employees, Contractors, and Visitors). Where they are different (other then the obvious Status) is unknown and most likely unnecessary to know for this discussion. Where they are the same is in that each of the Tables refers to people, so all three Tables should be based on a Table that contains information related to people (tblContacts?).

If that is what you are attempting to do, then you may not need the original IDs, since the replacement IDs should eventually take the place of the people related information in any other Tables. I hope I am not too far off track

-- Rookie

NOTE: It looks like plog is saying the same thing with an additional modification. The approach that I described is easy to implement, but may be harder to support in the long run. His is the reverse.

To Implement mine, the original Keys to the three Tables would not have to change. Instead you would build tblPeople and in each of the other three Tables, replace any related information with a Foreign Key to tblPeople. One big Negative is that if there ever is a new Role, a new Table needs to be created, and perhaps code written in order to support it.

To Implement his, the three Tables would go away, and they would be replaced by three other Tables (tblPeople, tblRoles, tblPeoplebyRoles). Your Project would need to be modified so that all referenced point to the new Data. While this approach takes more time up front, it has the distinct advantage of being easy to support, and if there ever is a new Role, all that is required is for a new entry to be added to tblRoles, and appropriate related entries added to tblPeople and tblPeoplebyRoles.

I would try to take time to do it his way

 

[njfrlng]

The tables Employee, Contractors, and Visitors are very different tables.

I need a very detailed list of info on employees, a semi detailed list on contractors, and very little on visitors.

So do I maintain 1 large table with all the required fields of all 3 original tables?

 

[Solo712]

The tables Employee, Contractors, and Visitors are very different tables.

I need a very detailed list of info on employees, a semi detailed list on contractors, and very little on visitors.

So do I maintain 1 large table with all the required fields of all 3 original tables?

Perhaps there is a simpler solution. You might want to create an all-purpose table with counters. When you need a new record for any of your three tables, just copy an ID from a field in the counters table and increment the field.

Best,
Jiri

 

[jdraw]

You may get some ideas re tables/structure from the article at
http://it.toolbox.com/blogs/enterprise-solutions/understanding-entities-in-er-diagrams-14255

You can also research supertype/subtype

 

[Pat Hartman]

plog offered the correct solution. You have an entity table with a unique ID. That table contains all the common fields and a field that identifies this entity as an Employee, Contractor, or Visitor. If it would be too wasteful to use just this single table, you could then have one or more related tables with details for a particular type. Sounds like all the visitor info couold fit into the entity table so you might only need tables for Employees and Contractors.

All relationships in the database would point to the entity table. Nothing would ever point to Employee or Contractor or Visitor directly.