join two tables in junction table

[Morten]

Hello,

I have imported two spreadsheets from excel to two temporary tables called "import_tbl_contract" and "import_tbl_products". I do this on daily basis and import many spreadsheets.

I end up with these two import tables:

[import_tbl_contract]
contract_number
contract_title
start_date
end_date

e.g.
09070605_p01; Syringes and needles; 01-01-2010; 31-12-2010

[import_tbl_products]
product_number
product_text
price

e.g.
02030405; syringe 1; dkk 12,00
05063004; syringe 2; dkk 10,00
74737327; syringe 3; dkk 15,00

I now append these data to the following two tables:

[tbl_contract]
contract_id (PK)
contract_number
contract_title
start_date
end_date

e.g.
1; 09070605_p01; Syringes and needles; 01-01-2010; 31-12-2010

[tbl_products]
product_id
product_number
product_text
price

e.g.
1; 02030405; syringe 1; dkk 12,00
2; 05063004; syringe 2; dkk 10,00
3; 74737327; syringe 3; dkk 15,00

I now want to join the data in a junction table:

[tbl_contractdetails] (junction table)
contract_detail_id
contract_id
product_id

e.g.:
1; 1; 1
1; 1; 2
1; 1; 3

How can I do this, when I on daily basis import many spreadsheets? I'm a newbie to SQL and VBA.

Thanks in advance.

Morten

 

[HiTechCoach]

The part I do not understand is how you match the data together.

Is is possible to attach a sample of the spreadsheets?

 

[FoFa]

I am assuming syringe 1you are matching to Syringes and needles.

It would appear from this limit view of your data, and assuming I am correct in the above sentence, a cross reference table saying syringe 1 = Syringes and needles,
syringe 2 = Syringes and needles, syringe 3 = Syringes and needles
Then you could use those in a join statement with the cross reference table in the middle pointing to the proper cross reference.

 

[Morten]

I have now attached my excel file and database. In the sheet "Data" you can see how the contract and products matches. I have a contractnumber which is "09070605_p01" and in this contract we have three products, which are:

02030405; syringe 1; dkk 12,00
05063004; syringe 2; dkk 10,00
74737327; syringe 3; dkk 15,00

I only import one contract at a time.

I hope you guys can help me.

 

[Morten]

No one can help me?

 

[FoFa]

it seems to me the question is what ties these two tables together. I.E. how do you know the products go with that Contract?
A cross reference or juncture table maybe the proper approach, unless the data in your products table is unique to a specific contract. If the product data is unique to a specific contract, I would store the contract number on each product (you still have to match them somehow). If a product can belong to multiple contracts, than a cross reference table is the better choice.