×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread700-1811302

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:
  • 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)
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

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

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

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

(OP)
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.

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

> 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

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

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

CODE

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:

CODE

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

---- 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?

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close