See if my analysis is correct:
Each customer may have many lifts. Each lift may be one of 3 types. You need to record a servicing and breakdown history for each such lift.
First cut at what you might use:
Customers::__pkCustomerID = Lifts::_fkCustomerID
Lifts::__pkLiftID = ServiceHistory::_fkLIftID
LiftTypes::__pkLIftTypeID = Lifts::_fkLiftTypeID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
This approach may be complicated by:
3 types which have different fields in them.
Not being in your industry, I don't know exactly what differences there are for your different lift types nor for which of the tables that I have mapped out in relationships for you, but there are at least two ways such lift type specific differences can be handled with either this data model or for one similar to it.
Yes the analysis is correct.
The fields in the 3 types of lift are not completly different so I suppose I could suppress the unused fields on the particular form.
This would mean that I could have a lift type table with 40 + fields but only be using 5 or 6 with one type of lift!
How would I differentiate on a form showing customer with lift detail in a portal?
There are multiple ways to do that. You can use a tab control and each tab can be for a different lift type. A script can automatically select a different tab or you might have a portal to multiple lifts on each tabl with each portal filtered to only list data on lifts from one of the three types.
You can also devise completely different layouts and use scripts/script triggers to change layouts so that as you move from lift to lift, the layout automatically changes to the one customized for the needs of that lift type. Note that you might be pulling up different groups fo service History records rather than different lift records when you do this.
"40+ fields, but only using 5 or 6" seems pretty extreme for different types of the same equipment, but you know the industry and I don't. I'll just note that even with completely different fields, 3 types of lifts produces 18 fields, so that 40+ figure seems too large.
And it can also be sometimes useful to add in 3 additional "detail" tables that handle some of the details that are different for each type of lift. A given lift record would then link to only one of the three tables set up for this purpose.