Solved

Access 2016 Merge Tables

Posted on 2016-11-23
8
73 Views
Last Modified: 2016-11-28
I am attempting to create a database that combines information from 2 sources.  One is a time keeping system, Table A.  The other is from a payroll system TableB.

I want to create a new table that uses all the data in Table A and and adds the SSN and ITemID from Table B.  Two fields in Tables have to match.  EmID from Table A has to match AIdent from Table B.  Also Worked Department from Table A has to match DeptName in TableB.

I have to match multiple fields because some employees will work in multiple departments in a single pay period.

I've tried using Dlookup and I can't seem to get the data to merge properly.  I keep getting hundreds of duplicate records.

I'm attaching a sample file with fake data that matches the structure of my real DB.  Didn't want to share anyone's SSN.
Sample.accdb
0
Comment
Question by:Mwvarner
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41899661
Since you have duplicate people rows in TableB and no way to refine the joining of the two tables, you need to remove the duplicates from TableB.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41899706
Duplicate removal query
DELETE TableB.*
FROM TableB 
WHERE TableB.ID <> (Select Min(B.ID) From TableB As B
WHere (TableB.FirstName = B.FirstName) AND (TableB.LastName = B.LastName))

Open in new window

0
 

Author Comment

by:Mwvarner
ID: 41899733
The rows in Table B aren't completely duplicates.  The departments and assignments are different.   I know DLookup can use multiple criteria I just can't work out the syntax.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 45

Expert Comment

by:aikimark
ID: 41899769
Unless you can state when one TableB row should be used and when a different TableB row should be used in the table-join operation we can't help you.  I don't see any other columns that help in this process.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41899902
We don't have enough information to resolve the problem but as has already been mentioned, you need to eliminate any duplicates.  In both tables, you will validly have multiple rows for a given employee so you should create queries to summarize or group the data to get down to a single row per employee per source.  Then you can join the queries and not end up with nonsense in the output. The problem is caused because neither table is an employee "master".  Both tables are transnational and by definition, will include multiple rows for each employee.

Before you can do any joining, you need to make the data types consistent.  So the department code should be EITHER numeric or text.  I'm going to suggest that it be text unless you know for certain that at the source, it will NEVER contain non-numeric characters and NEVER require leading zeros.  Fields like SSN are NOT NUMERIC.  They may contain only numbers at present but they are CODES and CODES are  always text unless you have a death wish.  SSN can have leading zeros so unless you want to end up with SSN's that are less than 9 characters, you MUST define SSN as text.  Same with Zip CODE if that field is contained in any of your import tables, especially if you might have employees with Canadian addresses.

Using DLookup() is not the correct way to solve this problem.  You shouldn't ever use domain functions in queries especially if there is an alternative and in this case there is.

PS, including ItemID (whatever that is) will prevent tableB from being summarized to one row per employee and once that happens, the joins will always produce nonsense so we are going to need to know more about your ultimate goal to advise further.
0
 
LVL 15

Accepted Solution

by:
John Tsioumpris earned 500 total points
ID: 41900126
Take a look at my attachment --> FinalJoinedTable (query)
Sample.accdb
0
 

Author Closing Comment

by:Mwvarner
ID: 41904845
Great Job and I really appreciate the assist. I have posted a related question so maybe you can help with that one as well.

Thanks again.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41904855
Look very carefully at the sample output created by John.  It might not actually work the way you need it to but then you never did explain how the de dupe should work.
1

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question