Pulling two queries back into one (1 Viewer)

machinetech

New member
Local time
Today, 02:03
Joined
Jun 10, 2019
Messages
7
I have a parent table that contains a list of all of the machines my company owns (Table 1). There is another table that contains all of our maintainence records(Table 2). From the maintainence records table I then create a query (Query 1) that predicts the next maintainence date based off of the most recent entry for that specific machine.
However there are some machines that have never been maintainanced, so I have identified these machines by using an unmatched query between Table 1 and Query 1. From the unmatched Query I created a Query that calculates when these machines should receive there first service (Query 2).

Now essentially what I want is a query that contains all of the machines with their expected maintainance dates. I have tried several different iif statements without any luck.

I'm thinking maybe if I could make a query of Table 1, and say that if the machine exists in Query 1 then the field will equal true, and if not false. Then I would be able to make an iif, if the field is true, pull date from query 1, if false query 2.

Sorry for being long winded, but I figured it would be best to give the whole scenario rather than just the 2 queries I have. I can post sample data in a reply if needed. Thanks for any advice.
 

jdraw

Super Moderator
Staff member
Local time
Today, 03:03
Joined
Jan 23, 2006
Messages
15,364
You may get some ideas from this free Equipment Maintenance data model from Barry Williams' site. Note he also includes Personnel and Skills required, but his is a generic model showing the typical set up. You can add, remove, disregard parts that do not apply to you. His model is meant as a starting point that you can customize to your needs if/when required.
Good luck with your project.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:03
Joined
Oct 29, 2018
Messages
21,358
Hi. It sounds like what you want is possible, but you could also just keep your two queries and just UNION them to get all the records into one query.
 

plog

Banishment Pending
Local time
Today, 02:03
Joined
May 11, 2011
Messages
11,613
I bet you can do this with just 2 queries, but for simplicity sake, I suggest 3:

Q1--this exactly the Query 1 you have now.

Q2--this would be based on Table1 and predict [FirstMaintenanceDate] for every machine.

Q3--this would be a LEFT JOIN query on Q2 into Q1. That means you would show all records from Q2 and just matching results in Q1. You would bring in the machine data from Q2 and then use an IFF statement for [Next Maintenance Date]. If there was a value in Q1 you use that, if null, use the [FirstMaintenanceDate] from Q2
 

machinetech

New member
Local time
Today, 02:03
Joined
Jun 10, 2019
Messages
7
The Union Query is exactly what I needed. Thank you.
I was able I followed the instructions from here and got exactly what I needed.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:03
Joined
Oct 29, 2018
Messages
21,358
The Union Query is exactly what I needed. Thank you.
I was able I followed the instructions from here and got exactly what I needed.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom