Form development

  • Thread starter Thread starter Aurora
  • Start date Start date
A

Aurora

I am using Access 2000.

I need to develop a database for machine maintenance. I
am planning one Db for the basic machine information with
a primary key being the Company's serial number for each
machine. Then I thought I would develop a 2nd database
for the completion of maintenances by machine serial
number. This Db would contain the date of mtce, what was
done, maybe product used, problem if there was one prior
to the mtce, any testing inform if necessary etc. I also
want to add the due date of the next scheduled maintenance
so that a report could be run weekly/monthly to see what
maintenance is required that month. Based on the machine,
maintenance can be due every month, quarterly, semi-annual
etc.

My thought was to created a form with the basic
information at the top and include a subform for entering
the maintenance completed. What I want to do is to add a
field to the basic mach Db that tell me when the next
maintenance is scheduled based on the most current
maintenance completed. So that by just looking at the
basic machine information - we can tell when the next
maintenance is scheduled. How can I do this???

I am open to any ideas as to how to develop such a
database.

Aurora
 
By Db, I hope you mean Table.... Put it all in one Db, and design the
tables first...
 
Why would I want to put all of the information into one
Table (Yes Db to me means the table). I thought this was
the idea of one to many relationships - not to bog down
one table. What am I missing here???

Aurora
 
Aurora said:
Why would I want to put all of the information into one
Table (Yes Db to me means the table). I thought this was
the idea of one to many relationships - not to bog down
one table. What am I missing here???
Click to expand...

Your not missing anything. Your basic idea for two tables is the correct
way to do this.

However; I would not recommend a field in the base table to store what is
essentially a calculation. You should be able to use an expression in a
query or on your form to derive the next date without having to store it.

One way would be to build two queries. The first would simply pull in the
Machine ID and the Max(MaintenanceDate) from your child table. You would
use a Totals query and Group By Machine ID. Then you could feed that into
a second query or report that takes the max date and adds whatever the
interval should be to calculate the next schedule date. The report/query
could then easily be filtered to show records where the calculated date is
in the current month or past due. Actually you might be able to do that
with a single query.

On the form an expression in a TexBox would look something like...

=DateAdd("M", 6, DMax("[DateField]", "ChildTable", "MachineID = " &
Me!MachineID & ""))

The above assumes that maintenance dates are 6 months apart.
 
Misunderstanding on top of misunderstanding.... I stated tables(plural).
From your original post, I thought you were going to create two separate
databases.... Sorry about that.
Damon
 
Back
Top