Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2019, 04:14 AM   #1
machinetech
Newly Registered User
 
Join Date: Jun 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
machinetech is on a distinguished road
Pulling two queries back into one

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.

machinetech is offline   Reply With Quote
Old 06-13-2019, 04:28 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,888
Thanks: 73
Thanked 1,961 Times in 1,909 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Pulling two queries back into one

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-13-2019, 04:31 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,555
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Pulling two queries back into one

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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-13-2019, 05:43 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,251
Thanks: 10
Thanked 2,236 Times in 2,189 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Pulling two queries back into one

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
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
machinetech (06-13-2019)
Old 06-13-2019, 06:39 AM   #5
machinetech
Newly Registered User
 
Join Date: Jun 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
machinetech is on a distinguished road
Re: Pulling two queries back into one

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.
machinetech is offline   Reply With Quote
Old 06-13-2019, 06:59 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,555
Thanks: 29
Thanked 631 Times in 614 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Pulling two queries back into one

Quote:
Originally Posted by machinetech View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] refresh the front enq queries from the back end? Locopete99 Modules & VBA 7 06-19-2017 07:16 AM
pulling queries off of same queries kplatt Queries 10 09-16-2009 12:23 AM
pulling specific data from a queries fndmewrk General 1 04-21-2008 02:06 PM
Linking Back-end RWOP Queries M_S_Jones General 1 02-14-2008 01:17 AM
Queries in the back-ends??? Newman General 6 03-07-2005 07:30 AM




All times are GMT -8. The time now is 02:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World