Linking 2 tables on 2 fields (1 Viewer)

AnilBagga

Member
Local time
Today, 09:55
Joined
Apr 9, 2020
Messages
105
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
 

Attachments

  • PP_Test-15Aug.zip
    211.9 KB · Views: 12

Gasman

Enthusiastic Amateur
Local time
Today, 04:25
Joined
Sep 21, 2011
Messages
7,145
I believe you add the table a second time and link the second field to that.?
 

AnilBagga

Member
Local time
Today, 09:55
Joined
Apr 9, 2020
Messages
105
Then which table do I refer to to use the total discount value? CustDisctbl or CustDisctbl_1?
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:25
Joined
Sep 21, 2011
Messages
7,145
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

Super Moderator
Staff member
Local time
Today, 04:25
Joined
Feb 19, 2013
Messages
12,585
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

Banishment Pending
Local time
Yesterday, 22:25
Joined
May 11, 2011
Messages
10,127
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:

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

Super Moderator
Staff member
Local time
Yesterday, 23:25
Joined
Feb 19, 2002
Messages
30,098
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

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 22:25
Joined
Feb 28, 2001
Messages
18,349
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

Super Moderator
Staff member
Local time
Yesterday, 23:25
Joined
Feb 19, 2002
Messages
30,098
I guess we're seeing two different questions.
 

Users who are viewing this thread

Top Bottom