My Preventative Maintenance database has a table with Equipment and a
table with Services.
The primary Key is an auto number field in the Equipment table.
To add services such as oil changes, belt change etc. I enter the
Equipment# as well as other info.
This tabulates a list of services for each piece of equipment on my
form.
I added some checkboxes so the maintenance department can check off
what’s been done.
On check it fills in Service Date Now() as well as takes a specified
amount of time so I can calculate time and time taken.
On exit It first runs an append qry that sends my completed items to a
service history table. It then runs an update to clear the checkbox as
well as taking the Service date adding a frequency to it which now
becomes the Due date. And it shows back on the list due for 7 days
later if the freq is 7.
What I need to do is take any items NOT checked send them to an items
not complete table daily.
Thing is I can only account for items missed once in the freq. In
other words, I have an Item due today, I don’t complete it. I send it
to my “not complete table” unfortunately tomorrow when I still do not
complete the task and I run the append it will again send the item.

So now I have an item that was added to my table 365 times since I
append daily. This item is a weekly check so it should not show more
than 52 times. Does anyone understand this??? Can anyone help??

I am a bit confused.
Which table is getting too many records? I think it is the Not Complete Table.

What is the structure of that table? Can you identify the duplicates in the
table by some field or combination of fields? If so, you could assign a no
duplicates index to the table based on that fields or combination of fields.
Doing so will make it impossible to have duplicates in the Not Complete Table.

Or you can modify your append query. Generic example of such a query.
INSERT INTO NotCompleteTable (<<List of Fields>>)
SELECT <<List of Fields>>
FROM [TheSourceTable]
WHERE SomeMatchingField = ??somevalue??
AND NOT Exists (SELECT * FROM NotCompleteTable WHERE
NotCompleteTable.MatchingField = [TheSourceTable].[SomeMatchingField])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 4/1/2011 7:21 AM, Anthony wrote:

On Apr 1, 1:21*pm, Anthony <anthony.cl... (AT) mahr (DOT) com> wrote:
Hi Anthony,

I build a similar system. In that I do not need a Not-completed-table,
because all the information is already in the Services-table.

Those Services that have an empty Service Date AND Due Date <= Today
still have to be completed.


Imb.

On Apr 1, 9:26*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Correct, the table with the repeats is the "not completed table". Im
doing this so I can get an idea of whats being done weekly as well as
whats not being done. This info will validate the need for more hands.
heres the sql for the append qry:

INSERT INTO [Task Not Completed] ( ID, EQTYPE, DESCRIPTION, INSERVICE,
LOCATION, SERIALNUMBER, MODELNUMBER, MANUFACTURER, WORKCENTER,
ASSETTNUM, STARTUPDATE, WARRANTYDATE, LABORCOST, MATLCOST, EXPR1,
SERVICEDUEDATE, SERVICEDATE, [Time], TIMETAKEN, COMPLETECHECK, EXPR2,
FREQUENCY, Priority, [Service Type], TimeRemaining )
SELECT Equipment.ID, Equipment.EQTYPE, Equipment.DESCRIPTION,
Equipment.INSERVICE, Equipment.LOCATION, Equipment.SERIALNUMBER,
Equipment.MODELNUMBER, Equipment.MANUFACTURER, Equipment.WORKCENTER,
Equipment.ASSETTNUM, Equipment.STARTUPDATE, Equipment.WARRANTYDATE,
Equipment.LABORCOST, Equipment.MATLCOST, [TIME]-[TIMETAKEN] AS EXPR1,
Service.SERVICEDUEDATE, Service.SERVICEDATE, Service.Time,
Service.TIMETAKEN, Service.COMPLETECHECK, [SERVICEDATE]+[FREQUENCY] AS
EXPR2, Service.FREQUENCY, Service.Priority, Service.[Service Type],
Service.TimeRemaining
FROM Equipment INNER JOIN Service ON Equipment.ID = Service.ID
WHERE (((Service.COMPLETECHECK)=False) AND
((Service.Contracted)=False))
ORDER BY Service.SERVICEDUEDATE, Service.Priority;

On Apr 1, 11:51*am, imb <im... (AT) onsmail (DOT) nl> wrote:
Your correct, they do need to be performed but to show management what
is NOT getting done I need to append whats left at the end of the
week. Doing this with repeats but no more than the frequency/yr (if
its a weekly service, every 7 days, then it should not be repeated
more than 52 times. (52 wks/yr)

Hi Anthony,

Again, all the information is already available in Service:

SELECT * FROM Service WHERE ServiceDate IS NULL AND ServiceDueData <=
Date

You can run such a query at the end of the week, or any time. It even
tells you how many days overdue (Date – ServiceDueDate)!
So, in my opinion, no need for a TaskNotCompleted table. Better make a
TaskNotCompleted report, based on the above query.

Imb.

On Apr 1, 11:56*am, Anthony <anthony.cl... (AT) mahr (DOT) com> wrote:
ok so what I did was add an autonumber "service tag" field to my
services table that is indexed with no duplicates. In my qry I
combined [service.service tag]&[Equipment.ID] this becomes a unique
identifier. I can now run the append to not complete and it will not
duplicate where the unique identifiers are alike. Unfortunatly, a week
from now when this item still is not complete. it will not copy it.
Sooooo what I did then is added an additional NOW() field to the
combined fields but since NOW() is a date format including time... it
again copies over duplicates. Is there a now function that represents
a short date?

On Apr 1, 9:51*pm, Anthony <anthony.cl... (AT) mahr (DOT) com> wrote:
Hi Anthony,

That is Date().

Imb.

Anthony <anthony.clang (AT) mahr (DOT) com> wrote in
news:94dc8bb3-3eaa-4bfb-abcd-4a5ee38a6017 (AT) 27g2000yqv (DOT) googlegroups.com
:

tables and append queries, instead of using filtered select queries.

Others (imb, Salad and John Spencer, maybe others) have given the
help you need, but you have failed to understand.

Essentially, your services completed table and your Services Not
complete table violate database design rules.
The data is the same in all three tables, the only difference is the
ServiceCompleteDate field is filled in or is null.
In the case of is null, the difference between upcoming or overdue is
the ServiceDueDate is <= or > date()

Your database will be much more efficient if you redesign it to use
the single table. When your maintenance dept clicks the complete
checkbox, run an append query to copy the data (with your date
changes) to the same table.

When you want to see overdue service you run a select query, when you
want to see upcoming service, run a different select query. To see
completed service, run a different select query.


--
Bob Q.
PA is y I've altered my address.

On Apr 1, 6:03*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
If I only had one table It would be nuts. I have 843 pcs of equipment
and each has a dozen services. Now your talking daily tasks... If I
had one table that just kept repeating that amount of data daily it
would be unsightly.

I have the data in the table once.... when it gets pulled up on the
form. it gets checked off complete then I append it to an append table
(completed) then it runs an update qry that unchecks the box, makes
the due date... the service date plus a frequency but only if the box
is checked. If at the end of the week their are items on the list it
runs an append query to a table called "not complete" Once the data is
in this table it will be used to hand out the beatings.. then it will
be updated with another update qry. This data will only remain until
the hours and other stats are retreived.
Does this make sense?