I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
query to combine different fields from multiple tables
- Thread starter Dave H
- Start date
You need to properly structure your data. This process is called normalization (http://en.wikipedia.org/wiki/Database_normalization).
First and foremost, you need a table which has everyone. This will contain at a minimum their name and a unique ID number (autonumber). Then, based on the rules of normalization you might have more tables. In those tables you would use that person's unique ID number, not their name or whatever else you have.
When your database is structured as such, running a query of everyone becomes simple because you have a table that lists everyone. Put this issue aside and focus on structuring your tables correctly.
First and foremost, you need a table which has everyone. This will contain at a minimum their name and a unique ID number (autonumber). Then, based on the rules of normalization you might have more tables. In those tables you would use that person's unique ID number, not their name or whatever else you have.
When your database is structured as such, running a query of everyone becomes simple because you have a table that lists everyone. Put this issue aside and focus on structuring your tables correctly.
Using that you would create LEFT JOINS (http://www.w3schools.com/sql/sql_join_left.asp) to the other tables and bring in the data from them that you want.
Can you post your structure? A Screenshot of your relationships table will do if you have it.
Can you post your structure? A Screenshot of your relationships table will do if you have it.
Plog,
I tried to simplify what I am trying to do in my original post. I don't like to use auto number to create arbitary unique identifiers. I have combined name, manager and month to create my unique identifier.
I have tried to upload a screenshot of the structure I was trying to use.
I used queries to load table 2 with all of unique identifiers.
I tried to simplify what I am trying to do in my original post. I don't like to use auto number to create arbitary unique identifiers. I have combined name, manager and month to create my unique identifier.
I have tried to upload a screenshot of the structure I was trying to use.
I used queries to load table 2 with all of unique identifiers.
Attachments
Oy. Do you really have fields named like 'Field 17', or did you do that to hide the real name? Seriously, please tell me those aren't actual field names.
In Table2 you are using are reserved words (http://support.microsoft.com/kb/286335) for field names (Month, Name). Doing this will cause you issues when trying to write code and queries.
In the other 2 tables you have a field called 'Month'--is the value of that based on the value in the respective 'Date:' fields? If so, that is incorrect as well--you shouldn't store redundant data.
Lastly, I see this is an APPEND query--why? What does the table this data is going to hold? Will you run this APPEND query once to get your data to the proper table, or will this be an ongoing query that you run frequently?
In Table2 you are using are reserved words (http://support.microsoft.com/kb/286335) for field names (Month, Name). Doing this will cause you issues when trying to write code and queries.
In the other 2 tables you have a field called 'Month'--is the value of that based on the value in the respective 'Date:' fields? If so, that is incorrect as well--you shouldn't store redundant data.
Lastly, I see this is an APPEND query--why? What does the table this data is going to hold? Will you run this APPEND query once to get your data to the proper table, or will this be an ongoing query that you run frequently?
Plog,
The names that I am being asked to use for example field 17 represents "Year End Provision close process, including deferred tax entries-consol (Days 1-6)" these names may change multiple times before I am finished so for now I am just using field name x. For database purposes the label doesn't matter.
Let me see if I can restate my question in a more simplified manner.
Say you have 3 tables.
In table 1 you have unique identifers 1,2,3,4, and 5
In table 2 you have identifiers 1,2 and 3 with data in field 1 and field 2
In table 3 you have identifiers 3,4 and 5 with data in field 3 and field 4
I need a query that pulls all records 1,2,3,4 and 5 from all the tables and populates fields 1, 2, 3 and 4 with data as available. I believe this may be a union query but I don't know how to accomplish that.
The names that I am being asked to use for example field 17 represents "Year End Provision close process, including deferred tax entries-consol (Days 1-6)" these names may change multiple times before I am finished so for now I am just using field name x. For database purposes the label doesn't matter.
Let me see if I can restate my question in a more simplified manner.
Say you have 3 tables.
In table 1 you have unique identifers 1,2,3,4, and 5
In table 2 you have identifiers 1,2 and 3 with data in field 1 and field 2
In table 3 you have identifiers 3,4 and 5 with data in field 3 and field 4
I need a query that pulls all records 1,2,3,4 and 5 from all the tables and populates fields 1, 2, 3 and 4 with data as available. I believe this may be a union query but I don't know how to accomplish that.
Honestly, it sounds like you are in for a lot of headaches with your database. I highly recommend you structure your database properly.
With that said, I believe you want a Full Outer Join. Those aren't supported by Access natively, but there is a Microsoft Article about hacking one together: http://office.microsoft.com/en-us/a...n-outer-join-query-in-access-HA001034555.aspx
The keyword there being "hacking".
With that said, I believe you want a Full Outer Join. Those aren't supported by Access natively, but there is a Microsoft Article about hacking one together: http://office.microsoft.com/en-us/a...n-outer-join-query-in-access-HA001034555.aspx
The keyword there being "hacking".
Similar threads
Solved
Left or Mid?