Help with query

[ironwood9 via AccessMonster.com]

I need help re-designing a query to get the desired results that is source by
a query that uses temp/alias fields, from two tables

tblCust
CustID

tblEquipTrans
EquipTransID PK- Autonumber
EqMoveFrom ----- > populated with CustID #
EqMoveTo ----- > populated with CustID #
MoveCode

I need the results to just have three columns – from tblCust, look for match
on CustID in tblEquipTrans, either in the “EqMoveFrom†field, or the
“EqMoveTo†field. I need to show the CustID in the first column that it found
a match to – if it finds the matching number in the “EqMoveFrom†field, or
the “EqMoveTo†field, it doesn’t matter – show the OTHER CustID, and show the
MoveCode for that record. If there is no match, show the custID value, “No
Match†in the second column, and “n/a†or simply null in the third column.

Something like:

CustID……MatchID…..MoveCode
12181………39621…………T
12181……….54896…………C
65488……….NoMatch……(null)
34219……….51322 T

The query I’m trying to alter is q_EquipMoveTwoColumns
It’s based on this query, q_EquipMove:

SELECT DISTINCT C.CustID, T.EqMoveFrom, T.EqMoveTo, T.MoveCode
FROM tblCust AS C LEFT JOIN tblEquipTrans AS T ON C.CustID=T.EqMoveFrom Or C.
CustID=T.EqMoveTo
ORDER BY 1, 2, 3;

The SQL for q_EquipMoveTwoColumns is:

SELECT q_EquipMove.CustID, IIf(IsNull([EqMoveFrom]+[EqMoveTo]),"No Match",IIf
([EqMoveFrom]=[CustID],[EqMoveFrom],[EqMoveTo])) AS EqMove
FROM q_EquipMove;

 

[smartin]

I need help re-designing a query to get the desired results that is source by
a query that uses temp/alias fields, from two tables

tblCust
CustID

tblEquipTrans
EquipTransID PK- Autonumber
EqMoveFrom ----- > populated with CustID #
EqMoveTo ----- > populated with CustID #
MoveCode

I need the results to just have three columns – from tblCust, look for match
on CustID in tblEquipTrans, either in the “EqMoveFrom†field, or the
“EqMoveTo†field. I need to show the CustID in the first column that it found
a match to – if it finds the matching number in the “EqMoveFrom†field, or
the “EqMoveTo†field, it doesn’t matter – show the OTHER CustID, and show the
MoveCode for that record. If there is no match, show the custID value, “No
Match†in the second column, and “n/a†or simply null in the third column.

Something like:

CustID……MatchID…..MoveCode
12181………39621…………T
12181……….54896…………C
65488……….NoMatch……(null)
34219……….51322 T

Untested:


SELECT
CustID,
NZ(EqMoveTo,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCode
FROM
tblCustID LEFT JOIN tblEquipTrans
ON tblCustID.CustID = tblEquipTrans.EqMoveFrom
UNION
SELECT
CustID,
NZ(EqMoveFrom,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCode
FROM
tblCustID LEFT JOIN tblEquipTrans
ON tblCustID.CustID = tblEquipTrans.EqMoveTo
;

 

[ironwood9 via AccessMonster.com]

Thanks, but I got a syntax error when I ran the query - is there a way to
attach the db ? It "blew up" on the last line.

I need help re-designing a query to get the desired results that is source by
a query that uses temp/alias fields, from two tables

[quoted text clipped - 23 lines]

65488……….NoMatch……(null)
34219……….51322 T

Untested:

SELECT
CustID,
NZ(EqMoveTo,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCode
FROM
tblCustID LEFT JOIN tblEquipTrans
ON tblCustID.CustID = tblEquipTrans.EqMoveFrom
UNION
SELECT
CustID,
NZ(EqMoveFrom,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCode
FROM
tblCustID LEFT JOIN tblEquipTrans
ON tblCustID.CustID = tblEquipTrans.EqMoveTo
;

 

[smartin]

Thanks, but I got a syntax error when I ran the query - is there a way to
attach the db ? It "blew up" on the last line.

I need help re-designing a query to get the desired results that is source by
a query that uses temp/alias fields, from two tables

[quoted text clipped - 23 lines]

65488……….NoMatch……(null)
34219……….51322 T

Sorry; there were some typos. That's what I get for not testing. Try
this. It does seem to give all possibilities, but that might be more
than you want to see:

SELECT
CustID,
NZ(EqMoveTo,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveFrom
UNION SELECT
CustID,
NZ(EqMoveFrom,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveTo
;

 

[ironwood9 via AccessMonster.com]

Yes, that KIND OF works - and I see what you mean about "more than you want
to see," for example 34219 is in the "baseline" table, and it finds the
"Match," and gives the right code, but then there is another line in my
recordset where it says: 34219 "No match" - well, if something is a no match,
that means literally no match at all.

Thanks, but I got a syntax error when I ran the query - is there a way to
attach the db ? It "blew up" on the last line.

[quoted text clipped - 4 lines]
Sorry; there were some typos. That's what I get for not testing. Try
this. It does seem to give all possibilities, but that might be more
than you want to see:

SELECT
CustID,
NZ(EqMoveTo,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveFrom
UNION SELECT
CustID,
NZ(EqMoveFrom,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveTo
;

 

[smartin]

More thoughts... I'm not sure what you really want to see. You could try
replacing "LEFT JOIN" with "INNER JOIN" throughout to cut out some of
the noise. HTH.

Yes, that KIND OF works - and I see what you mean about "more than you want
to see," for example 34219 is in the "baseline" table, and it finds the
"Match," and gives the right code, but then there is another line in my
recordset where it says: 34219 "No match" - well, if something is a no match,
that means literally no match at all.

Thanks, but I got a syntax error when I ran the query - is there a way to
attach the db ? It "blew up" on the last line.

[quoted text clipped - 4 lines]

65488……….NoMatch……(null)
34219……….51322 T

Sorry; there were some typos. That's what I get for not testing. Try
this. It does seem to give all possibilities, but that might be more
than you want to see:

SELECT
CustID,
NZ(EqMoveTo,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveFrom
UNION SELECT
CustID,
NZ(EqMoveFrom,"NoMatch") AS MatchID,
NZ(MoveCode,"n/a") AS MoveCodeX
FROM
tblCust LEFT JOIN tblEquipTrans
ON tblCust.CustID = tblEquipTrans.EqMoveTo
;