Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
MrStohler

MrStohler

Technical User
Feb 1, 2003
38
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
> 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
 
Upvote 0 Downvote
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 ([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
 
Upvote 0 Downvote
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
944
SkipVought
S
puforee
  • Locked
  • Question
Best way to setup tables for multible related data sets
Replies
1
Views
539
dhookom
dhookom
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
543
dhookom
dhookom
oxicottin
  • Locked
  • Question
Creating tables and relationships for new database
Replies
2
Views
4K
MajP
MajP

Part and Inventory Search

Sponsor

Back
Top