Solved Access Query - From 2 different tables, without IIF! (1 Viewer)

ironfelix717

Registered User.
Local time
Today, 13:05
Joined
Sep 20, 2019
Messages
193
Hi, I have a reasonably complex query I am hoping to get help with.

Essentially, I need to match an order containing CustomerID and Version to a record that may exist in one of TWO tables, and get the STATUS field of the record.

I have worked around this in the past using an expression field in the query with IIF function, but I am looking for a much cleaner way of doing this and have a hard time imagining there isn't a simpler way.

diagram.png


Notes:**
The related CustomerID and version in orders will always exists in one of the two tables, but never simultaneously
The Customers_CURRENT table will always have the highest 'Version' field.


Thank you for any help!!!
 

Attachments

  • Database1.accdb
    1.9 MB · Views: 101

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Sounds like you want to use a FULL OUTER JOIN.

Sent from phone...
 

ironfelix717

Registered User.
Local time
Today, 13:05
Joined
Sep 20, 2019
Messages
193
Hi!

Sorry forgot to attach my test db!
 

isladogs

MVP / VIP
Local time
Today, 17:05
Joined
Jan 14, 2017
Messages
18,186
Combine your two Customers tables with the Status field used to define whether Current or Old.
Join to your Orders table in the query. Job done!
 

plog

Banishment Pending
Local time
Today, 12:05
Joined
May 11, 2011
Messages
11,611
Why do you have 2 tables with the exact same structure? You should store data in fields, not in table names.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:05
Joined
May 7, 2009
Messages
19,169
see Query2.
 

Attachments

  • Database1 (3).accdb
    2.5 MB · Views: 103

ironfelix717

Registered User.
Local time
Today, 13:05
Joined
Sep 20, 2019
Messages
193
Why do you have 2 tables with the exact same structure? You should store data in fields, not in table names.
😴:sleep::sleep:


@arnelgp
see Query2.
Hi, thanks for your efforts. That query does not produce intended results. See below.

@isladogs
Combine your two Customers tables with the Status field used to define whether Current or Old.
Join to your Orders table in the query. Job done
I still need other data other than "Status" from either of those 2 tables. So rather than just determining the status, I need the full record returned, which i guess means I need the 'ID' field from either OLD or CURRENT table, based on status. Hmmm......


See query 3 in new example:


Screen Shot 2020-11-08 at 7.19.27 PM.png
 

Attachments

  • Database1_2.accdb.zip
    140 KB · Views: 98

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I understand what you're looking for. Is it something like this?

1604883104335.png
 

ironfelix717

Registered User.
Local time
Today, 13:05
Joined
Sep 20, 2019
Messages
193
UPDATE**

I just needed a Union query to join both tables and then process the query on the union query as I would a single table. Why that didn't cross my mind earlier, not sure.

I suspect thats the cleanest way of doing it. Which will be more than one single query (2), but thats the way the cookie crumbles.

Thanks
 

Attachments

  • Database1_3.accdb.zip
    155.1 KB · Views: 92

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,358
UPDATE**

I just needed a Union query to join both tables and then process the query on the union query as I would a single table. Why that didn't cross my mind earlier, not sure.

I suspect thats the cleanest way of doing it. Which will be more than one single query (2), but thats the way the cookie crumbles.

Thanks
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

isladogs

MVP / VIP
Local time
Today, 17:05
Joined
Jan 14, 2017
Messages
18,186
Combining the records from both tables into one single table would have exactly the same outcome as combining the records using a union query. The table would still be fully normalised as it would have the same structure as your existing tables

Doing that would also mean your query is editable whereas a union query is not.
 
Last edited:

Users who are viewing this thread

Top Bottom