Solved Access Query - From 2 different tables, without IIF!

[ironfelix717]

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.

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!!!

 

[theDBguy]

Hi. Sounds like you want to use a FULL OUTER JOIN.

Sent from phone...

 

[ironfelix717]

Hi!

Sorry forgot to attach my test db!

 

[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!

 

[plog]

Why do you have 2 tables with the exact same structure? You should store data in fields, not in table names.

 

[arnelgp]

see Query2.

 

[ironfelix717]

Why do you have 2 tables with the exact same structure? You should store data in fields, not in table names.

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

 

[theDBguy]

Hi. Not sure I understand what you're looking for. Is it something like this?

 

[ironfelix717]

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

 

[theDBguy]

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.

 

[ironfelix717]

Thanks @theDBguy !!

 

[theDBguy]

Thanks @theDBguy !!

You're welcome! We were all happy to assist. Cheers!

 

[isladogs]

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.