Merging Data in Access

[NeoPa]

The general technique to use if you need a FULL OUTER JOIN in Access between TableA & TableB would be to use a UNION query adding the results of two separate queries together where one is TableA LEFT JOIN TableB and the other is TableB LEFT JOIN TableA.

Something like :

SELECT TableA.*,
       TableB.*

FROM   TableA LEFT JOIN TableB
  ON   TableA.ID=TableB.ID

UNION

SELECT TableA.*,
       TableB.*

FROM   TableB LEFT JOIN TableA
  ON   TableB.ID=TableA.ID

It's not FULL OUTER JOIN, but it does the same job, albeit less efficiently.

[sam2779]

merging database

You guys crack me up! I really do appreciate all the help though and I really am trying different ways to get it to do what I want. See I get my data from another source and then I have to put it all together, which is the reason for multiple tables. So I have one table that looks like this:
WUC #failures
text number

next one looks like:
WUC MMHs
text number

next one is:
WUC NMC
text number


I want to be able to merge all together. Some of the WUCs have #failures, MMHs, and NMC. However some only have one or two of the others. What I want is a complete list but if some of them have 2 or all three of the other fields (Failures, MMHs, NMC) then I want them on one line.

For what its worth, I'm freezing at work. I think they keep the temperature on the North Pole setting.

[sam2779]

I guess I do not know how to do a union query in Access.

[ajalwaysus]

I'm glad I could provide an entertaining sub plot to this thread. =)

(if I dare try) Just so we are all on the same page, you receive data from multiple sources, all having different pieces of the pie (so to speak) and you now need to merge them all in to one single table. If that is so then I think you need to give us a more expanded explanation of your data sources and what's in them. You mentioned First and Last names and telephone numbers, but now you mentioned "WUC" what is that? Is that the common field between all your data sources.


-AJ (I would take cold over heat any day!)

[NeoPa]

You guys crack me up! I really do appreciate all the help though ...

I don't think we were trying to be funny. We do vaudeville too, but we generally charge for that.

Anyway, moving on. I'm not sure what more there is to know about UNION queries in Access than is already included (as an example) in post #16. It is possible to use UNION ALL to ensure duplicates are not dropped (as is the standard behaviour for UNION queries). If you're interested in learning more independently then try Finding Jet SQL Help. It's covered quite well in the Help if you know how/where to look.

Your post provides some Meta-Data information, but not enough to work with I'm afraid. I was hoping to avoid more to-ing & fro-ing by suggesting a time-tested format that I know works (Post #14). If you can provide that information as requested I'm confident we can help you progress.

[Denburt]

I don't think we were trying to be funny. We do vaudeville too, but we generally charge for that.

Neo, I think he is appreciating this thread as vaudeville... LOL I am quite sure some that are less experienced than us would consider it so (to some degree). I would like to toss something in but all I can tell you Sam is to look at post #14!

Posting your table structure will go a long way in resolving this.

[sam2779]

Ok, initially, I was trying to use last names etc as an example so I wouldn't give you the real data which might confuse you. Then I thought it may just be easier to go ahead and divulge a little of what I'm working with. The WUC is the common field in all the tables and is in text format. I do not have primary or foreign keys set up in the database. I will look at the link for the help that Neo has provided. I looked at post #14 again-as I've said no PK or FK. Should I create one?
Table=[Table 1]

[I]Field Name   Type[/I]
WUC          Text
#Failures    Number

Table=[Table 2]

[I]Field Name  Type[/I]
WUC         Text
NMC         Number

Table=[Table 3]

[I]Field Name  Type[/I]
WUC         Text
MMH         Number

I see you guys are way more advanced than I am, so I'm going to start with my Access for dummies book. With all that being said, I will certainly read more of what help has to offer me. Thanks again for everyone's help.

[NeoPa]

I think I must have misunderstood what you were trying to say earlier Sam.

I want to be able to merge all together. Some of the WUCs have #failures, MMHs, and NMC. However some only have one or two of the others. What I want is a complete list but if some of them have 2 or all three of the other fields (Failures, MMHs, NMC) then I want them on one line.

Were you referring to WUCs when you said "some of them"? I inferred you were talking about tables. This makes life easier if you only have three different types of data (in clearly defined tables).

I don't think an FULL OUTER JOIN is necessary here, and though posible, would probably be more complex than required.

Start by setting up a table (let's call it tblMain for now) with fields for WUC, Failures, MMHs & NMC. It would look like :
Table Name=[tblMain]

[I]
Field      Type      IndexInfo[/I]
WUC        String        PK
Failure    Number
NMC        Number
MMH        Number

Next we append each of the tables in turn (Here is an example of the query for the first table) :

INSERT INTO tblMain (WUC,Failures)
SELECT      WUC,
            [#Failures]
FROM        [Table 1]

When all are done (Matching records will fail. This is expected & not a problem), then we go through all of them updating. I'll use [Table 2] as an example for the second phase updates :

UPDATE tblMain INNER JOIN [Table 2]
    ON tblMain.WUC=[Table 2].WUC
SET    tblMain.NMC=[Table 2].NMC
WHERE  tblMain.NMC Is Null

When you have done this for all three tables your data should be all fine & dandy. Do let us know how you get on though. We're all interested now.

[Denburt]

I didn't mean any offense Sam (I have been there though) I am wondering if these tables are related such as are the Field Name types on each table related to the other table in any way thus producing the records used??? If not then the following should produce all the records from all three tables and in separate fields:

SELECT [Field Name Type], [WUC Text],[#Failures Number],null as [NMC Number],null as  [MMH Number] FROM Table1

Union all select [Field Name Type], [WUC Text],Null, [NMC Number],null FROM Table2

Union all select [Field Name Type], [WUC Text],Null, null, [MMH Number]  FROM Table3

[Denburt]

I think Neo may be more on target than I am at this point but hey, I thought I might take a stab in the dark. Oh and as far as cold or heat I would never want either in an extreme so I guess for now Louisiana is safe enough for me.

[NeoPa]

I think [Field Name Type] is actually supposed to be a heading Den. It was supposed to be similar to the example data I posted in post #14. Unfortunately, as the format wasn't followed the posted information was quite unclear. I've fixed it up now. It should make more sense.

As for the UNION ALL concept, unfortunately that won't work as required. It will create multiple records instead of a single record where different fields are found in matching records. Does that make sense?

[sam2779]

I have appended all the data into one table, and of course I did lose some data. I had all my failures because that's the one I appened first, but I lost some NCM and MMHs. The second phase of updating I did 2 inner joins one for MMHs and one for the NMC. I'm not sure where to go from there, but I am so much more closer than I was yesterday. Thanks!

[NeoPa]

Sam,

I think you've gone astray.

If you post all the queries that you've run I will help to clarify whereabouts you need to make changes. I assume you still have the original data available. Don't lose that until everything is perfect (at least).

[sam2779]

UPDATE tblMain INNER JOIN MMHs
    ON tblMain.WUC=[MMHs].WUC
SET    tblMain.MMH = [MMHs].MMH
WHERE  tblMain.MMH Is Null

This is my update query for MMH; I didn't get an error message, but something still does not seem right...

[NeoPa]

If you post all the queries that you've run I will help to clarify whereabouts you need to make changes.

That seems fine Sam, as far as it goes. I did ask for all the queries you've run though. I cannot be much help with just this one.