Merging tables (1 Viewer)

[Scott488]

Hi Guys (and Gals),

First post so please hang on in there with me as I'm not too familiar with access terminology but I'm leaning!

So here is my problem. I have two tables which have data in them which is trawled from source which doesn't have any data manipulation tools in it. I want to be able to merge both of these table into one. While I have tried this in a query, I'm having issues with the joins as some records are missed.

I've uploaded a stripped down version of the tables which may explain better.

I want the resulting table to show all sites 1 through to 5 with the columns from both tables included, so something like

Site number 2012 Killed 2013 Killed 2012 Injured 2013 Injured
1 3 5 0 0
2 1 1 0 0
3 4 4 1 1
4 0 0 1 1
5 0 0 10 6

 

[pbaldy]

Along the lines of:

SELECT Table1.[Site Number], Table1.[2012 (Killed)], Table1.[2013 (Killed)], 0 AS 2012Injured, 0 AS 2013Injured
FROM Table1
UNION ALL
SELECT Table2.Site, 0 AS 2012Killed, 0 AS 2013Killed, Table2.[2012 (Injured)], Table2.[2013 (Injured)]
FROM Table2

 

[plog]

What you want is called a full outer join: http://www.w3schools.com/sql/sql_join_full.asp

And, Access doesn't support those so you have to do it by hook and crook. pbadly's code will get you 1/2 way there. You would save his code and name it 'FullQuery_sub1'. Then you would use it in another query. You would bring in all the fields from into the bottom of Design View. Then you would click the Sigma/Summation symbol in the ribbon. Then for every field except [Site Number] you would change the 'Group By' underneath all the fields to 'Sum'. Run that and it will give you your data.

However, this isn't the best way to work with data. You are creating an improperly structured data source. You might want to read up on normalization on how to properly structure data:

https://en.wikipedia.org/wiki/Data_normalization

 

[Scott488]

Thanks for the responses.

A full outer join it is then (although it doesn't exist)

While I understand the basics of data normalization, the problem is where the data comes from in the first instance. I have no choice but to export duplicate records from this source.

Having used the tips provided, I have managed to obtain what I needed.

 

[plog]

This has nothing to do with how you are receiving the data. The UNION query you first inquired about was so that you could work with the data. My advice is to make a UNION query to work with the data, but do it properly.

Your query shouldn't store valid data in field names (2012Injured, 2013Injured, etc.). The year portion of that should be a value in a field, not part of a field name. Same for the Injured/Killed information. The query you build should have data like so:


SiteNumber, Status, DataYear, DataQuantity


SiteNumber would contain the same data as it does now; Status would hold Killed/Injured and DataYear would hold the year the data is for; and DataQuantity would hold the total number for that Status/DataYear permutation. It would make your query simplier (eliminating the need for a subquery) and it would create a better structure to write further queries upon.