I have three tables linked. One table has a column for part numbers and then 4 different columns for price based on what contract a customer is on. Another table has the customer ID and the contract they belong to and the third table has the sales data by customer and part number ordered. I have the sales table and the customer table joined by customer ID and the contract table joined to the sales table by part number. Is it possible to write a query that would show the customer ID, the part number ordered, the contract they belong to, and then the price for that part number coming from the column for the specified contract. The contract numbers are the column headers in that table. The code I have so far is below, just need to add price in the SELECT clause but don't know how to only select from a specific column based on criteria from another table.
SELECT [Sales].[Customer ID], [Sales].[Item ID], [Customers].[Contract Number],
FROM ([Sales] INNER JOIN [Customers] ON [Sales].[Customer ID] = [Customers].[Customer ID])
INNER JOIN [Contracts] ON [Sales].[Item ID] = [Contracts].[Item ID];
Sales Table
| Customer ID | Item ID |
|---|---|
| Customer 1 | Item 1 |
| Customer 2 | Item 2 |
| Customer 3 | Item 1 |
| Customer 2 | Item 3 |
Customer Table
| Customer ID | Contract |
|---|---|
| Customer 1 | Contract 1 |
| Customer 2 | Contract 3 |
| Customer 3 | Contract 4 |
| Customer 4 | Contract 2 |
Contract Table
| Item ID | Contract 1 | Contract 2 | Contract 3 | Contract 4 |
|---|---|---|---|---|
| Item 1 | $20.00 | $22.00 | $24.00 | $26.00 |
| Item 2 | $3.00 | $4.50 | $5.00 | $6.00 |
| Item 3 | $45.00 | $48.00 | $50.50 | $52.00 |
Hoping to get an output like:
| Customer ID | Item ID | Contract | Price |
|---|---|---|---|
| Customer 1 | Item 1 | Contract 1 | $20.00 |
| Customer 2 | Item 2 | Contract 3 | $5.00 |
| Customer 3 | Item 1 | Contract 4 | $26.00 |
| Customer 2 | Item 3 | Contract 3 | $50.50 |
CodePudding user response：
Options - examples use fields without spaces in naming:
- DLookup domain aggregate function
SELECT Customer.CustomerID, Sales.ItemID, Customer.Contract,
DLookUp("[" & Replace([Contract], " ", "") & "]","Contract","ItemID='" & [ItemID] & "'") AS Amt
FROM Customer INNER JOIN Sales ON Customer.CustomerID = Sales.CustomerID;
- UNION query to rearrange the Contract fields to normalized structure and then 2 more queries
SELECT ItemID, Contract1 AS Amt, "Contract 1" AS Contract FROM Contract
UNION SELECT ItemID, Contract2, "Contract 2" FROM Contract
UNION SELECT ItemID, Contract3, "Contract 3" FROM Contract
UNION SELECT ItemID, Contract4, "Contract 4" FROM Contract;
SELECT Customer.CustomerID, Sales.ItemID, Customer.Contract
FROM Customer INNER JOIN Sales ON Customer.CustomerID = Sales.CustomerID;
SELECT Query2.CustomerID, Query1.ItemID, Query2.Contract, Query1.Amt
FROM Query2 INNER JOIN Query1
ON (Query2.Contract = Query1.Contract) AND (Query2.ItemID = Query1.ItemID);
- avoid the DLookup and UNION by normalizing data structure