Linking 2 tables on 2 fields

[AnilBagga]

In the enclosed DB, qryQuoteCalculations, I want to link the tblCustRFQ with CustDisctbl on 2 fields EndCustomerCode and Group with EndCustCode and Category. The value of TotalDiscount in CustDisctbl is needed based on a combination of the endCustCode and Category

The qry gives an error if I join the 2 tables on 2 fields.

How is this done? I think it can be done without editing the SQL code. I was shown this once but cannot recall how it is done

 

[Gasman]

I believe you add the table a second time and link the second field to that.?

 

[AnilBagga]

Then which table do I refer to to use the total discount value? CustDisctbl or CustDisctbl_1?

 

[Gasman]

Then which table do I refer to to use the total discount value? CustDisctbl or CustDisctbl_1?

TBH, I do not know. I can open the RFQ table, but not the Disc table, due to my version, so I will bow out, as I cannot try anything at my end. Sorry.

 

[CJ_London]

your query is trying to link to an excel file which have not provided - so nobody can open your query

FROM ((((((tblCustRFQ LEFT JOIN tblCustFabricConvRates ON tblCustRFQ.UncoatedGSMID = tblCustFabricConvRates.StdRateID) LEFT JOIN tblCustFabricConvRates AS tblCustFabricConvRates_1 ON tblCustRFQ.CoatingGSMID = tblCustFabricConvRates_1.StdRateID) LEFT JOIN tblCustPriceMaster ON tblCustRFQ.[EndCustomerCode] = tblCustPriceMaster.EndCustCode) LEFT JOIN CustMasterTbl ON tblCustRFQ.[EndCustomerCode] = CustMasterTbl.CustCode) INNER JOIN CustDiscTbl ON tblCustRFQ.EndCustomerCode = CustDiscTbl.EndCustCode) INNER JOIN tblStdUOM ON tblCustRFQ.[UOMID] = tblStdUOM.UOMID) LEFT JOIN ShippingMaster ON CustMasterTbl.RoutingCode = ShippingMaster.RoutingID;

best I can say is you have a mixture of inner and left joins - you may need to break the query down into two separate queries and join them together

 

[plog]

The error qryQuoteCalculations gives me is that it can't find a spreadsheet linked table.

When I build a new query and JOIN as you demonstrated it works without an error, it just shows no records because there are no matches between those 2 tables on those 2 fields. Here's my SQL which works:

SELECT tblCustRFQ.RFQID, tblCustRFQ.RFQID, CustDiscTbl.ID
FROM tblCustRFQ INNER JOIN CustDiscTbl ON (tblCustRFQ.Group = CustDiscTbl.Category) AND (tblCustRFQ.EndCustomerCode = CustDiscTbl.EndCustCode);

I suggest you demonstrate your issue with data. Provide us with 2 sets:

A. Starting data. The database you provided could sufice, but I don't think there's enough records in tblCustRFQ. Populate it with more data which will actually match with records in CustDiscTbl.

B. Expected results of A. Show us what data you expect to end up with. Don't tell me how to get there, actually show me what data you expect your query to generate when you feed it the data from A.

 

[Pat Hartman]

I don't have a problem with the join.

• Open the QBE
• Add both tables
• Draw the two join lines
• Select the columns you want to show

1. Why not use consistent naming standards for your tables?
2. Joining on non-key fields can result in duplication. One or both of the tables needs to have a unique index on the two join fields. Open the Indexes dialog to do this. You cannot create multi-column indexes using the options on the fields in the tables
3. I can't tell why you need this two-column join, but the better solution is to put the PK of one of the tables into the other as a FK so the join will be on one column only.

 

[The_Doc_Man]

To answer a specific question: When you drag a table to the relationships window a second time, in the window it has a different name but it is in fact the same table. That "name_1" only appears in the relationships window. When you create a query that would use the relationship automatically, Access will use the right table. The relationship entry occurs for only a couple of reasons. (1) The various automatic query, form, and report builders use it. when appropriate (2) If you try to establish relational integrity between the related tables, the RI facility will use it. Other than those two cases, a formal relationship at that level is more for documentation. Not that there is anything wrong with that.

 

[Pat Hartman]

I guess we're seeing two different questions.