Remove duplicate columns (1 Viewer)

kevnaff

Member
Local time
Today, 08:39
Joined
Mar 25, 2021
Messages
141
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:

1643727020832.png


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.


1643726969463.png


Is there a way to remove all of the rows where the [Code No] exists in both of them?

Thanks
 

Ranman256

Well-known member
Local time
Today, 04:39
Joined
Apr 9, 2015
Messages
4,337
in the left side, set where to: IS NULL
and youll only get those.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:39
Joined
Feb 19, 2002
Messages
42,970
I would not create the scheduled maintenance records ahead of time. How are these records getting created now and why are there duplicates?

The query should run every day for a month out and include late jobs as well as future jobs. The criteria is based on the service period defined for the part and keyed off the last service record found. If no service record is found, you would need to decide what to do. I think the simplest solution is to add a service record when a part is put into service and use the inservice date as the serviced date so you have a starting point.

But we need to start with why are there duplicates now?
 

kevnaff

Member
Local time
Today, 08:39
Joined
Mar 25, 2021
Messages
141
I would not create the scheduled maintenance records ahead of time. How are these records getting created now and why are there duplicates?

The query should run every day for a month out and include late jobs as well as future jobs. The criteria is based on the service period defined for the part and keyed off the last service record found. If no service record is found, you would need to decide what to do. I think the simplest solution is to add a service record when a part is put into service and use the inservice date as the serviced date so you have a starting point.

But we need to start with why are there duplicates now?

Hi Pat,

I'm not too sure why the records get created ahead of time, this was in place before I took over the database. The main reason I can think of is that the automatically generated job autofills a lot of information that the user would normally have to fill in, which speeds up the process.

The way they are now created is that one query finds all equipment that is due a service, or will be in the next 28 days. A second query then finds all equipment that already has a job created. The 3rd query, which is above, compares the equipment's [code no] and then leaves me with only the equipment that is due a service, that does not already have a job allocated to it. I then have a command button that uses the [code no] in the 3rd query to create a job for each [code no].

I don't think I have any duplicates, unless I've missed something or not explained it very well.

Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:39
Joined
Feb 19, 2002
Messages
42,970
I think @Ranman256 told you to use a left join for the third query. If you don't understand what he was talking about, you can use the wizard to create a "find unmatched" query which will create the left join for you.

The "find unmatched" query returns rows where there is an entry in tblA but no match in tblB which seems to be what you want.
 

Users who are viewing this thread

Top Bottom