From temporary table to master table and then join this in junctiontable

M

moso97ad

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 data:

[import_tbl_contract]
contract_number
contract_title
start_date
end_date

[import_tbl_products]
product_number
product_text
price

I now append these data to the following two tables:

[tbl_contract]
contract_id (PK)
contract_title
start_date
end_date

e.g.
1; 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]
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
 
D

Dorian

I don't see how you can since there is nothing in your data that links
product to contract.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
J

John W. Vinson

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

[tbl_contractdetails]
contract_detail_id
contract_id
product_id
Click to expand...

You can't create a (useful) junction table from *just* the contract and
product tables. The whole point of a junction table is that a record only
exists when there is in fact a real-life connection between a particular
Contract and a particular Product.

You could run a "Cartesian join" query adding every possible combination of
contracts and products, but that would be pointless, since it would imply that
every contract in fact involves every single product; if you had 1000
contracts and 500 products, you'ld get 500,000 rows.

Do any of your spreadsheets provide a logical link between products and
contracts?
 
M

moso97ad

On 8 Apr., 17:52, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
..
Do any of your spreadsheets provide a logical link between products and
contracts?
Click to expand...

I have the contract_number which links the contract and products.

Morten
 
J

John W. Vinson

On 8 Apr., 17:52, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
.

I have the contract_number which links the contract and products.

Morten
Click to expand...

No, it does not, not based on what I've seen.

The contract number doesn't "link" anything in the example you posted. If I
asked you "What products are related to contract 123?" where would you look
for the answer?

Please explain where in your data there is some connection between the table
of contracts and the table of products. If it exists you haven't posted it.
 
Top