Append data into multiple related tables

[jaryszek]

Hi Guys,

I have database like in attachment. It is simple example of relationships.
Data can be different, this is not about model.

I am getting data in Excel.
Also in attachment.

How can i append these data into multiple related tables? What is your solution if you are getting more data to input?
I can go through each form and add People, and Machine step by step, one by one. But when i will have thousends of records i need somehow to automate it.

I am usually prepare data in Excel - looking for foreign keys using vlookup and appending this data into Access table using append query (for one table).

What is your best practice?
Please help,
Best,
Jacek

 

[Cronk]

Firstly, about your relationships, is there any reason for having outer joins to tblvRAM and tblvCPUNumbers? Without referential integrity, you can have orphan IDs in tblMachine.

Also I wouldn't have a lookup table for recording RAM size - it's just a number. OTOH, if you were recording other RAM info such as speed, manufacturer etc, it would be a different matter.

Do you really need to record a CPU number? Again, why not record it directly in tblMachine?


As to adding data, I'd have a primary form showing a person with a subform showing the machine(s) allocated to that person, together with a new PC button which would open a form for inputting a new PC.

 

[jaryszek]

thank you Cronk.

As to adding data, I'd have a primary form showing a person with a subform showing the machine(s) allocated to that person, together with a new PC button which would open a form for inputting a new PC.

so still you have to break up data from Excel or other files and open each form and paste them. Or input them manually step by step.

What about thousends of records to input.
How do yo handle data?

Also inputting manually into subforms? Or breaking down and using append queiries?
Or different ways?

Best,
Jacek

 

[Cronk]

Misunderstood your question.

You can import Excel data into Access. I'd import the whole un-normalized data into a temporary table with a unique ID, call it (say) TempID.

Then use a query to create people table, with only people data but also import the TempID. Do the same for the Machines.

Using queries joining tblPeople, tblMachines and tblTemp, you can create the join table using the TempID to join the tables.

If you get stuck, come back with a cut down copy of your database with the imported tblTemp containing about a dozen or so records.

 

[jaryszek]

Hi Cronk,

i added data from Excel to database to table DataTemp.

Also, i created 2 queries:

MachineTemp and PeopleTemp with TempID.

Using queries joining tblPeople, tblMachines and tblTemp, you can create the join table using the TempID to join the tables.

Now i stucked. Can you help me?

Best,
Jacek

 

[Cronk]

Delete all the relationships between the tables - you can add them later when data is imported.

Delete all records in tblMachine.

Add new number fields to tblMachine (TempID, MachineRam, MachineCPU). The RAM and CPU Ids can be added later

Then run the following query to add records to tblMachine

INSERT INTO tblMachine ( TempID, MachineRam, MachineCPu )
SELECT DataTemp.TempID, DataTemp.MachineRam, DataTemp.MachineCPu
FROM DataTemp;

Running the following query will populate tblPeopleMachine

INSERT INTO tblPeopleMachine ( MachineID, PeopleID )
SELECT tblMachine.MachineID, tblPeople.PeopleID
FROM (DataTemp INNER JOIN tblMachine ON DataTemp.TempID = tblMachine.TempID) INNER JOIN tblPeople ON DataTemp.People = tblPeople.PeopleName;

 

[jaryszek]

hi Cronk,

thank you.
This is working.

The RAM and CPU Ids can be added later

How this you are adding ?

All would be ok if you do not delete relationships and present records.
When you will have thousends of records and want to add new ones - it would be strange to delete old records.

But good idea to add new temp fields and based on that connect all.
Very nice idea.

Thank you,
Anyone else?

Best,
Jacek

 

[Cronk]

How this you are adding ?

Firstly, re-read my comments in #2. Why do you want to use IDs when you are storing a numeric value? Also I note that you have some values for RAM which are not in the lookup up table, so there would be no index.
If you still want to go ahead, use an append query by linking tblMachine to the lookup table on the value and update tblMachine with the index from the lookup table.

 

[jaryszek]

hi Cronk,

i read your comments, thanks for that.

If you still want to go ahead, use an append query by linking tblMachine to the lookup table on the value and update tblMachine with the index from the lookup table.

But yes, i want to go ahead.

You mean something like this? :

"INSERT INTO tblMachine ( RAMID )
SELECT tblvRAM.RAMID
FROM tblvRAM INNER JOIN tblMachine ON tblvRAM.RamSize = tblMachine.MachineRam;"

Best wishes,
Jacek

 

[Cronk]

Looks OK. Did you try it?

 

[jaryszek]

yes it is working, i like your method Cronk,

thank you!