Creating new tables from one big excel nightmare (1 Viewer)

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

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.

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:
after you updated the detail table, you may remove unnecessary fields like "job name", etc. and only retain JobID field.

We have no idea how many tables you need at this point and neither do you. Start by identifying entities. Jobs, customers, employees, tasks might be a start depend on what you mean by "jobs". That word is used for many different types of applications from high rise building construction to gardening.

Once you have identified some entities, figure out what attributes (fields/columns) go with each entity. I'll do people since that's pretty standard:
FirstName
LastName
MailStop
WorkPhone
CellPhone
HireDate

These attributes all occur ONCE for a person.

You should end up with some number of tables and be able to distribute all the columns of the original spreadsheet to one and ONLY one table. To connect all the tables, you will need either junction tables for many-many relationships such as customers-jobs or foreign keys to connect "child" tables to their "parent" table. Tasks would relate to one and only one job so the task table would be a child of the job table and each row in the task table would have the JobID of its parent job.

Work on that for a while and get back to us.

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

Sometimes you'll be lucky and there will be natural keys in your Excel data but most of us aren't. That means that you'll need to create them as you convert the data. I generally build a function to run all the queries in the correct order so as I refine things, I don't break anything that already works or forget it. You will almost certainly need to do the conversion several times before you are happy with the results.

Here's what I do.

1. I import the spreadsheet because I usually need to add a few key columns to it that will help me to build foreign keys as I load the child tables.
2. Create separate make table queries for the top tier entities.
3. Create matching update queries to run after each make table query. You join the new table to the big spreadsheet table on the unique matching fields and update the foreign key field for each entity.
4. When you run the make table queries for the child tables, use the foreign key you stored in the big spreadsheet table.
5. As a sanity test when all the tables are loaded, try to create a query that reproduces the spreadsheet. If your result doesn't match, figure out why.