![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Merging Tables in Access.
- Thread starter Qwertyman
- Start date
Qwertyman said:I can't figure it out at all. I have four tables all from different years in one Access file. Is there any way to merge them into one big table so i can coordinate/consolidate it better? I've searched google and no luck. thanks.
Do all four tables have the same schema?
well sort of. different labels on top.guito13 said:Do all four tables have the same schema?
On another note how do you get Access to recognize social security numbers that start with 0?
Thanks for the Excel tip i thought of that but it might take longer.
Qwertyman said:well sort of. different labels on top.
On another note how do you get Access to recognize social security numbers that start with 0?
Thanks for the Excel tip i thought of that but it might take longer.
1) The column labels wont matter, is the schema (Data type, length) the same on all tables?
2) How does it not 'regoznize' these values?
The date type is roughly the same, the length is different though. Some records have more than others.guito13 said:1) The column labels wont matter, is the schema (Data type, length) the same on all tables?
2) How does it not 'regoznize' these values?
I'm not sure how it recognizes the values or i don't get the question, sorry.
Ok, try this.
Copy and paste one of the tables into a new table, copy the structure and data both.
Write append queries to append the other three tables to the newly created table. You may have to manipulate the fields if the datatypes do not match.
If this dosent work, do it the ugly way and paste the data in access and then import it into another table.
As far as the SSN's, just write an update query to pad the left of the numbers with zeros if they are not 9 characters long.
Without knowing more I cannot be of much more help.
Copy and paste one of the tables into a new table, copy the structure and data both.
Write append queries to append the other three tables to the newly created table. You may have to manipulate the fields if the datatypes do not match.
If this dosent work, do it the ugly way and paste the data in access and then import it into another table.
As far as the SSN's, just write an update query to pad the left of the numbers with zeros if they are not 9 characters long.
Without knowing more I cannot be of much more help.
nonsequitur
[H]ard|Gawd
- Joined
- Feb 13, 2002
- Messages
- 1,525
you should be able to set up queries to append data from one table to another (preferrably a fifth unique table). you can add a field to the final data and show were the record came from (if it's not obvious to the user).
if the ssn's need formatting, you can take care of it at that time.
personally, i prefer all unique id's i work with be text (i work with hospitals) and patient numbers, insurance plans can start with zero's. when converting text to number, you can always evaluate the size and add zero's while converting.
if the ssn's need formatting, you can take care of it at that time.
personally, i prefer all unique id's i work with be text (i work with hospitals) and patient numbers, insurance plans can start with zero's. when converting text to number, you can always evaluate the size and add zero's while converting.
well for the SSN this is what happened. In excel i set the format for SSN's to go like this 000-00-0000 so it picked up the number when i typed it in, but it eliminated it in the fx portion. Access is only seeing it as 8 numbers not 9.
As for my other problem i will arrange all the tables together in excel so i can make one big one in access. Appending the tables wasn't working too well. Thanks for all your help, I appreciate it!
As for my other problem i will arrange all the tables together in excel so i can make one big one in access. Appending the tables wasn't working too well. Thanks for all your help, I appreciate it!