Combine Tables with Different Field Name

[newbie123]

I have two tables:

Table 1 Field Names:
1. Emp ID
2. Units

Table 2 Field Names
1. Emp ID
2. Units

*There are Emp IDs in Table 1 that may not be in table 2 - and vice versa.
*There are Emp IDs in each table that may appear more than once - due to the
multiple work days.

I can't use the JOIN function as it does not give me the option I want - that
is to create Table 3 where:
1. If Emp ID 12345 is in both Table 1 (unit=4) and Table 2 (unit=6) , then
in Table 3 put the sum of Emp ID 12345, unit =10.
2. If Emp ID 12345 is in Table 1 (unit=4) and NOT in Table 2, then in Table
3 put the sum of Emp ID 12345, unit =4.
3. If Emp ID 12345 is not in Table 1 and in Table 2 (unit=6), then in Table
3 put the sum of Emp ID 12345, unit =6.

Help!

 

[Gina Whipp]

newbie123,

I think you should read up on Normalizaion and Relational Database Design...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


The way you have set up your tables has made it quite difficult to get the
data you want. I think you should reconsider your table set up or switch to
Excel which is more in line with what you are doing. Example...

tblEmployee
eEmployeeID (Primary Key)
etc..

tblEmployeeUnits
euEmployeeID (Foreign Key linked to tblEmployee
euUnits
etc...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John Spencer]

Normalize your data with a union query and then use the use that query as the
source for getting the sum

Union query (can only be created in SQL view, not design view)
SELECT [Emp id], Units
FROM [Table 1]
UNION ALL
SELECT [Emp id], Units
FROM [Table 2]

Then
SELECT [EMp Id], Sum(Units) as Total
FROM qUnionQuery
GROUP BY [Emp ID]

A better alternative would be to append all the records from one of the tables
into the other table so you have only one table to deal with. I realize that
this may not be a valid solution for you depending on the complete structure
of the two tables and their sources.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[newbie123 via AccessMonster.com]

Thanks for the response John.

When I did the Union query - the resultant table has the Emp ID repeated
numerous times. I read something about this on the forum - about it being a
Cartesian (sp?). How do I get this not to happen?

Thanks again!

Normalize your data with a union query and then use the use that query as the
source for getting the sum

Union query (can only be created in SQL view, not design view)
SELECT [Emp id], Units
FROM [Table 1]
UNION ALL
SELECT [Emp id], Units
FROM [Table 2]

Then
SELECT [EMp Id], Sum(Units) as Total
FROM qUnionQuery
GROUP BY [Emp ID]

A better alternative would be to append all the records from one of the tables
into the other table so you have only one table to deal with. I realize that
this may not be a valid solution for you depending on the complete structure
of the two tables and their sources.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have two tables:

[quoted text clipped - 20 lines]

Help!

 

[newbie123 via AccessMonster.com]

It works! Thanks VERY much John!

Normalize your data with a union query and then use the use that query as the
source for getting the sum

Union query (can only be created in SQL view, not design view)
SELECT [Emp id], Units
FROM [Table 1]
UNION ALL
SELECT [Emp id], Units
FROM [Table 2]

Then
SELECT [EMp Id], Sum(Units) as Total
FROM qUnionQuery
GROUP BY [Emp ID]

A better alternative would be to append all the records from one of the tables
into the other table so you have only one table to deal with. I realize that
this may not be a valid solution for you depending on the complete structure
of the two tables and their sources.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have two tables:

[quoted text clipped - 20 lines]

Help!

 

[newbie123 via AccessMonster.com]

Another question. I have four tables I need to combine:

Table 1 Field Names:
1. Emp ID
2. #Detect

Table 2 Field Names:
1. Emp ID
2. #Missed

Table 3 Field Names:
1. Emp ID
2. #NoAction

Table 4 Field Names:
1. Emp ID
2. #Avoid

*There are Emp IDs in Table 1 that may not be in table 2, 3 and 4 - and vice
versa.

In Table 5 - I am trying to create:

Column 1: Emp ID
Column 2: #Detect
Column 3: #Missed
Column 4: #NoAction
Column 5: #Avoid

There are Emp ID in columns that may have data for column 2, 3, 4,5 or maybe
just 2 and 3, etc. For the columns where there are no data associated with
that Emp ID - either leave blank or "0".

 

[John W. Vinson]

Another question. I have four tables I need to combine:

Table 1 Field Names:
1. Emp ID
2. #Detect

Table 2 Field Names:
1. Emp ID
2. #Missed

Table 3 Field Names:
1. Emp ID
2. #NoAction

Table 4 Field Names:
1. Emp ID
2. #Avoid

*There are Emp IDs in Table 1 that may not be in table 2, 3 and 4 - and vice
versa.

In Table 5 - I am trying to create:

Column 1: Emp ID
Column 2: #Detect
Column 3: #Missed
Column 4: #NoAction
Column 5: #Avoid

There are Emp ID in columns that may have data for column 2, 3, 4,5 or maybe
just 2 and 3, etc. For the columns where there are no data associated with
that Emp ID - either leave blank or "0".\

Neither structure is correctly normalized. The current structure stores data
in the table name - you have a table for Detect, another table for Missed etc.

The second structure stores data in fieldnames - a field for Detect, a field
for Missed, etc.

Data should not be stored EITHER in tablenames or in fieldnames, but in fields
in a table!

A properly normalized table structure would have two tables: a four-row (at
present, this might change) table Actions with one primary key text field
named Action with values "Detect", "Missed", "No Action", "Avoid"; and an
EmployeeActions table with fields EmpID, Action, and any other fields (such as
the date of the action, or the event which might have been "missed" or
"avoided"). Maybe there are no such other fields if each employee can only
"Detect" once.

 

[newbie123 via AccessMonster.com]

the data are sent to me in 7 different spreadsheets. Each month I have to go
through and combine the data into one table or spreadsheet for analysis. I
thought bldg an access dbase would be easier to get the data into one table -
which I can export to excel to analyze.

I don't understand this stmt: Data should not be stored EITHER in tablenames
or in fieldnames, but in fields in a table! The data are in tables. I just
same the tables those names to make it easier to identify.

I am new to access (may 2009!) and am on a steep learning curve to get this
analysis done. Sorry if I am using incorrect terminologies, etc.

Another question. I have four tables I need to combine:

[quoted text clipped - 28 lines]

just 2 and 3, etc. For the columns where there are no data associated with
that Emp ID - either leave blank or "0".\

Neither structure is correctly normalized. The current structure stores data
in the table name - you have a table for Detect, another table for Missed etc.

The second structure stores data in fieldnames - a field for Detect, a field
for Missed, etc.

Data should not be stored EITHER in tablenames or in fieldnames, but in fields
in a table!

A properly normalized table structure would have two tables: a four-row (at
present, this might change) table Actions with one primary key text field
named Action with values "Detect", "Missed", "No Action", "Avoid"; and an
EmployeeActions table with fields EmpID, Action, and any other fields (such as
the date of the action, or the event which might have been "missed" or
"avoided"). Maybe there are no such other fields if each employee can only
"Detect" once.

 

[John W. Vinson]

the data are sent to me in 7 different spreadsheets. Each month I have to go
through and combine the data into one table or spreadsheet for analysis. I
thought bldg an access dbase would be easier to get the data into one table -
which I can export to excel to analyze.

You can use File... Get External Data... Link to link to the spreadsheets, and
Append queries to migrate the data into your Access tables (whatever tables
you eventually create).

I don't understand this stmt: Data should not be stored EITHER in tablenames
or in fieldnames, but in fields in a table! The data are in tables. I just
same the tables those names to make it easier to identify.

The logic is called "Normalization", and it's a rather deep and arcane area if
you're new to it. Basically, if you have a table for each kind of action -
*or* if you have a field for each kind of action - then you're in trouble if
your business rules ever change and you need to create a NEW kind of action.
You would need to add a new table (or change the structure of your table),
rewrite all your queries, redesign all your exports, redesign your forms and
reports... ouch!

If you store the fact that a given record is "Missed" by storing "Missed" as
data in a field, then this problem does not arise.

Perhaps you could explain the meaning of the data and a bit more about the
structure of your spreadsheets; someone may be able to suggest a reasonable
approach, if my four-table structure isn't practical.

I am new to access (may 2009!) and am on a steep learning curve to get this
analysis done. Sorry if I am using incorrect terminologies, etc.

No apologies needed - we're all new to something! Here are some resources you
might find helpful; Crystal's video and/or tutorial might be good places to
start:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials