Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1

[MrStohler]

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:
[ul]
[li]tblRecords with fields for RecordID, RecordDate, RecordNotes, etc. The RecordID field is an Autonumber field and the other fields hold data from Excel[/li]

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

[li]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)[/li]
[/ul]

I have built the relationships between the 3 tables using RecordID as the "link" for 1-1 relationships

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

 

[Andrzejek]

If you take just the two tables: tblRecords and tblOpenData, and forget for a moment about the AutoNumbers in both tables, how do you know which record(s) from tblOpenData relate to which record in tblRecords?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MrStohler]

The tables noted in the OP have no data, only a structure. The data is all in 2 Excel sheets currently - the date is the sort mechanism - one line of data per day.

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.

 

[Andrzejek]

> I can easily import each of these sheets into 2 new temporary tables and I have done so.
> 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

 

[Andrzejek]

I am guessing here….
If you populate your tblRecords with the data from your tblRecords_Temp with something like:

Insert Into tblRecords (RecordDate, RecordNotes, …)
(Select RecordDate, RecordNotes, … From tblRecords_Temp)

By-passing RecordID field since it is an Autonumber

(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:

Insert Into tblOpenData ([blue]RecordID[/blue], …)
(Select [blue](Select Distinct R.RecordID 
From tblRecords R, tblOpenData_Temp O 
Where R.RecordDate = O.RecordDate)[/blue], … From tblOpenData_Temp)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Andy has asked you several times, because this is a pivotal question...

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein