-1

Can I use two joins between two tables in Access database?

I have a customer database, and my customer names appear in two different fields and I want to convert customer names into the short names and return that in a query in one single field.

In attempt to solve that I have created a second table with all the customer names and their abbreviations then linked "CustName" field with the "Customer_Name" field in my main table, in my query I am returning the short names of my customers. The struggle is that some customer names e.g Toyota appear in "customer_Plant" field instead of "customer_Name" field (please see picture). I want to use different Toyota shortnames by each plant location. Another difficulty is that the "Customer_Plant" field in my original table is not always populated, except for Toyota.

Is there any way I can use multiple relations between two different tables? so that access query can return short names, not just by "customer_Name" but also by "Customer_Plant" at the same time.

Access does not allow me to join "Customer_Plant" with "custPlant" if one join is present between the tables. Is there any other way I can achieve this?

Tbl_claimdata & tbl_custShortName:

Join between the tables:

Current Output:

moneebtrq
  • 1
  • 1
  • Hi. "Relations" in the sense of FKs are not needed to query. *Tables* represent relation(ship)s/associations, and table aliases are a way to refer to multiple copies of the same table. (An alias actually denotes a typical *row* from an input table.) [What is a self join for?](https://stackoverflow.com/a/37384306/3404097) Please read & act on [mcve] which includes full cut & paste & runnable code as text in the question itself, including code to generate input tables, which should be formatted as tables. (Use images/links only when necessary or supplemental & avoid links if you have the rep. – philipxy Aug 13 '18 at 09:31
  • Please more clear. Why can't you just join once & keep rows where one column OR (else) another matches? Please clearly show your original input tables & your derived intermediate tables & the desired output for them & show any queries for parts that you can manage. – philipxy Aug 13 '18 at 09:45
  • I am new to this, sorry if my question was not clear. I revised the question, added more pictures showing all the tables, joins and current output. Will you please have a look? thanks – moneebtrq Aug 13 '18 at 11:42
  • Hi. Please act on all the content of my previous comments. You added some images via links but that is something I specifically said not to do. (Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097)) @Brian Please don't inline images that should be text. Comment to the poster that it should be text. For a table, preferably executable as part of a [mcve].) PS Re 'more clear': Are are you using SQL or just gui? Why were you not helped by googling, say, 'site:stackoverflow.com join same table twice in access'? – philipxy Aug 13 '18 at 17:35
  • Might want to correct spelling of `Description` in field name. – June7 Aug 13 '18 at 20:00

1 Answers1

0

If Plant name is not provided in either or both tables, consider:

Query1: Claims_ADJ

SELECT tblCustClaimData.Customer_Name, tblCustClaimData.Customer_Plant, Nz([Customer_Plant],[Customer_Name]) AS LinkNameClaim FROM tblCustClaimData;

Query2: Short_ADJ

SELECT tblShortCustName.CustName, tblShortCustName.PlantName, Nz([PlantName],[CustName]) AS LinkNameShort, tblShortCustName.ShortName FROM tblShortCustName;

Query3:

SELECT Customer_Name, Customer_Plant, ShortName FROM Short_ADJ RIGHT JOIN Claims_ADJ ON Short_ADJ.LinkNameShort = Claims_ADJ.LinkNameClaim;

Query3 is not updatable so probably useful only for a report.

So alternative is DLookup in query (queries 2 and 3 not needed) or textbox:

DLookUp("ShortName","tblShortCustName","Nz([PlantName],[CustName])='" & Nz([Customer_Plant],[Customer_Name]) & "'")

June7
  • 19,874
  • 8
  • 24
  • 34
  • Hello, thanks for the advise, I think I can't use foreign key because I do not populate tblCustClaimData myself, but I want to make changes in the naming convention when I run the query. Therefore I created a second table and linked them on names, In join properties I select "include all records from tblCustClaimData". I include "shortName" field in my query that returns good results except Toyota, because I want different short name for Toyota by each plant location. – moneebtrq Aug 14 '18 at 07:59
  • Then you have to use composite key (compound join). Or DLookup(). – June7 Aug 14 '18 at 08:33
  • Thanks @June7, Yes composite key works but the problem is that it requires both fields to be always populated, which is not true in my original database. If 'plant' field was always populated then I might as well have just picked plants and replaced them with their customer short names. Although I am not familiar with Dlookup, will it work if some plant names are missing, (toyota plant names are always there) – moneebtrq Aug 14 '18 at 09:49
  • You build the tblShortCustName so there should always be a value in the CustPlant field. And yes, if the plant is unique value then can just link on that one field. – June7 Aug 14 '18 at 17:00
  • If you mean Customer_Plant field is sometimes not populated then that does complicate. Please edit your question to add the info provided in these comments. – June7 Aug 14 '18 at 17:08
  • Yes that is complicated, anyway thanks for your help – moneebtrq Aug 15 '18 at 09:32
  • Your example data does not show Customer_Plant empty in tblCustClaimData. It does show CustPlant empty in tblCustShortName. CustPlant should never be empty because you build and populate this table. You need to clarify the situation. Edit question. See edited answer. – June7 Aug 15 '18 at 19:01