Inserting new data within related tables inside Access

[TrueMan2010]

Hi All,

I need to insert new records in existing Tables of Access. The two tables are related by Primary and Foreign key relation. The new data to be imported is in Excel sheet which I have imported into new table.

Now,

Some columns needs to be inserted into Parent table and some would be inserted to child table.
The parent table has an automatically incremented ID column which is foreign key for the child table.

The insert into Parent table works fine but nothing's happening when I try to append records to the child table.
I used this query to insert values into child tables foreign key from parent tables primary key.
No-Luck. :(
INSERT INTO ChildTable(FID)
SELECT ID
FROM Parent table
WHERE ID > 4430

NOTE: 4430 is the last number that existed in both tables before my inserts to the Parent table.

Can anyone help me ?

Thanks very much !!

[nico5038]

This can only be realized with an "external" ID linking the original Parent to it's Children.
Adding the Parent will also require the addition of the "additional" "external ID" to the Parent table.
Now the children can be added extracting the autonumber of the Parent found using this "external ID".

Getting the idea ?

Nic;o)

[TrueMan2010]

Not really !
Let me explain what I understood.
Table1 (parent)
ID1 A B C

Table2(Child)
D E F ID1 ID2
So, I have to add an auto increment column say(ID3) in both the tables?
Then, insert new records in Table1.
After this operation ID1 gets populated by new values. Then what would be the next step?
Use ID3 values to get values in field ID1(table1) and insert them in ID1(table2) using ID3?
and then insert rest of the data in table2?
I appreciate you reply.:o)
Thanks !

[nico5038]

Input table data:
Table1 (parent)
ID1 A B C

Table2(Child)
ID1(FK) ID2 D E F

Database tables:
Table1 (parent)
NewID1 A B C OldID1

Table2(Child)
NewID1(FK) ID2 D E F

It's assumed that the ID1 from the input is stored in OldID of the parent table. The NewID (autnumber) needed for adding the child, can than be "extracted" by using the ID1 from the input to extract the NewID from the parent they belong to.

The OldID could be emptied or removed after the import, as after adding the children it's no longer needed.

Nic;o)

[TrueMan2010]

Thanks very much !! Nic,
I'll try your solution and post back with the results.

Kind Regards,
Trueman

[TrueMan2010]

Hi Nic,
I used this query to insert records into child table

Insert into Child(Field5, Field6, ID)
From Import inner join Parent on
Import.Field1 = Parent.Field1and
Import.Field2 = Parent.Field2

Thanks !!
Trueman

[nico5038]

I see you got the message :-)

Success with your application !

Nic;o)

[TrueMan2010]

Nic,
Can you take stab on this one please?

UPDATE tblA
SET tblA.Field0 = tblB.Field0,
tblA.Field1= tblB.Field1,
tblA.Field2= tblB.Field2,
tblA.Field3= tblB.Field3,
tblA.Field4= tblB.Field4,
tblA.Field5= tblB.Field5 FROM tblB INNER JOIN tblA ON tblB.[PrimaryLocation] = tblA.PID;

Error: missing operator in expression 'tblA.Field5=..............=tblA.PID'

Just trying to update certain records in one table based upon corresponding records from another table.
In some another forum I got the idea of using DLOOKUP and I'm looking into that as well.

Kind Regards,
Trueman :)

[nico5038]

You could probably use:

tblA.Field5 = (SELECT tblB.Field5 FROM tblB WHERE tblB.[PrimaryLocation] = tblA.PID)
 

This should be faster as the DLOOKUP() function that's rather "slow".
Another (faster) solution could be to perform first a DELETE * from all unique ID's in tblA that also resides in tblB and than Append tblB completely.

Nic;o)

[TrueMan2010]

Thanks very much !!

Both of the ways work good. I was using 'from' clause in my update query hence syntax error.
Removing that and rearranging the query yielded results. :)


Regards,
Trueman