Results 1 to 5 of 5
  1. #1
    bacanter01 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    3

    Vehicle work order database design

    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.Click image for larger version. 

Name:	Relationships.PNG 
Views:	50 
Size:	34.9 KB 
ID:	6826

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't recommend separate tables for vehicle types.

    I have seen some template databases for this sort of application. You might Google: Access work repair order database
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bacanter01 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    3
    Why not separate vehicles from customers? I'm doubting you but I am curious why.

    By adding customers to the vehicles table will I be able to have a drop-down populate with each customer only listed once and then select a vehicle owned by that customer? example - John has 5 vehicles and Bill has 3 vehicles, if I select John I only want to see 5 vehicle options and if I select Bill I only want to see 3.

    After thinking about the VehicleCustLink table I've come to the conclusion that it shouldn't matter that an identical vehicle is in the same table. It will have a different ID and I don't see much value in reporting on repairs by vehicle type alone. Any queries will be against customers, not the vehicles.

    I've spent a week of evenings searching for something to do this and I've only found about half a dozen templates that are even remotely close. None of those will really do what I'm hoping to accomplish. I have found a few programs that will do 80% of what I want but they are all $400+ and that isn't an option. I keep ending up with something that requires an inventory of parts and you can't reuse any of the information unless it happens to be the same part number. In my scenario I will have one alternator, not an alternator for a Ford Escort and different one for a Chevy Cavalier. The price for the part will only be on the work order and he'll enter it manually. It's pretty painful to watch him take a minute and a half to type alternator and find out that he really typed altermater. If all goes as planned then he (I) will type most things in and he can select from a list.

    I may have just realized another issue. Can you store multiple things in a single field? An alternator may also be accompanied by a belt. Is there a better way for that?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did not suggest combining customers and vehicles. You stated 'I realize that breaking the vehicle types into several tables and joining them to create an entire vehicle record is the most efficient.' This is what I was responding to. Why would vehicle types be in several tables? How would joining them then 'create an entire vehicle record'?

    Yes, combo/list boxes can be set up to offer unique list of items such as customer and then in another combo choose from vehicles associated with that customer. This is cascading (dependent) combo/list boxes. Refer to tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.

    It is possible to save multiple values in a single field but I would never use that approach. You can define a field as multi-value if you set Lookup to a combo or list box and set AllowMultipleValues to yes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    bacanter01 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    3
    Sorry, I misunderstood what you were referring to. This database will be small enough that it doesn't matter but from what I remember in college, it's more efficient (storage wise) to breakup duplicate data. That's where I was coming from when I said that.

    The tutorials look awesome. I've been through a few on other sites but about the point that I felt like I was learning something they want you to purchase the rest of the series.

    Thanks for the quick reply, I'm sure I'll be asking more questions before this is over

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parts/Work Orders Database Design
    By snewton in forum Database Design
    Replies: 5
    Last Post: 03-13-2012, 07:06 PM
  2. Replies: 1
    Last Post: 04-13-2011, 11:14 AM
  3. Design tables in order to keep history
    By snoopy2003 in forum Database Design
    Replies: 2
    Last Post: 02-22-2011, 02:43 PM
  4. Generate Sum from a work center and order #
    By KrenzyRyan in forum Programming
    Replies: 2
    Last Post: 01-19-2011, 09:51 AM
  5. Need help with database to display vehicle information
    By jlmnjem in forum Database Design
    Replies: 1
    Last Post: 07-25-2006, 08:48 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums