Please help me set up my tables for new database

[jzwp11]

OK, then I think a tweak to the table structure is in order. Since an asset type can have many PM schedules and the same PM schedule names (PM1, PM2...) can apply to many asset types we have a many-to-many relationship. Additionally, since each combination of PM schedule name and asset type have many PM Tasks and a PM task can apply to many PM scheduleNames/asset type combinations we have a second many-to-many relationship


tblPMScheduleNames
-pkPMSchedule
-PMNumber

tbAssetTypePMScheduleNames
-pkAssetTPMSchNameID primary key, autonumber
-fkAssetTypeID foreign key to tblAssetTypes
-fkPMSchedule foreign key to tblPMScheduleNames

tblPMScheduleTasks
-pkPMSchTaskID primary key, autonumber
-fkAssetTPMSchNameID foreign key to tblAssetTypePMScheduleNames


-fkPMTaskID foreign key to table that holds the various PM tasks
-interval (the mileage or hours of use at which the task must be conducted for the given asset type/scheduleName)


You would then join pkPMSchTaskID in the PMEvent tasks table (as a foreign key) when an actual PM task is conducted.

[10 Gauge]

Hrmmm, ok I tried setting this up but I am not seeing any actual many-to-many relationships happening, just a bunch of one-to-many relationships.

[jzwp11]

Both tables: tbAssetTypePMScheduleNames & tblPMScheduleTasks are junction tables which are what is used to structure many-to-many relationships.

BTW, you no longer need the AssetPMTask table that you showed in your attachment.

[10 Gauge]

How long did it take you to master all of this? I feel like I'm lost in a 10 square mile corn maze right now..... The sad part is I believe myself to be technically savvy when it comes to this stuff but I'm struggling right now.

[10 Gauge]

Hrmmm I think we have this linked to the wrong table.... Currently we have these PM's being linked to the AssetType table. But the PM's aren't differentiated by Truck/Heavy Equipment. They are actually differentiated by the Make & Model.

OR.... I guess instead of just having two equipment types (truck and heavy equipment) I could be more specific (pickup truck, end loader, backhoe, roller, etc). ? Would that work too I suppose?

[jzwp11]

I guess instead of just having two equipment types (truck and heavy equipment) I could be more specific (pickup truck, end loader, backhoe, roller, etc). ? Would that work too I suppose?

I would think the above would be better. Do you differentiate between various types of pickup trucks 1/4 ton, 1/2 ton etc. with respect to PM task or are all pickup trucks handled the same way?


You could have tblAssetTypes contain records as described above and then add another field to categorize the type as either truck or heavy equipment

tblAssetTypes
-pkAssetTypeID primary key, autonumber
-txtAssetTypeName
-fkAssetCatID foreign key to tblAssetCategories

tblAssetCategories (holds truck, heavy equipment etc -this would be more general classification names)
-pkAssetCatID primary key, autonumber
-txtAssetCategoryName

[10 Gauge]

Yes I think what you outlined above would be the best way to do this rather than trying to link it to the make/model though that would be the most specific way to do it.

As far as P/U truck PM service goes, we actually don't even have a PM schedule for them as far as I am aware like we do for the heavy equipment. The PM schedule we currently have ONLY encompasses heavy equipment.

[jzwp11]

Thinking more long term, makes and models change whereas your basic asset type probably wouldn't. If you relate the asset type to the asset, you make the connection to make and model as well (just less directly).

[10 Gauge]

Ok I've got it setup and made a form that allows me to enter in the different PM Tasks per PM # for each different type of equipment and entered in some PM stuff for diesel pickup trucks to test this out.

Now the challenge I am facing is how to display this information on my equipment details form. Obviously I need a subform for the information but I am confused as to how it will automatically know what equipment type to look for based on the equipment type being displayed on the master form.

[jzwp11]

This is where you will have to filter the subform's recordset based on what is in the main form. In other words a query that references a control on the main form. This site has some guidelines for referencing controls, properties etc. on forms and subforms. Before I try to offer any advice, I need to see your modified structure and I will need some specifics as to what you are trying to display in your equipment details form.

[10 Gauge]

Hrmmm so something like..... Me.[Asset Details]![AssetTypeID]

Would that reference the control I am looking for?

On the equipment details form, under the "PM Tasks" tab, I want to display the PM Tasks that are related to the equipment type of the equipment being displayed on the form.

However I would like to be able to display different "columns" if you will or different boxes for each PM level 1-5 (for pickup trucks, 1-4 for equipment). This is to avoid just having a jumbled mess of information that has to be sorted out to see what services go with what pm levels.

[jzwp11]

The me. shortcut can be used in VBA code but you must use the full form reference in a query Forms!formname!controlname

However I would like to be able to display different "columns" if you will or different boxes for each PM level 1-5 (for pickup trucks, 1-4 for equipment). This is to avoid just having a jumbled mess of information that has to be sorted out to see what services go with what pm levels.

This sounds like a job for a cross tab query and form. I have not used those much, so you may want to start a separate thread on that. The other option is to have a form in form view that shows the PM# with a subform showing the PM items and embed that entire form/subform into you asset detail form. That might get a little messy. Another option is to have a button on your asset detail for that opens the PM form when you need it.

[10 Gauge]

Sweet ok I got it to work. I am going to check out the cross tab query and see what that's all about. I just did something though trying to create a combobox and it seems to have borked the entire form.... *facepalm*

[10 Gauge]

Here's where we are for the day... Not sure if you can take a look at the Asset Details form and see what I did to screw it up. It's not loading any data any more and throwing up an error about AssetKeyType's.

Thx!!!

[jzwp11]

I had to clean up some things. I tried to have the PM schedule form as a subform, but for some reason when I changed the asset type in the main form it would not update the subform even with some code. So, I opted to have a button to open the form when needed. The modified DB is attached.