Asset DB setupdesign question - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Asset DB setupdesign question

Tom DeJonge

2 years ago

We have many different types of assets. Some get PMs every month, every 2 months, 3 months, 6 months or once a year. We also have these start at different months. For tracking I need to know what PMs are due and what ones are late. I am haveing a hard time setting up the table to place data and to track based due and late. Any guidence would be helpful, I am no sure how much info I should place in this post. Examples below. Thank you

Asset is its own table
PM data its own table linked to asset

PMs:
Gens - 2x a year - April & Oct
DC - 2x a year - March & Sept
UPS - 1x a year - Feb
HVAC - 4x a year - Feb, May, Aug, Nov
Load Test - Monthly

Do I just place each month in th table or do I place stat month and calculate other months? Or some other path.

Juan C Rivera @Reply

2 years ago

your gonna need the DateAdd function set up the table with intervals and time (ie WW time 2 for every two weeks) You will need to be creative setting up the tables and queries. but very easy when you get into the nuts n bolts. have the last inspection date performed date add into annon bound field to show next duedate. last item is to update last due date to show the next inspection due. if you get into a jam let me know we can work tis out one step at a time.

V/r
Juan

Sami Shamma @Reply

2 years ago

Hi Tom

I would approach this as follows:

FrequencyT:
   FrequencyID AutoNumber
   Frequency (Number) This is always in numbers of months. So annually will be 12, Monthly will be 1 etc.
AssetT
   AssetID AutoNumber
   AssetName ShortText, etc
   FrequencyID
PmT
   PmID AutoNumber
   AssetID Number
   PmDate Date/Time
   PmCpmpleted Yes/NO

This will give you the basic structure to do your queries and calaculationd.
Note, with this every time you perform/Schedule a PM, a new PmT record will be added.

Good luck

	Sami Shamma @Reply 2 years ago
This will give you history of each Asset's PM

	Tom DeJonge OP @Reply 2 years ago
Thank You Gentlemen for the guidance.

	Sami Shamma @Reply 2 years ago
You are welcome, If you need further help post here.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View