Excel Import

[Guest]

Here's my table structure:

KTFJobs:

pk JobNumber : Number
JobTitle : Text
KTFValue : Currency
LabourBudget : Currency
MaterialBudget : Currency
QS : Number
CM : Number
SF : Number
StartDate : Date/Time
FinishDate : Date/Time
Address1 : Text
Address2 : Text
City : Text
County : Text
PostCode : Text

tblGroup:

fk JobNumber : Number
pk GroupID : AutoNumber
GroupName : Text

tblLocation:

fk GroupID : Number
pk LocationID : AutoNumber
LocationName : Text

tblTask:

fk LocationID : Number
pk TaskID : Autonumber
TaskName : Text
Price : Currency
ScheduledStart : Date/Time
ScheduledFinish : Date/Time

I have setup a spreadsheet in excel for inputting all the data (it is much
easier quicker this way) and just need to import that data into my tables.
Here's what I need:

1) Select the excel file to be imported
2) Check if the Job Number already exists (if it does, stop the import)
3) Each of the fields in KTFJobs is related to a specific cell in the excel
sheet, this information needs to be imported and appended to the KTFJobs
table.
4) Each group has many locations, each location has many tasks. In my
spreadsheet, this information is input, denormalised. In the following format:

GroupName LocationName TaskName Price Start Finish

Group1 3 The Road Decs 150 3/5/06
4/5/06
Group1 4 The Road Decs 170 3/5/06
4/5/06
Group2 7 Some Street Repairs 200 4/5/06
5/5/06
Group2 7 Some Street Decs 150 6/5/06
7/5/06

etc.

This information needs to be imported into the appropriate tables,
maintaining the relationships. All the information in the spreadsheet can be
formatted or unformatted as necessary.

I would appreciate any help on each of the above steps.

Thanks,

Dave

 

[Guest]

Link to the spreadsheet using the TransferSpreadsheet method.
Use append queries to append the columns in the spreadsheet to the fields in
the tables (one append query per table). Start with the highest level
parent. Then you will need to join the parent table with the spreadsheet
table to create the foreign keys for the child table append.