Beginner Q: How to separate data from Excel into related tables and maintain relationship?
Beginner Q: How to separate data from Excel into related tables and maintain relationship?
(OP)
I would appreciate it if someone here could provide input regarding my steps to separate imported Excel data into 3 related tables and advice on how to implement it.
Here's more detail - the Excel data is in 2 separate worksheets. Each sheet has a line of data for each day there is a record. I can easily import each of these sheets into 2 new temporary tables and I have done so.
I have also created 3 separate tables that will eventually contain the data from Excel:
My next step(s) eludes me. I want to get the data from the temporary tables to the 3 tables noted above, but I do not know how to have the RecordID match appropriately. I understand I can use an append query to copy data from the temp tables to the 3 target tables but how do I also have tblOpenData and tblRestrictedData include the RecordID from tblRecordData ?
After this gets up and going, our goal is to add more tables linked to tblRecordData thru 1-many relationships, but one step at a time.
Thank you for taking your time to review this post
Here's more detail - the Excel data is in 2 separate worksheets. Each sheet has a line of data for each day there is a record. I can easily import each of these sheets into 2 new temporary tables and I have done so.
I have also created 3 separate tables that will eventually contain the data from Excel:
- tblRecords with fields for RecordID, RecordDate, RecordNotes, etc. The RecordID field is an Autonumber field and the other fields hold data from Excel
- tblOpenData with fields OpenDataID, RecordID and other non-sensitive data. The OpenDataID field is an Autonumber field, RecordID is a foreign key to tblRecords and the other fields hold data from Excel
- tblRestrictedData with fields for RestrictedDataID, RecordID, and other restricted data fields. The RestrictedDataID field is an Autonumber field, RecordID is a foreign key to tblRecords and the other fields hold data from Excel (fyi - this is not highly sensitive data, the restictions are more for practicallity, not data security)
My next step(s) eludes me. I want to get the data from the temporary tables to the 3 tables noted above, but I do not know how to have the RecordID match appropriately. I understand I can use an append query to copy data from the temp tables to the 3 target tables but how do I also have tblOpenData and tblRestrictedData include the RecordID from tblRecordData ?
After this gets up and going, our goal is to add more tables linked to tblRecordData thru 1-many relationships, but one step at a time.
Thank you for taking your time to review this post
RE: Beginner Q: How to separate data from Excel into related tables and maintain relationship?
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Beginner Q: How to separate data from Excel into related tables and maintain relationship?
As I learn of the advantages of Access and the limits of Excel it makes sense to transition to Access. Conceptually, all the data could be manually entered into Access, but given that there are thousands of records this is not practical.
RE: Beginner Q: How to separate data from Excel into related tables and maintain relationship?
> I want to get the data from the temporary tables to the 3 tables noted above
It looks to me that you do have data in those "temporary tables".
So, my question is: how do you know which record from one temp table belongs to which record in the other? Do you match them by certain field that contains a date?
And yes, Access would be a way to go, by-passing Excel. But you do want to transfer the data from Excel to Access, right?
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Beginner Q: How to separate data from Excel into related tables and maintain relationship?
If you populate your tblRecords with the data from your tblRecords_Temp with something like:
CODE
(assuming the RecordDate is the field that is unique in tblRecords and is corresponding to another field in tblOpenData and that is how the 2 tables have related data)
You can populate your tblOpenData with the data from tblOpenData_Temp by something like:
CODE
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Beginner Q: How to separate data from Excel into related tables and maintain relationship?
how do you know which record(s) from tblOpenData relate to which record in tblRecords?
You need one or more fields to join in any two tables.
What are they?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein