Vehicle Repair Database Table & Form Design

[Sketchin]

Hello, I am designing a database for a tank trailer repair shop and need a bit of guidance on how to structure my tables.

At a high level, the user will input a Work Order# and all relevant info into a parent form (customer, PO#...etc). They will then choose tasks on a subform (changed shocks, brakes, etc...). After adding the task, they will then need to assign location, vehicle side and compartment#.

My current structure works from a normalization standpoint (AFAIK), but I can't figure out an elegant way to setup the subforms for entering tasks and assigning locations.

Here is an example of what the data would look like in a spreadsheet:

Task	Vehicle Side	Compartment	Location
Replace Shock	Left		Axle 1
Replace Shock	Left		Axle 2
Replace Shock	Right		Axle 1
Replace Valve	Front	1

Vehicle Side, Compartment and Location are all pulled from lookup tables and stored in a junction table for task details. This allows a task to be assigned many sides, locations etc...

The Quote Tasks table is where the individual tasks will be stored. There will be many tasks per work order, each with many locations etc...

The trouble is more from a UI/UX standpoint. I have a single form for selecting tasks, and a continuous form to assign locations and sides to the task. Ideally, I would have a continuous form for entering tasks, with a continuous subform for locations and such. Access doesn't allow this, so I don't really know how to elegantly design this form structure.

 

[MarkK]

Maybe not exactly to your point, but I wouldn't link to a table just to get one field. A link is a necessary evil, a data point you create in order to support a relationship between entities, but if the related entity is itself only one data point, omg, just put that data directly in the object it belongs to.

Let's say you want to create a database for students at a school, and there are two kids named Suzy, well, then would you do...

tStudent
StudentID
FirstNameID --> so you can link to Suzy??? It's just a single data point!!!
LastName

Rather, create one lookup table with a Key field, and that field holds data like Side, Location, Compartment, Category, like...

tLookUp
LookupID
Key
Data

Then store tTaskDetail.Location as ShortText in the table, and in your UI, base a combo on the lookup table with SQL like SELECT Data As Location FROM tLookup WHERE Key = 'Location'. This still lets you independently manage the location list. Still stores and preserves the actual and historical location in the TaskDetail row, still does everything you need done, but with far less complexity.

 

[June7]

For your subforms, review https://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

 

[Sketchin]

Maybe not exactly to your point, but I wouldn't link to a table just to get one field. A link is a necessary evil, a data point you create in order to support a relationship between entities, but if the related entity is itself only one data point, omg, just put that data directly in the object it belongs to.

Let's say you want to create a database for students at a school, and there are two kids named Suzy, well, then would you do...

tStudent
StudentID
FirstNameID --> so you can link to Suzy??? It's just a single data point!!!
LastName

Rather, create one lookup table with a Key field, and that field holds data like Side, Location, Compartment, Category, like...

tLookUp
LookupID
Key
Data

Then store tTaskDetail.Location as ShortText in the table, and in your UI, base a combo on the lookup table with SQL like SELECT Data As Location FROM tLookup WHERE Key = 'Location'. This still lets you independently manage the location list. Still stores and preserves the actual and historical location in the TaskDetail row, still does everything you need done, but with far less complexity.

Thanks for the feedback, I think that is a great idea due to the reduced complexity. Intuitively I was thinking there was a better way, but I just couldn't quite get there to see it.

 

[Mark_]

To add to MarkK's post, I also add an "orderby" numeric. Often you'll have values that are intuitively together, but may not if in alpha order. Think "Front Center" "Drivers side Front" "Passengers side Front" "Drivers Center" "Passengers Center" "Drivers Rear" "Passengers Rear" "Rear Center".
This is OPTIONAL as not all looked up fields will need it, but is really handy when it makes far more sense to the end users.

I would also have a "Task Type" that further limits your locations AND would be in TaskDetail. Depending on data, this could be pulled from the tblLookupTaskList. Haven't worked on Tanker Trucks, but having had to do user maintenance and inspections on trucks in the past, I know changing the bulb on a signal indicator needs a different type of "Location" than indicating "fuel line from driver side fuel tank leaking".

 

[Pat Hartman]

Creating dozens of lookup tables is a lot of work and creates a maintenance burden unless you create forms to manage each separate lookup table. I use a mini-app that gets added to all new applications to avoid the issues. The lookups are essentially used to populate the RowSource for combos so technically, no join is required in your main queries. The validation is done as the field is initially filled in on your maintenance form. My sample includes a sort field that is used as the primary sort field for the RowSource combos with the alpha field as the second sort field. When the user wants the alpha sort, he can skip over the sort field as he creates new entries in a lookup table.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk