-
a design question
Hi all,
I basically am stumped. I am taking excel ('97, we're strapped for cash!) files and converting them into tables to be imported into this Access (again, '97) DB.
I have about 5 or 6 tables with about 100 reocords each. What I want to do is make either a: query/report that will filter ALL the same records from all the tables. I have tables with the same ID number, but different fields of data in the tables. Example: in table 1 i have: ID, Status, Weight. Table2 has ID, Flow speed, alkalinity. So what I want is a DB that has the unique records with all the fields from the other tables.
I though you were supposed to link them together to do this. I did, but the query's I'm doing are prolly not creative enought to get what I need out of the DB.
Any ideas would be appreciated,
Randy
-
There is an easier way..
In Access 97 link the worksheets from the .xls file as attached tables, using File, Get External Data, Link Tables..., and select the .xls file(s) from the Link Dialog, after you have selected Microsoft Excel (*.xls) from the Files of type combobox, and then use the Link Spreadsheets Wizard...
I am not too clear from your post what data integrity issues you have, but perhaps you need to create a UNION query of all tables, and then run the Find Duplicates Query Wizard.
-
-
After you have the data as tables in Access, here is one way that works although I'm thinking there might exist a simpler way. I'm assuming there is only one record for each ID in each source table and that you will only need to end up with one record for each ID in the combined table.
First, Make a table (tblDest) whose fields are ID (key field), and then one field for each of the other fields in the other tables.
Second, make an append query for each table. Each of these queries' job is to append the ID of one of the tables to tblDest. ID being key field prevents duplication.
Third, make an update query for each table. The first query updates the records in tblDest per the field data in the first table. The second does the same for the second table. etc.
In your case you will need 10 - 12 queries for this, but you can run them in order in a macro with one touch of a button.
