Creating new tables from one big excel nightmare

[vegemite]

I have the lovely task of creating a job management database from one big gnarly excel table. I really havent used access for over a decade and some is like riding a bike but some more difficult. I think more difficult too as I am using existing data rather than starting fresh. I want to split off into tables for some of the repetitive info (like clients, job type etc...)
I was thinking of this (but please you wont offend me if my thoughts are way off track like I said, I am a long time away from access)

I have my full table
Create a new table for job type
Give job types an ID
Generate a new field in my full table that just holds the ID
then lookup in my forms.

Would that work?
Spoiler - I tried but either I AM way off track or my lookup just didnt work. Can you even look up retrospecitvely?

Cheers!
Meg

 

[June7]

Don't know what 'retrospectively' means in this context.

The process you outlined looks reasonable. How did you populate the new foreign key ID field in main table?

Would have to know the settings for combobox lookup to know if you are off track. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[arnelgp]

the easiest way around is get your worksheet, backup first.
create new access database.
on the worksheet. copy the sheet you are working on to another sheet (dup copy).
on the other copy of the sheet, you Remove Duplicates. remove unnessesary columns.
verify if there is still duplicate records, if non, Import it to access. make sure to add autonumber to the table that is generated.
this will serve as your Master job table.

now import the other sheet also.
this will be your detail table.
add a column (say, JobID, the FK to master job table, (number / Long).

update this JobID, linking your table to the master job table by "job name" or any other common field:

update jobDetail Inner Join JobMaster As T1 on JobDetail.JobName = T1.JobName set JobID = T1.ID;

after you updated the detail table, you may remove unnecessary fields like "job name", etc. and only retain JobID field.

 

[vegemite]

Thanks guys! you are awesome!
I had mapped out the tables I needed the joins etc (I'm not that rookie ;-)) and I did de dup and create the new tables. I meant retrospectively in the sense that I want to create the links now from existing data (and when you add a new job pulldown from the client table).
I slept on it and I was overthinking the join - sometimes a sleep helps! But also the confidence from you guys that I was on the right track.
Cheers!
Meg