Dlookup in Query

[Milothicus]

i'm importing data from a spreadsheet. i've imported data from excel into a new table (tblImport), and need to distribute it across my existing tables, taking the relationships into account. i populated my main table with an append query, and that worked fine. now i need to populate 'tbldetails', which is the 'many' table in the one-to-many relationship with the main table (tblOrders).

each record that is going into tbldetails is associated with a record in tblorders in the proper primary key/foreign key way. the problem is that there's no reference to tblorder's primary key in the imported data. each detail record is associated with a job number, and a sequence number (each job has it's own set of sequence numbers: 1,2,3, etc) so here's what i want to work in my append query:

OrderFK: DLookUp("[OrderID]","tblOrder","[tblOrder]![Pnum]= '" & [PnumNew] & "'And [tblOrder]![SeqNum] = " & [SeqnumNew])

so for each imported record, it looks up the primary key in tblOrders, matching up the order number (Pnum) and sequence number (SeqNum).

PnumNew and SeqnumNew are in the import table. these are what i'm not sure how to refer to and have the query go through each record to find an OrderID for it...

any help? can i clarify anything for you?

 

[aleb]

So what is the problem ?
Pull two tables link them by job number, leave the PK field from the first table and order details fields from the second table, run query ,check if everything correct and then create "make table query" out of it. Then add the new field to the newly created table and set it to autonumber.

Am I missing anything here ?