Hi All.
I have a database for equipment, the majority of which, requires a yearly service. When equipment is being serviced, a job is created and service details are completed for the equipment.
I want to be able to automatically create a job (record) when the equipment is within 28 days of its service due date.
To hopefully do this, I have created a query that selects all equipment that is within 28 days of a service. This is called QueryPPMOutstandingFinder.
I have then created a query to find all jobs (records) from the Assignments table to find the equipment that already has a job created for it. The reason some already have a job created is because I previously had a button that worked perfectly until I updated the database to Access 365.
Now I have a list of everything that is either already due a service, or is due a service within the next month. I also have a list of jobs that have already been created for equipment due a service.
What I now need to do is compare both of the queries, and remove any equipment that is due a service, that already has a job created for it.
So far I have created a new query called QueryPPMCreateJobs, this selects the equipment's unique code number from both queries. I have joined [code no] in both queries with the below join:
This gives me the results below. Any row where the code number occurs twice, means that this device already has a job created for its next service. Any row where the left column is blank, this is a piece of equipment that does not have a job already.
Is there a way to remove all of the rows where the [Code No] exists in both of them?
Thanks
I have a database for equipment, the majority of which, requires a yearly service. When equipment is being serviced, a job is created and service details are completed for the equipment.
I want to be able to automatically create a job (record) when the equipment is within 28 days of its service due date.
To hopefully do this, I have created a query that selects all equipment that is within 28 days of a service. This is called QueryPPMOutstandingFinder.
I have then created a query to find all jobs (records) from the Assignments table to find the equipment that already has a job created for it. The reason some already have a job created is because I previously had a button that worked perfectly until I updated the database to Access 365.
Now I have a list of everything that is either already due a service, or is due a service within the next month. I also have a list of jobs that have already been created for equipment due a service.
What I now need to do is compare both of the queries, and remove any equipment that is due a service, that already has a job created for it.
So far I have created a new query called QueryPPMCreateJobs, this selects the equipment's unique code number from both queries. I have joined [code no] in both queries with the below join:
This gives me the results below. Any row where the code number occurs twice, means that this device already has a job created for its next service. Any row where the left column is blank, this is a piece of equipment that does not have a job already.
Is there a way to remove all of the rows where the [Code No] exists in both of them?
Thanks