Make Table Query to Combine Form & Subform 1

[frosty2000]

I am working on a database to track employee data and their dependents' (spouse and children's) data. I have an "Employee Table" to track employee data, and a "Dependent Table" to track dependent data. The two tables have similar fields (i.e. date of birth, gender, etc) and are linked by the employee's Social Security Number. To differentiate between a record for an employee, spouse, or dependent, both tables have a record type column, where I enter "E" for employee (in the "Empoyee Table"), "S" for spouse, and "D" for dependent (in the "Dependent Table").

With these two tables, I created a main form (for inputting Employee data) and a subform (for inputting dependent data). I am in the process of creating a Make Table Query so that the two tables are combined into one. It is essential that the two tables are combined into one for the future queries I need to create.

For instance, I want the new Make Table to list all employees and all dependents (with each person having their own row of data). In my make table query, I built a formula for each column to tell it to pull the corresponding field based on the data's record type (i.e. in the new "Last Name" column, pull "Dependent Last Name" if record type is "S" or "D", and to pull "Employee Last Name" if record type is "E".)

However, the Make Table Query is only pulling records for the spouse and dependents - the employees are not showing up.

Does anyone have any ideas on how to solve this problem? I'm new to Access and don't have much programming experience. I've been through the entire Access userguide and can't figure this out!

Thanks,
frosty2000

 

[PHV]

Take a look at UNION query:
SELECT 'E' As Type, [Employee Last Name] As LastName, ...
FROM tblEmployee
UNION
SELECT type, [Dependent Last Name], ...
FROM tblDependant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[frosty2000]

PHV - Thanks for your help. The UNION query was exactly what I needed! I was able to create the union query, and then create a make-table query off the UNION query.

Here's some sample code I used for my UNION query:

SELECT [Last Name] AS [LastName], [First Name] AS [FirstName]
FROM [1 EmployeePersonalInformation]

UNION SELECT [Member Last Name] AS [LastName], [Member First Name] As [FirstName]
FROM [2 MemberPersonalInformation];