Recurring Event Dates

[shirin]

I am developing a database about preventive maintenance and I need to know all preventive operation that must have been done on an asset until now. I have start dare (date installed), the intervals, and the finish date (now). But I can not get all dates that I need. I try dateadd function but I only get the first operation that has been done on the asset. your kind guidance is requested in this regard.thanks

[June7]

Post your attempted query. Provide sample data.

[shirin]

ID	Type equipment	Date install
1	COADIS 3	11/5/2013
2	COADIS 3	11/5/2013
3	MELODY 2	10/6/2013
4	MELODY 2	10/6/2013
5	MELODY 2	10/6/2013
6	MELODY 2	10/6/2013
7	MELODY 2	10/6/2013
8	MELODY 2	9/6/2013
9	MELODY 2	9/6/2013
10	MELODY 2	9/6/2013
11	MELODY 2	9/6/2013
12	MELODY 3	8/7/2013

ID	Equipment	Equipment Id	Date (in months)
p-01	MELODY 3	6	8
p-02	MELODY 3	6	9
p-03	MELODY 3	6	12
p-04	MELODY 3	6	24
p-05	MELODY 3	6	36
p-06	MELODY 3	6	48
p-07	MELODY 3	6	60
p-08	MELODY 3	6	72
p-09	MELODY 3	6	84
p-10	MELODY 3	6	96
p-100	New Space 320	11	90
p-101	New Space 320	11	127

the first table is the preventive plan the second is the customer equipment
and this is what I have done

SELECT [Customer Equipment].ID, [Customer Equipment].Site, [Customer Equipment].[Type equipment], DateAdd("m",[Preventive plan]![Date (in months)],[Customer Equipment]![Date install]) AS doneplans
FROM ([Equipment Model] INNER JOIN [Customer Equipment] ON [Equipment Model].[EQ ID] = [Customer Equipment].[Equipment model id]) INNER JOIN [Preventive plan] ON [Equipment Model].[EQ ID] = [Preventive plan].[Equipment Id];

[June7]

The query references 3 tables but you provide only 2 tables data.

If you want to provide db for analysis, follow instructions at bottom of my post.

[shirin]

Files (3).zip
Here is the database

[June7]

Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.
Might want to correction spelling of table names Constractor to Contractor and Technition to Technician, also a field named Cunstructor to Contractor.
What is 'recetion'? This word is in some of the Operation descriptions.

Should not save equipment model/description in CustomerEquipment and PreventivePlan tables, just the equipment ID.

Not yet clear to me what you need. You say you 'only get the first operation' but neither of the Operation tables are included in the query.

[shirin]

Thanks for your advice. I will correct them.
But about what I need. As you see, all the installation dates are in 2013/14 and there are some preventive plans that must be done on each equipment like every 6/7 months (more or less). I need to know the dates of which those preventive plans operation must have been done in order to compare them with operations that have been done in 2016 ( all operations in table operation are done in 2016) and know whether or not they have been done on time. And plus that, to use the date of the last operation that should have been done on the equipment to schedule next operations that should be done on each of the customer equipment. I know how to do these two, but I am kind of stock in the level of getting the dates.

[orange]

I agree with June's comments. I think you should step back and make a list of your requirements and identify the business facts. This will help your with table design and appropriate relationships.
For example -Can a Customer have more than 1 Contract?
You should also be aware of reserved words that Access uses.

It would seem that an Asset would have a some specification regarding preventative maintenance schedule.( exam[le --Owners manual would have this)
So, if you know when an Asset was purchased/put into service, you should be able to identify the estimated PM events. But whether or not a PM event actually was performed is a separate question. How do you intend to get/calculate "actual PM service event dates"?

[shirin]

I do not have any data about the PM has done before 2015 so I cannot determine the actual time of PM for that but what I have are the 2016 events which can be either PM or Emergency.so I need to determine that the scheduled PM is done on time on a customer asset, for knowing this I need to know, for example, which PM must have been done on an asset in 2016. And we suppose to think that all PM in 2017 is done on time.
And plus that each customer can only have one contract, which it’s kind is determined by the cost of each operation that has been done on their asset during their contract time and their contract is renewed yearly.

[June7]

Don't think I will be able to help you. Data is just too confusing. Examples:

Operation table has one record for EqID 6 at location Loc-001, however, this location and equipment is not in CustomerEquipment table.

Operation table has one record for EqID 5 at location Loc-001, however, there are 3 records for this location and equipment in CustomerEquipment table. Should the Operation record be associated with all 3 in CustomerEquipment? Assume the same operation performed on all 3 equipment on the same date?

Why are there EqID numbers in Operation that are not in EquipmentModel?