Need to combine data in 2 tbls even when it does not match

M

Marty

First, I apologize, I've seen a similar issue addressed but I can't figure
out how to apply the solution to my tables and fields.

I have 2 tables: Tbl_Keystrokes and Tbl_Errors
The fields for Tbl_Keystrokes are: MO, YR, USER, KEYSTROKES
The fields for Tbl_Errors are: MO, YR, USER, ERRORS

Sample data for Tbl_Keystrokes:
MO YR USER KEYSTROKES
07 07 Carol 232323
07 07 Helen 333333
08 07 Carol 888888
08 07 Helen 555555
08 07 Janice 444444

Sample data for Tbl_Errors
MO YR USER ERRORS
07 07 Ellen 9123
07 07 Helen 105
08 07 Carol 219
08 07 Helen 199
08 07 Janice 444

In any particular month a user may have keystrokes but no errors or may have
errors but no keystrokes. I need to combine the data so that all the data is
included and data for the same user is shown side by side like this…

MO YR USER KEYSTROKES ERRORS
07 07 Carol 232323
07 07 Ellen 9123
07 07 Helen 333333 105
08 07 Carol 888888 219
08 07 Helen 555555 199
08 07 Janice 444444 444

Thanks so much for your help.
Marty
 
D

Damon Heron

What is stopping you from adding an Errors field to the Tbl_Keystrokes and
eliminating Tbl_Errors?

Damon
 
T

Tom Wickerath

Hi Marty,

You can use an Append query, which is based on a Union query, to append the
records from both tables into a new table. Here is a tutorial that you can
download on using Union queries:

http://www.accessmvp.com/TWickerath/downloads/unionqueries.zip

The easiest way to create a union query from two source tables is to have
the same number of fields in each table. So, you can add the missing Errors
field to the Tbl_Keystrokes table, and the Keystrokes field to the Tbl_Errors
table. Use the same data type and field sizes. You do not need to populate
these fields with data.

1.) Copy the structure only of one of these tables to a new empty table.
2.) Create a Union query that brings the data together for your two sources
tables.
3.) Create an append query that uses your new union query as a source of
data, and appends records to your new empty table.

Here are tutorials on using Append Queries:

Create an append query (MDB)
http://office.microsoft.com/en-us/access/HP051880831033.aspx

Adding rows by using an append query
http://office.microsoft.com/en-us/access/HA011860631033.aspx

Your situation is fairly simple, but here is a reference to an article that
you might want to tuck away (bookmark) for possible future use:

Merge Records from 2 Identical Databases by Danny J. Lesandrini

http://www.amazecreations.com/datafast/ShowArticle.aspx?File=Articles/mergedatabases.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

Ken Sheridan

Marty:

You can join the tables on the MO, YR and USER columns, but because there
will not always be a match you'll need to use an outer join to cater for
those situations. Outer joins are one directional, so you'll need two of
them. The results can be combined by means of a UNION operation:

SELECT Tbl_Keystrokes.MO, Tbl_Keystrokes.YR,
Tbl_Keystrokes.USER, KEYSTROKES, NZ(ERRORS,0)
FROM Tbl_Keystrokes LEFT JOIN Tbl_Errors
ON Tbl_Keystrokes.MO = Tbl_Errors.MO
AND Tbl_Keystrokes.YR = Tbl_Errors.YR
AND Tbl_Keystrokes.USER = Tbl_Errors.USER
UNION
SELECT Tbl_Errors.MO, Tbl_Errors.YR,
Tbl_Errors.USER, NZ(KEYSTROKES,0), ERRORS
FROM Tbl_Errors LEFT JOIN Tbl_Keystrokes
ON Tbl_Errors.MO = Tbl_Keystrokes.MO
AND Tbl_Errors.YR = Tbl_Keystrokes.YR
AND Tbl_Errors.USER = Tbl_Keystrokes.USER
ORDER BY YR, MO, USER;

There being no point in having two tables you can then use this to populate
a new single table if you wish and then delete the existing tables.

Note that I've used the Nz function to return a zero in place of Null errors
or keystrokes. Normally with numeric data like this its best to have a
default value of zero, and in a single table it would be advisable to set the
Required property of the keystrokes and errors columns to True and their
DefaultValue property to 0.

Ken Sheridan
Stafford, England
 
Top