Append Query

#11

Quote:

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?- Hide quoted text -

Hi Anthony,

After first reading your answer, it still did not make any sense to
me.
After a second thought I think that I know what the difference is
between your and mine approach.
By the way, I am not impressed by the number of pieces equipment or
the number of tasks.

I use three tables: Equipment, Services, and Tasks.
Services are the things that have to be done on the equipments, in a
general way, including an interval or some kind of frequency, but
without the information when it has to be done.
On the base of the definition of the Services, Tasks are generated.
These Tasks define which Service has to be performed when on what
Equipment. Here you have the DueDate, ExecutionDate, checking
possibilities, etc.

In this table Tasks you have all the information that is necessary
for daily schedules and for reporting on whatever you want.

I hope this helps.

Imb.

#12

Anthony <anthony.clang (AT) mahr (DOT) com> wrote in
news:0c223351-159c-4a12-9aa4-ee0cddef3421 (AT) f15g2000pro (DOT) googlegroups.co
m:

Quote:

On Apr 1, 6:03*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
Anthony <anthony.cl... (AT) mahr (DOT) com> wrote
innews:94dc8bb3-3eaa-4bfb-abcd-4a5
ee38a6017 (AT) 27g2000yqv (DOT) googlegroups.com
:

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 understand. You have misdesigned the database, using separate
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.- Hide quoted text -

- Show quoted text -

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?

No, it does not make sense..You have the same total number of records
in the database. Instead of keeping them where they belong in one
table you move them around, based only on the value contained in a
single field in the tables. That is nuts. Moving records from table
to table just causes bloat.(see
http://www.granite.ab.ca/access/bloatfe.htm)

I've worked with tables that contain millions of records.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Append Query

comp.databases.ms-access comp.databases.ms-access

Re: Append Query - 04-02-2011 , 03:17 AM

Re: Append Query - 04-02-2011 , 05:57 AM