URGENT: How to create a query based on composite key from two table

[elly.khanlar]

Hi everyone,
I'm trying to create a query by joining two tables based on their composit key. I have tried to build the composit key for two tables. However, in the design grid the name of composit key for each table doesnt appear so I can join them?

 

[theDBguy]

Hi. That's correct. You don't link tables using index. Instead, you use fields to link them. So, try linking all the fields you used in the index in your query.

 

[plog]

Your phrasing around composite key makes me feel like you don't fully understand what one is. A composite key is the combination of multiple fields in one table that makes the record unique:

https://www.javatpoint.com/sql-composite-key

For example a SalesPersonID and SalesDate could make a composite key in a table that lists all sales:

DailySales
SalesPersonID, number, foreign key to SalesPerson table
SalesDate, date, date of sale
SalesQuantity, number, number of sales for that salesperson on that date

In DailySales, every SalesPersonId and SalesDate permutation is unique. There will never be multiple records that have both the same SalesPersonID and SalesDate values. That is a composite key.

Perhaps you can demonstrate your issue with data.

 

[elly.khanlar]

From table A here are fields
Delivery location,location desc,material,material desc,volume,month,quarter
the composite key for this table is Delivery location and Material


From table B here are fields,
Delivery location,location desc,material,material desc,month,origin

the composite key for this table is delivery location and material


by joining these two tables composite key, I would like to create a table that will add origin to table A based on the delivery location and material

 

[theDBguy]

From table A here are fields
Delivery location,location desc,material,material desc,volume,month,quarter
the composite key for this table is Delivery location and Material


From table B here are fields,
Delivery location,location desc,material,material desc,month,origin

the composite key for this table is delivery location and material


by joining these two tables composite key, I would like to create a table that will add origin to table A based on the delivery location and material

Hi. Just a guess (without anything to use for testing), but you could try something like:

SELECT B.Origin
 INTO NewTable
FROM TableB B
 INNER JOIN TableA A
ON B.[Delivery Location]=A.[Delivery Location]
  AND B.Material=A.Material

(untested)