Question with query

[Eric Huang]

Hi,

I am fairly new to Access, and I hope someone here can help me with my question.

I am trying to combine two tables with different fields. My first table has salary data of each employee:

Event ID (primary key)
Employee ID
Event type (pay raise or pay decrease)
Event date
New salary figure

Second table has injury data:

Event ID (primary key)
Employee ID
Event type (type of injury)
Event date
+ other fields related to the injury



I tried to create a query that display all events and event details related to an employee in chronological order, but I ended up getting the permutation of the two tables. What would be the proper way of doing this?

Also, for logistic reason, I prefer not to create a combined table that has all the above fields.

Thanks

[June7]

Try a UNION query. There is no wizard or designer for UNION query. Must type in the SQL View editor window.

SELECT "PayChange" As Source, EmployeeID, EventID, EventDate, EventType, Salary FROM Table1
UNON
SELECT "Injury", EmployeeID, EventID, EventDate, EventType, Null FROM Table2

If field names do have space, enclose names in []. Advise to not use spaces in any names.

[Access_Blaster]

Hi,

I am fairly new to Access, and I hope someone here can help me with my question.

I am trying to combine two tables with different fields. My first table has salary data of each employee:

Event ID (primary key)
Employee ID
Event type (pay raise or pay decrease)
Event date
New salary figure

Second table has injury data:

Event ID (primary key)
Employee ID
Event type (type of injury)
Event date
+ other fields related to the injury

I tried to create a query that display all events and event details related to an employee in chronological order, but I ended up getting the permutation of the two tables. What would be the proper way of doing this?

Also, for logistic reason, I prefer not to create a combined table that has all the above fields.

Thanks

Why does table (2) have the same field names as table (1)? Are you duplicating information? You could have..

Employee table
EmployeeID (primary key)
EfirstName
ElastName
Etc..

Event table
EventID (primary key)
EmployeeID (foreign key)
EventType
EventDate
Etc...

Salary table?
SalaryID (primary key)
Etc..

Then you could link with the EmployeeID's or some other unique field.
Richard

[orange]

Please describe your tables in plain English. Why is EventId PK on the 2 tables? I think normalization of tables may be an issue. Getting you tables and relationships designed is basic to ease of use and stability.

What is an Event?