Should be simple query but I'm stumped

[hvacfixer]

OK, my first post. I have three tables. (tblLocations, tblEquipment, and tblCalls). The tblEquipment table has 4 fields. (EqID, LastPMDate, PMSchedule and LocID). The tblLocations table has 2 fields. (LocID and Name). The tblCalls table has 5 fields. (CallID, DateGenerated, DateCompleted, CallType, and LocID). All tables are related by LocID.



What I am trying to accomplish is to Generate Calls based on the PM Schedule and the Last PM Date. The CallType field in the tblCalls table is used to record whether the calls are PM or Trouble. That is the only allowed values for that field. The PMSchedule field in the tblEquipment table is used to record either Quarterly or Semi-Annually. The DateGenerated and DateCompleted fields in the tblCalls table is used to record the dates that the call was generated and completed respectively.

I have been able to get the locations calls should be generated for correctly with the following query:

SELECT tblLocations.Name, tblLocations.LocID
FROM (tblLocations INNER JOIN tblCalls ON tblLocations.LocID = tblCalls.LocID) INNER JOIN tblEquipment ON tblLocations.LocID = tblEquipment.LocID
WHERE (((tblEquipment.LastPMDate)<Date()-91) AND ((tblEquipment.PMSchedule)="Quarterly")) OR (((tblEquipment.LastPMDate)<Date()-182) AND ((tblEquipment.PMSchedule)="Semi-Annually"))
GROUP BY tblLocations.Name, tblLocations.LocID
ORDER BY tblLocations.LocID;

The problem arises when all generated PM Calls have not been completed and I need to generate a new set of PM Calls. It will give me a list of locations that are duplicates of the already generated but not yet completed calls. ( The LastPMDate field in the tblEquipment is updated with the DateCompleted field value in the tblCalls table). So I need to modify my query to exclude those locations that have a PM call already generated but not yet completed.

Any help would be greatly appreciated. I am currently working around this by running my query above and having it make a temporary table, then I run another query to delete the locations where PM calls have been generated but not completed. But, I know there has to be a way to alter this query to give me the locations I need.

Sorry for the long post, I hope it is clear.

[hvacfixer]

Hi all,

This has had 33 views and no responses. Is there something I am missing here? Do I need to provide some other information? Somebody say something, please.

[HiTechCoach]

The problem arises when all generated PM Calls have not been completed and I need to generate a new set of PM Calls. It will give me a list of locations that are duplicates of the already generated but not yet completed calls. ...

Any help would be greatly appreciated. ...

I would not be incluidng ones in the new set on PM call when the last one has not been comeplete. I would filter these out using a sub query.

[HiTechCoach]

Hi all,

This has had 33 views and no responses. Is there something I am missing here? Do I need to provide some other information? Somebody say something, please.

This is not a trivial task. I have built several PM/Maintenance databases.

You might get more response if you were to post a sample of your database with some sample data.

[ajetrumpet]

This has had 33 views and no responses.

hvac,

You should know that posting a question on a forum doesn't guarantee yourself a response. Nobody is getting paid to answer questions here. Just an FYI for you OK?

Somebody say something, please.

See above. The other you may not be aware of is that the longer the post is, the less of chance you have to get any kind of response at all. Hopefully that makes sense for you. Good luck!

[HiTechCoach]

OK, my first post. I have three tables. (tblLocations, tblEquipment, and tblCalls). The tblEquipment table has 4 fields. (EqID, LastPMDate, PMSchedule and LocID). The tblLocations table has 2 fields. (LocID and Name). The tblCalls table has 5 fields. (CallID, DateGenerated, DateCompleted, CallType, and LocID). All tables are related by LocID.

What I am trying to accomplish is to Generate Calls based on the PM Schedule and the Last PM Date. The CallType field in the tblCalls table is used to record whether the calls are PM or Trouble. That is the only allowed values for that field. The PMSchedule field in the tblEquipment table is used to record either Quarterly or Semi-Annually. The DateGenerated and DateCompleted fields in the tblCalls table is used to record the dates that the call was generated and completed respectively.

I have been able to get the locations calls should be generated for correctly with the following query:

SELECT tblLocations.Name, tblLocations.LocID
FROM (tblLocations INNER JOIN tblCalls ON tblLocations.LocID = tblCalls.LocID) INNER JOIN tblEquipment ON tblLocations.LocID = tblEquipment.LocID
WHERE (((tblEquipment.LastPMDate)<Date()-91) AND ((tblEquipment.PMSchedule)="Quarterly")) OR (((tblEquipment.LastPMDate)<Date()-182) AND ((tblEquipment.PMSchedule)="Semi-Annually"))
GROUP BY tblLocations.Name, tblLocations.LocID
ORDER BY tblLocations.LocID;

The problem arises when all generated PM Calls have not been completed and I need to generate a new set of PM Calls. It will give me a list of locations that are duplicates of the already generated but not yet completed calls. ( The LastPMDate field in the tblEquipment is updated with the DateCompleted field value in the tblCalls table). So I need to modify my query to exclude those locations that have a PM call already generated but not yet completed.

Any help would be greatly appreciated. I am currently working around this by running my query above and having it make a temporary table, then I run another query to delete the locations where PM calls have been generated but not completed. But, I know there has to be a way to alter this query to give me the locations I need.

Sorry for the long post, I hope it is clear.

I am a llittle confused by the design. Why is there a field [tblEquipment.LastPMDate]? Nopramlly this woudl come frpomt he "calls" table.

I think the way you have your relations is this:

Locations > equipment
Locations > Calls

Is this correct?

I normally handle it like this:

Locartions > equipment > (service) calls

[hvacfixer]

HiTechCoach,

I may have designed the relationships wrong. What I have is each location may have one or more pieces of equipment. Each piece of equipment has its own PM Schedule. So it is possible to have different PM Schedules at the one location. So, I put the PM Schedule in the Equipment table and the last time PM was performed in the same table. I have uploaded a sample of the database. Maybe it will help.

I think your idea of a sub-query is what I am looking for. I am having trouble making it work though.

Any suggestions about redesigning the relationships are welcomed. I have been fooling around with Access for a long time, but I have never did any work for anyone else. It is mostly just a hobby for me and I enjoy it.


Thank you for your responses.

[HiTechCoach]

The key to success of any database is in the properly design of the table structures. This theory is call Normalization.

See the links here: Previous Post

As you can see there is a lot written on the subject of data normalization. I believe it is because this is so important. This is not limited to just Access. This theory also applies to any relational database.

Here is another link (Click Here) that is very good.

I think you are starting to run into issue based on your current design.

I believe the first thing you need to do is study up on data normalization. Then take a very close look at your table structures and relationships. Do not worry about how to design the forms at this point. Once you have the tables and relationships design properly everything else will get easier.

I have not taken a look at your database yet. I do have it downloaded. I will take a look at it shortly.

[hvacfixer]

The key to success of any database is in the properly design of the table structures. This theory is call Normalization.

See the links here: Previous Post

As you can see there is a lot written on the subject of data normalization. I believe it is because this is so important. This is not limited to just Access. This theory also applies to any relational database.

Here is another link (Click Here) that is very good.

I think you are starting to run into issue based on your current design.

I believe the first thing you need to do is study up on data normalization. Then take a very close look at your table structures and relationships. Do not worry about how to design the forms at this point. Once you have the tables and relationships design properly everything else will get easier.

I have not taken a look at your database yet. I do have it downloaded. I will take a look at it shortly.

Thank you. I am familiar with normalization. I will review it with your links. I read C.J. Date's book when I first started fooling around with Access.

I guess I am kind of weird. This is just a hobby, mostly because I don't have any formal training, but I first started fooling around with databases when I got my first computer with dBaseIII back in the DOS days.

I have not ever had any employment doing database design, but I have designed many for my own use. I have fantasized about doing some database design work. I just have never attempted to do so for other people.

Thanks again for taking the time to look at this for me.

[hvacfixer]

HiTechCoach,

I think I have solved my problem. I created another query that looks like this:

SELECT tblCalls.LocID
FROM tblCalls
WHERE (((tblCalls.DateGenerated) Is Not Null) AND ((tblCalls.DateCompleted) Is Null) AND ((tblCalls.CallType)="PM"))
ORDER BY tblCalls.LocID;

I saved it as qryLocationsGenerated. I then added it to my original query and ended up with this:

SELECT tblLocations.Name, tblLocations.LocID
FROM qryLocationsGenerated RIGHT JOIN (tblLocations INNER JOIN tblEquipment ON tblLocations.LocID = tblEquipment.LocID) ON qryLocationsGenerated.LocID = tblLocations.LocID
WHERE (((tblEquipment.LastPMDate)<Date()-91) AND ((tblEquipment.PMSchedule)="Quarterly") AND ((qryLocationsGenerated.LocID) Is Null)) OR (((tblEquipment.LastPMDate)<Date()-182) AND ((tblEquipment.PMSchedule)="Semi-Annually") AND ((qryLocationsGenerated.LocID) Is Null))
GROUP BY tblLocations.Name, tblLocations.LocID
ORDER BY tblLocations.LocID;

I think this is probably what you were talking about using a sub-query. I didn't get it working right at first. I think I had the Join between the sub-query and Locations table wrong. I was using an inner join instead of a right join.

Thank you for looking at this for me. I am going to mark this as solved for now.


hvacfixer