How to make a link from one table to mulitple tables?

[grog]

Hi

I am designing a database to track jobs (i.e. lists of tasks) for a
property management firm. Part of the design is straightforward.
The first set of tables are: Clients, Properties, Buildings and Units
These are all related in the following way 1 Client to many Properties;
1 Property to many Buildings, and 1 Building to many Units.
Other tables in the database are Jobs and Tasks. 1 Job can have many
Tasks.

The difficulty I am having with the design is that a given Job needs to
be assoicated with any one of the first set of tables. Clients,
Properties, Buildings or Units.

Does anyone know how to set up the Jobs table or form so that a given
job can be associated with any one of these 4 tables?

thanks

Clive Grog

 

[Linc]

Hey Grog,

I think what you should do is make lookups to your Clients, Properties,
Buildings, Units tables in your Jobs table. Lookups create 1 to 1
relationships that lookup the information in one table (say Clients) and
stores it the current table (say Jobs).

For example,

In the Jobs table, create a field called Client. When assigning a DataType
pick Lookup Wizard and follow the instructions.

Hope this helps

 

[KARL DEWEY]

Think you are making it harder than it needs to be. I can not think of a
Task to be performed on a Client - Say "Hello."

You also may not need Properties, Buildings and Units but just
Equipment/Item that has the task performed on it. A Task would be 'cut the
grass' and 'trim hedges.' All you need is to identify where. Have fields
for Properties, Buildings and Unit in the Equipment/Item table.

Maybe have additional fields to further define the item location.

You need to layout the work requirements before you build the database.

For re-occuring like preventive maintenance then try this --
Create a Periodic Maintenance (PM) table that list all the PM services you
will perform. In this table use the lowest common denominator of your
intervals. Most folks do not call a daily a PM but have weekly as the lowest
interval. If weekly is your lowest then compute all services in multiples of
weeks - a 2-month would be 9 weeks, 6 months would be 26 weeks.

Decide when the next PM will be scheduled based on last schedule or last
completed.

Build a junction table of all equipment, services (PMs), Last Schedule, Last
PM Complete, and flag for last schedule or last completed.

Run update query each time a PM work order is closed - flagged closed.

Create a Work Order table that has Equipment, PM, Schedule Date, Completed
Date, Maintainer, Comments (to be able to record exceptions and problems),
Closed, and other fields as needed.

Generate PM work orders each week for the next however many weeks you plan
in advance. Use an append query to generate the PM work orders.