Lookup multiple fields

[deemat]

Hello,

Could someone help with this please-

I have 2 tables. I need to match the Trf column to either the Elec or Gas column from table2 and if the IDs match in either of the 2 columns (Elec or Gas) then return the agent name from table2. If none of these match then continue to lookup column XX from table1 and see if there is a match in column XX in table2 and return agent name from table 2. If still no match, continue to look up column YY from table1 and see if there is a match in column YY in table2 and return agent name from table2.

I have tried various joints on the tables but have not managed to get far with this.



TABLE1

Trf	XX	YY	Can Date	Number
2459587347		5247174941	8/07/2015	1
8970564231		5641208774	11/07/2015	1
2911176586		5244712677	20/07/2015	1
1234567890	1234408621		18/08/2015	1
7777897588	789456311		20/07/2015	1
442591000	4103809841		18/08/2015	1

TABLE 2

Agent	Created Date1	Elec	Gas	XX	YY
Dummy	08/07/2015	2459587347	7777897588	4963094541	4561145846
User	10/07/2015	3958666710	0	4001180789
Name	11/07/2015	0	9279007776		5641208774
Trial	11/07/2015	2878854457	0	1234408621
Test	16/07/2015	0	442591000		1234596116
Name1	18/08/2015	0	0	6203123216	5642243427
User1	18/08/2015	0	0	6305605624
Dum12	20/07/2015	2911176586	0	4102766798

Appreciate your assistance.

Thank you

[Micron]

Did you consider a union query? That's how I'd approach this first.

[deemat]

Thanks Micron,

I tried union but it did not run. Also got an error 'Invalid null'. Could you please advice of the union query you're thinking of in this case..

I did think if creating 4 different table that gave me agent names if there is a match in the respective columns(Gas, Elec, XX and YY)then do a union with these. But this would not work as a record could have 2 matches and would then appear 2 times. Also, this does not seem to be the best approach as quite messy.

Thanks

[Micron]

Here's one version of what you asked for, although when you see it, I think you will decide it's not what you wanted. However, it does produce a result. Field/table names are based on your post, so you'll have to change if required. Maybe it will allow you to acheive what you need with some work. I'll work on the union part of things later (watching Blue Jays kick Texas Rangers' butt)...

SELECT Table2.Agent, Table2_1.Agent AS XXAgent, Table2_2.Agent AS YYagent
FROM ((Table1 LEFT JOIN Table2 ON Table1.trf = Table2.Elec) LEFT JOIN Table2 AS Table2_1 ON Table1.XX = Table2_1.XX) LEFT JOIN Table2 AS Table2_2 ON Table1.YY = Table2_2.YY;

[Micron]

I'm wondering if you don't quite grasp how a union query works and how to build one (sorry if that's unfair) but my example below works.
It's simply two or more select queries that are strung together. Each gives you PART of what you want from a set of fields when data is in different columns of the same or multiple tables and is related to one or more fields from some other table(s). I put this together for you during commercials .
Note: just make sure your trf and related fileds in table 2 are the same data type. I found that 8970564231 is large enough to be a double if it is a number type.

SELECT Table1.trf, Table2.Agent FROM Table1 INNER JOIN Table2 ON Table1.trf = Table2.Elec
UNION
SELECT Table1.trf, Table2.Agent FROM Table1 INNER JOIN Table2 ON Table1.trf = Table2.Gas
UNION
SELECT Table1.trf, Table2.Agent FROM Table1 INNER JOIN Table2 ON Table1.XX = Table2.XX
UNION
SELECT Table1.trf, Table2.Agent FROM Table1 INNER JOIN Table2 ON Table1.YY = Table2.YY;

Note that only the last statement contains the ending semicolon. Also, if you get duplicate records, you can try inserting the DISTINCT or DISTINCTROW clause:
(e.g. SELECT DISTINCT). However, I seem to recall reading that a union query only returns distinct records by design.

Post the union sql you tried if you want any analysis or comment on it.

[deemat]

Thanks for taking the time out for the responses,

I tried the above union query but gives me a syntax error. I re-named the tables and columns as required. I have posted the query i tried below

SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT Actual Saves.Transfer ID = retentionTrackerSaves.Elec Loss Transfer Case ID
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT Actual Saves.Transfer ID = retentionTrackerSaves.Gas Loss Transfer Case ID
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT Actual Saves.NMI = retentionTrackerSaves.NMI
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT Actual Saves.MIRN = retentionTrackerSaves.MIRN;

[June7]

Table and Field names have spaces. The names must be enclosed in [].

Suggest you create a field in each SELECT statement that identifies the agent group.

SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent, "Elect" AS AgentType FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT [Actual Saves].[Transfer ID] = retentionTrackerSaves.[Elec Loss Transfer Case ID]
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent, "Gas" FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT [Actual Saves].[Transfer ID] = retentionTrackerSaves.[Gas Loss Transfer Case ID]
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent, "NMI" FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT [Actual Saves].NMI = retentionTrackerSaves.NMI
UNION
SELECT RTT Actual Saves.Transfer ID, retentionTrackerSaves.Agent, "MIRN" FROM RTT Actual Saves INNER JOIN retentionTrackerSaves ON RTT [Actual Saves].MIRN = retentionTrackerSaves.MIRN;

[Micron]

RTT [Actual Saves].[Transfer ID]

The RTT portion belongs within your brackets too. The others you were probably leaving for deemat to fix I guess. Here's the entire sql corrected (I hope!).

SELECT [RTT Actual Saves].[Transfer ID], retentionTrackerSaves.Agent, "Elect" AS AgentType FROM [RTT Actual Saves] INNER JOIN retentionTrackerSaves ON [RTT Actual Saves].[Transfer ID] = retentionTrackerSaves.[Elec Loss Transfer Case ID]
UNION
SELECT [RTT Actual Saves].[Transfer ID], retentionTrackerSaves.Agent, "Gas" FROM [RTT Actual Saves] INNER JOIN retentionTrackerSaves ON [RTT Actual Saves].[Transfer ID] = retentionTrackerSaves.[Gas Loss Transfer Case ID]
UNION
SELECT [RTT Actual Saves].[Transfer ID], retentionTrackerSaves.Agent, "NMI" FROM [RTT Actual Saves] INNER JOIN retentionTrackerSaves ON [RTT Actual Saves].[NMI] = retentionTrackerSaves.NMI
UNION
SELECT [RTT Actual Saves].[Transfer ID], retentionTrackerSaves.Agent, "MIRN" FROM [RTT Actual Saves] INNER JOIN retentionTrackerSaves ON [RTT Actual Saves].[MIRN] = retentionTrackerSaves.MIRN;

His error might have been caused by the spaces, or it might have been something else as often Access will return "Can't find table/field name..." or something like that.
deemat, most experienced developers (not just Access) NEVER use spaces in ANY object names, or special characters either (exception, _ underscore is commonly used).

[June7]

Thanks for the fix, micron. I just didn't follow through.

[ssanfu]

I have tried various joints on the tables but have not managed to get far with this.

From the look of the table designs in Post #1, the tables are designed like a spreadsheet. This is not a normalized table design.

I would advise stepping back and looking at the design. You will have a lot more problems like this one if you use the current table structure (IMHO).