Hi all. I'm new to Access and I'm working on a database for my dad to use at home. He does work on the side in his garage and he's looking for something to keep track of completed work and to potentially use when he does his taxes. Right now he's using handwritten bills as invoices and Excel to keep track of income.
Background Info
He only works for about 25-30 different people so this shouldn't ever be anything too complex. Each of these customers could have anywhere from 1 to 10 vehicles. A few vehicles are identical but not owned by the same customer. He's not a typer so I'm hoping that once the data is entered then he can simply select it from a drop-down menu.
The Design
Here's what I've got so far. Any suggestions or required changes for what I expect to accomplish? My main concern is that the customer can be correctly tied to many vehicles and that the same vehicle can be tied to many customers.
The Goal
I'm sure that it's possible but I'd like to have a Work Order form that you can select an existing user from a drop-down or type a new customer name in and have it added to the Customer table upon updating the WorkOrder table. I'd also like to do the same thing with Parts and Jobs. Is it possible? It's probably more trouble than it's worth for no more that it will take to update any of these tables in a simple form but it would make it easier on him.
I need to be able to tie one customer to many different vehicles if needed. I also need that to work in reverse. He has two vehicles that are 100% identical but are owned by two different customers. It's going to be a rare occasion that he has more than one or two of those but I don't see anything but confusion coming from have two identical records (aside from the VehicleID). The CustomerID will be different unless the same customer owns two identical vehicles and there's probably about 100 different cars that he works on. Not impossible but not very likely.
I want to be able to select a customer while creating a work order and I want only the vehicles that I've linked to that customer to be available on the vehicle drop-down. Is this possible?
Misc
I may be wrong but the design is my mix of the easiest and most efficient. I realize that breaking the vehicle types into several tables and joining them to create an entire vehicle record is the most efficient. I'm an Access virgin so if I have to support this I'd rather keep it as simple as possible.
Can anyone confirm the success of this design or do I need to change my layout a bit before I move on to attempting to build forms and queries?
Thanks in advance.