Merging Data in Access

[sam2779]

I have multiple tables in access. I want to be able to combine all the tables into one large table. Let's say I have one table that has last name, first name, credit card information then another table that has last name, first name, telephone number. I want to be able to merge the 2 however just because I have the credit card information doesn't mean I have the telephone number and vice versa. Therefore I cannot create a simple query. I want one list that combines both and if there is no credit card information for that person then just leave it blank or the same it there is no telephone number.

Hope that makes sense. Thanks!

[NeoPa]

The question makes sense. Unfortunately I very much doubt the same can be said of your data structure. Please check out Normalisation and Table structures. I'm sure you, like many before you (myself included), will find the understanding from that quite invaluable.

As far as a direct response to the question is concerned, disparate tables can be linked via common unique references. Typically though, unless there are good reasons not to, all data relating to a single object type would be held in a single store (table).

[sam2779]

Thanks for the reply. Unfortunately there is a good reason why it does not all come in one table but my goal was to put it all in one table without having to copy and paste. Oh well thanks for your help.

[ajalwaysus]

Sam,

Do you have an ID that relates these tables together? If you do, you could outer join these tables together by that ID, a less efficient and less recommended way would be to join on first and last name (because different people can have the same first and last name).

Let me know if you have any questions,
-AJ

[NeoPa]

That should certainly be straightforward enough assuming you have fields in the various tables you can use to link them together reliably. It's early to give up on the problem (unless you have no such referential fields available of course).

Are your only connecting fields the name of the person?
How reliably are they set up (can they be spelled differently or otherwise incorrectly held)?

[NeoPa]

@ajalwaysus
I don't believe the FULL OUTER JOIN (which would be required here) is implemented in Access's Jet SQL AJ. Unless they've introduced in in Access 2007. I doubt it though. The link gave a SQL Server example (which does support it fully of course).

[sam2779]

Thank you, I think an outer join may be what I'm looking for.

[ajalwaysus]

@NeoPa
Did you end up at this link:
http://office.microsoft.com/en-us/ac...345551033.aspx
because this looks like what I would do.
I'm sorry but am I missing something? It's been a long day, so i could be suffering a complete brain fart. =/

-AJ

[sam2779]

I see what he's saying. I think I do need to do a full outer join which cannot be done in Access database.

[ajalwaysus]

Ok, I see the issue, my apologies. The only other idea I could think of is a Union query. But since I don't have a good idea of the structure of the data, I can only throw out ideas.

-AJ

[NeoPa]

Check out the link AJ (yes I did go and look). 3/4 of the way down (you need to expand things a little) there is :

Note You cannot define a full outer join in an Access database.

[NeoPa]

@sam2779
There are ways Sam. Please don't get distracted by the discussion. We still need some answers from you if we're to progress. This is doable. Even in Access. We may need to cheat a little, but it's nothing too complicated or that we (I at least) haven't done before.

[ajalwaysus]

Wow, I am having a bad day, disregard my post, the more I think about it, that is a dumb idea. They have recently turned on the heat at work here, even though it's 65 degrees outside, so I am not all here right now.

Sorry,
-AJ

[NeoPa]

I suggest you post the meta-data (info about the layout / structure) of the tables in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

You need to explain too, what the linking is for all this data. If there are no unique references available then we need to know what you do have.

[NeoPa]

Don't be daft AJ. You're not responsible for the project. You're simply spending some of your spare time attempting to assist someone in need. If you get it wrong sometimes then don't worry about it. I think it's great that you're here pitching in at all. If you weren't half as good as you are I'd still be pleased.

I'm cold by the way ;) It's starting to get chilly here in the suburbs of London now Autumn is here. My thick jumper protects me from the worst of it mind. The heating is on now though (evening 20:10) so hopefully it won't get too cold.

[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]

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]

@sam2779
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]

@NeoPa
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]

Field Name   Type
WUC          Text
#Failures    Number

Table=[Table 2]

Field Name  Type
WUC         Text
NMC         Number

Table=[Table 3]

Field Name  Type
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.
@sam2779
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]

Field      Type      IndexInfo
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]

@NeoPa
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.