Please help me set up my tables for new database

[10 Gauge]

Ok awesome, the tabbed subforms are working a treat.

I went ahead and simplified the SOS tracking system. It was just far too complex for what was being done (in my very humble opinion). I deleted all of the related SOS tables and just stuck with the main SOSEvent table with fields for the events taking place and at what usage the SOS occurred.



I am getting to the PM part now on the details form and I am having a hard time figure out how I am going to implement this but before I ask any questions I am going to sit and let this run through my brain a bit and see if I can't figure out what I want....

[10 Gauge]

Ok I've run in to a problem with the PM Tasks.

I have entered in a full list of PM Tasks. I then have a ComboBox setup in my subform for AssetPMTasks to display the list of PM Tasks available. That all works just fine. The problem is when I choose one of the items on the list, instead of using that particular PMTaskID, it makes a new ID and copies the PM Task name. How can I have it use the actual PMTaskName that I chose instead of appending that name to a new ID and using it?

[jzwp11]

It should not be doing that, something is not set up properly either in the combo box or subform. Check the record source for the subform and make sure it is only the table. Likewise, check the row source of the combo box to make sure it is only the table that has the list of possible task. Also make sure that you are binding the combo box to the correct field in the subform's underlying table. It should be bound to the foreign key that represents the task not the primary key of the table that underlies the subform.

[10 Gauge]

I can't get it to work I don't know what's wrong.

[jzwp11]

Can you post the DB? I will take a look at it tonight.

[10 Gauge]

Yeah I'm going to keep working on it and see if I can't figure it out. I'll post the most up to date copy I have before I leave the office tonight.

Thanks!

[10 Gauge]

Now it says "Field Cannot Be Updated" when I try and enter in a new record for PM Tasks... grrrrrrrrrrrr

[10 Gauge]

FYI, I am having to simplify the whole database as I go, there's too many things I don't understand so it's going to be a little different when you look at it.

[10 Gauge]

Alright this is what I have so far.... Again when I add items to the PM Tasks tab it wants to assign each task a new PMTaskID instead of using what's already there.

And the PM tracker was simplified which will make it a bit more manual labor intensive I would assume but I just don't understand it all.

Thanks again for looking at this.

[jzwp11]

The problem with the AssetPMTask form was that you based it on the following query rather than the table itself

SELECT AssetPMTask.[PM1-250], AssetPMTask.[PM2-500], AssetPMTask.[PM1-750], AssetPMTask.[PM3-1000], AssetPMTask.[PM1-1250], AssetPMTask.[PM2-1500], AssetPMTask.[PM1-1750], AssetPMTask.[PM4-2000], PMTasks.PMTaskName, AssetPMTask.AssetID, AssetPMTask.AssetPMTaskID, AssetPMTask.PMTaskID
FROM PMTasks INNER JOIN AssetPMTask ON PMTasks.PMTaskID = AssetPMTask.PMTaskID;


In joining the assetPMtask table back to the PMtask table you are forcing Access to create a new matching record because of the INNER JOIN. As I mentioned earlier, the subform should be based on the solely on the table. I also noticed that you did not include the assetID which then did not connect the data back to the asset to which it applied

I also see that in your effort to make form building easier, you have denormalized the table structure (PM1, PM2....). I do not recommend this change. You should manipulate your forms to make them work with a sound table structure. Without a sound table structure, you will set yourself up for problems later on.

I've attached the modified database. I also created a subform for the various key numbers related to the asset.

[10 Gauge]

Thanks jz. I don't know how I am supposed to link each PM task to a PM number based on the interval and have it in an understandable format.

[jzwp11]

What is a PM number? Does it relate only to the task or does it relate to the asset or is it a combination of both?

[10 Gauge]

It's a combination of both, let me attach a spreadsheet for you to look at that kind of shows what's going on.... This spreadsheet shows the equipment numbers along with what happens at each PM level and when each PM happens.

[jzwp11]

Is the PM schedule actually dependent on the type of asset rather than the specific asset? What are the various numbers for each section 420/420IT and 701/702?

[10 Gauge]

420/420IT = Equipment Model #

701/702 = Our companies Unit # (asset #)

And yes it's dependent on the model rather than per specific unit #.

Is this a feasible way to handle this?