Stuck on A Table/Form Design

[dgaletar]

Hello all. Here is my scenario:

I have a database where we track information for our fleet of trucks. So far everything is working perfectly! What I am looking to do is to create some type of “Service Work” report where I and the other mechanics can record the work that we do on the vehicles; almost like a “Vehicle History Record”.

The problem is that I am not sure what the best way to do this is. Here is what I have done thus far:

• I created a table titled “WORK”
  
  • In that table I have the following columns:
    
    • ID (Autonumber)
    • tb_CUA (the Vehicle Number)
    • Date (the date of the work being done)
    • Tech (Lookup of the Tech doing the work)
    • Service Type (Lookup from another table, “SrvcType” which lists the types of service that can be done)
    • Notes (Memo field)
  
  
• I also created a form titled “WORK”
  
  • It is a continuous form that lists all of the fields from the table “WORK”
  
  
• I also placed a button on the main form that opens the “WORK” form and associates it to the tb_CUA (Vehicle Number) from the main form. Here is the code for that button:

Private Sub Command883_Click()
   'Open Form (Notice the frm prefix to let the coder know that its a form)
     'Notice that form is opened in ADD mode, and with a filter applied.
     DoCmd.OpenForm "WORK", acNormal, , "[CUA#]=" & Me.tb_CUA, acFormAdd
 
   'Manipulate the form
     Dim f As Form
     Set f = Forms("WORK")
 
     'Set defaultvalue so that all records automatically get assigned the new id
       f.tb_CUA.DefaultValue = Me.tb_CUA
 
   'Set the form to be modal. This will prevent the user from clicking outside the form, until the form is closed again.
      f.Modal = True
 
End Sub

So far everything is working fine. Then I noticed my issue… the problem that I am having is that there may be several entries into the “Service Type” and “Memo” fields for the same Vehicle Number, date & Tech. Therefore, the Tech should not have to enter that information again each time, unless of course it is a new record altogether.

Can anyone suggest a way of doing this in a way that a beginner might be able to understand???

Thanks in advance for your help with this issue!

dgaletar

[orange]

You may get some ideas from this free data model

http://www.databaseanswers.org/data_...nt_version.htm

[dgaletar]

Thanks, orange. That's awesome! But what does it all mean??? lol Just kidding. But I am trying to figure out the connection to what I am doing. I guess that the "Service_Bookings" table is the closest to what I am looking to implement. I'm seeing a few labels that I don't understand. I know that PK=Primary Key, but it looks like there is also a FK, PF, and I think an rk. Any ideas?

Anyway, maybe the thought here is to make a "WORK" table for the general info, like the "Date" & "Tech", and a "WorkDone" table to hold the "Service Type" and "Notes" fields. Does that sound right???

[dgaletar]

Soooo, I was playing around, and I think that this might work... I changed the "WORK" table to ONLY hold "tb_CUA" (Vehicle Number), "Date" and "Tech". I then added another table, "WORKdone". This table holds the following fields: "ID", "tb_CUA", "Service Type", and "Notes".

I then created the form for the "WORK" table, and added a subform for the "WORKdone" table. I can now add several "Service Types" with related "Notes" to the specific Vehicle for the same "Date" and "Tech".

Am I missing something here, or should this work properly??? Wait, will do I need to relate the "Date" and "Tech" for the "WORKdone" details, or ARE they already related???

(Now I'm just confusing myself again!!!)

[orange]

If you really don't know whata data model is, then you should read and work through this tutorial. It weill help you with concepts and a procedure to analyze your needs and design appropriate tables.

http://www.rogersaccesslibrary.com/T...lationship.zip

There really is no shortcut to learning database design.

[dgaletar]

Thanks, orange, but I am a mechanic. Not a database designer. I've been charged with organizing the records for our garage, and of course I've been given no budget to do so. So, after reading through the many many "how-to" articles out there, I decided to try and do it with Access 2010. So far I have been able to accomplish everything that we need. And now, we need this.

So forgive me if I seem like I am stumbling through this process, but I am. I have been fortunate that many people have taken the time to walk me through the steps that I have needed to accomplish the great task that I have thus far.

With that being said, and I mean NO offense by this, if you don't have the time and patience to help me start with the basics to get this task done, then I will wait for someone else who does have the time and patience.

In the mean time, I will continue to stumble through it and I will certainly review your "data models".

Thanks!

[orange]

I gave you the most basic document/tutorial to deal with generic database design - and you have chosen to ignore it.

How many mechanics do you have that use a hammer and chisel instead of a crescent wrench or sockets?

There are underlying concepts in being a mechanic, an aircraft designer, a pilot and a database "builder", that are essential to proper technique. Think about it. Or hire a designer if you feel you can't do it.

[orange]

I just did a little research and found that JoeM has already cautioned you on design principles

I built this entire database from scratch with no prior Access experience

--from JoeM---
My stomach flipped a little when I read this. I once tried creating a database with no prior experience in Access or any understanding of relational databases, and it was an unmitigated disaster. Access is not nearly as intuitive as Excel, and if you do not know what you are doing, it is very easy to program yourself in a corner and drive yourself crazy. After a few months of getting nowhere, I stopped, read up on Relational Databases and introductory Access books, and started over from scratch. It made a huge difference. If this is going to be a big project, I would highly recommend doing this yourself, or hiring a consultant to create the database for you.

Here is a good write-up on relational databases and rules of normalization: http://www.deeptraining.com/litwin/d...aseDesign.aspx

And it seems you ignored him too.

[dgaletar]

orange, if I had my way I would be sitting in an Access 2010 class right now. But I don't have my way. I have a task that I have been asked to get done, and a couple forums with some very kind and very patient people who have helped me along with it.

If you don't want to be one of those patient people who help, than I thank you for your time. Cautioning me on "design principles" is like my wife telling me to stop going to strip clubs. She doesn't stop trying, but the end result is the same.

What I'm asking for help on seems like something that is done often in Access; using the database for some sort of "Work Progress" tracking. This task really shouldn't be that hard. If you have an idea of how I can do it, please pass it along. Pointing me to something that looks neat doesn't help me.

BTW, I often get people who ask me to walk them through fixing their own cars. I relate it to the economy that we are in. Anyway, when they do, if I offer to help (which is more often than not), I stick with them to the end. I don't give them some random information that a "mechanic" should know and send them on their way.

I also mentioned before that I HAVE NO BUDGET for this task. It sucks, but that's what I am stuck with. So hiring a designer is obviously out of the question.

And, we probably could have solved this puzzle by now if you would have just helped me.

[dgaletar]

Just in case anyone else feels like trying to help me with this issue, I have attached a copy of the current database for your review.

Thanks!