Ok, I have made a Database to keep track of my work and I have got everything working in the database except one thing. I have a tab that has 10 fields labeled Hr1, Hr2, Hr3, Hr4, Hr5, Hr6, Hr7, Hr8, Hr9, Hr10 and I need to have them Auto Calculate and fill in Field labeled "Total Labor". I tried making the "Total Labor" a Calculated Field but doing that it did not add up correctly and I was required to have all 10 fields filled in, which is not always needed. For more information The "Hr1" fields are drop downs or combo boxes not sure what it is called but I click on arrow a list comes down for me to pick my hours from the description of work I did
I am a Novice so please respond as simple as possible. I have attached my database please help

You have several structural issues with your database that need to be corrected first. The main one is this: Hr1, Hr2, Hr3, Hr4, Hr5, Hr6, Hr7, Hr8, Hr9, Hr10 These fields are an example of repeating groups which is a clear sign that your database is not normalized. There is a good discussion on normalization on the wiki found on this site. Additionally, you have a series of 10 or so jobs (what I did) D1, D2 etc. which are also repeating groups. If a vehicle has multiple jobs conducted on it, then that describes a one to many relationship. The jobs should therefore be RECORDS not fields and those records should be in a related table.

tblVehicles
-pkVehicleID primary key, autonumber


tblVehicleJobs
-pkVehicleJobID primary key, autonumber
-fkVehicleID foreign key to tblVehicles (creates the relationship between the two tables)
-txtWhatWasDone

Additionally, you have 2 code tables, the rule is that like data should be in 1 table. You would combine the two tables into one. You can add a field that designates which group of codes each belongs to.

Also, I noticed that you use many lookups in your Work table. Although Access has this capability, it is generally not recommended. This site provides details as to the problems that table level lookups can cause. Essentially, the info you have in lookups should be in their own tables if they are not already in one (i.e. makes of the vehicles).


Also, it is generally recommended to not have spaces or special characters (#,?, /,\,- etc.) in your table or field names.

Ok, I am not sure what you mean by "The jobs should therefore be RECORDS not fields and those records should be in a related table." As you can guess I am a Mechanic and this is my first time using Access and making a database. I do appreciate your help and hopefully your patience in helping me. The code 1 and code 2 tables I made them like that because that is how I have look them up and write them down, there are actually three separate codes I have to use at work "the Pre-code"(what I'm doing like Diagnose, Repair, Replace....Ect) the main code"(what Area of vehicle.A/C, Brakes,Cooling System....ECT)and the "specific code"(what part of area like if code2 is A/C then I installed A/C Compressor) I decided instead of using the third one I would use a text box in the database(also my service writer inputs the last code cause there are so many) That is why I have the description of Code 2 displayed then I write what I did, cause I would not at a glance know what code 2 means if I had to look up the Shop # or Work order to know what area and what I did. Also I will be Adding more Codes to both Code 1 and Code 2 tables as I come across them. I will make a table for the makes of vehicle I got that look up thing from a database I found online for vehicle maintenance but it would not do what I needed so I made my own at least I am trying.

Did you look up normalization on the wiki on this site? If you have not had a chance; here is a link to it. Normalization is the key to any successful database.

As to the codes, they sound like they are inter-related, but they are still all codes, so they belong in the same table (one of the rules of normalization: like data in 1 table). We will need a table to related codes to one another more on that later.

From what I understand, you receive cars in to be repaired. Those repairs are conducted under work orders. Now can a work order only be applicable to 1 car or can you have multiple cars on the same work order?

Can a car be brought in many times and thus could be found tied to several work orders over time?

How do you define a job?

thanks for link will read that asap, As to Work Orders they belong to only one car, but that car can have many work order from previous work done. Jobs ... the only way I can think to explain this is A officer turns in his shop(car) and on the tie-up card under symptoms he states what is wrong with his shop(car), which can be from just a PM(oil Change) to however many different things he says is wrong with his car, I just picked ten because usually a car doesn't have more than 10 things wrong with it and my thoughts where if it did I would just make another entry for it to continue the work on the car.

This is a main problem with your database. With your current structure, if you had to and another item (#11), you would have to redesign your table and all associated forms, reports and queries that are tied to that table. That is not something you want to do. That is why you would treat each item/job that needs to be repaired as a record.

Since you can have a car come in many times, it would be best to have a table that holds the vehicle information. You type it in once & only once. Then you would tie the vehicle to the work order.

I have taken your database and have started to make the revisions. The revised database is attached. It will still need more work, but I wanted to provide an example of what I have been describing.

Thank you. I will look at it. I am currently in the process of combining the codes together and making a table for type of vehicles. Working for the city I do not make much but would be willing to pay something for your help if your interested in completing all the changes that need to be done I would make tables or anything else that is simple enough for me to do, with your guidance. if not I would still appreciate your help in completing my database. I am reading all the stuff you told me to read and I am trying my best to understand it all, I just would like this database finish a quick as possible. I have like 30 sheets of paper I have full with information of my Jobs that I have done.

You're a mechanic? You understand using the right tool for the job, then. Would you use a chainsaw to pound nails? I'm guessing not.

Trying to use Access the same way you'd use a spreadsheet (?e.g., Excel) will only cause you (and Access) considerable headaches!

Access is optimized to work best with well-normalized data. If "relational" and "normalization" are unfamiliar terms, plan on brushing up before expecting to get good use out of Access.

Here are the four learning curves I suggest you tackle before expecting to get an Access application up and running. ... and "like this database finished as soon as possible" ?!?

1. Relational database design and normalization - Access works best with well-normalized data.
2. Access tricks/tips - Access does things differently. You wouldn't expect to use what you know about MS Word to do formulas in Excel, would you?
3. GUI - Graphical User Interface - if it isn't easy to understand and use, they won't.
4. Application Development -- ?never built a house? Where to start? What to do? When to stop?

You may have already surmounted several of these, but they will all affect how, how easily, how quickly you can build this application.

Good luck!

One of the main goals of the forum is for us to help you learn Access in order for you to design and create your database application. As a volunteer, I pop into UtterAccess as I have time; Unfortunately, I do not have the time to commit to a for-hire project. You can post in the Job/project forum on this site to see if others would be interested in designing an application for you for a fee.

If you decide you want to do it for yourself, it will take some learning and a commitment in both time and effort on your part to see your project through to the end, and we stand ready to help you in that effort.

Ok, I have imputed the models and since you already combined the code tables together, what is the next step... I am not sure what to do next???
Thanks
John

The next part is to continue working on the work order table and how it relates to the jobs and the labor. Could you please explain your how you work with work orders, jobs and labor? From what I understand, a work order has many jobs (things you do to the vehicle).

ok, the Employee(Officer or Civilian) fills out a tie-up card with Shop number(which is unique to each vehicle), year, make, model, color, and writes down the Symptoms( what is wrong ), then My Service writer assigns a Work Order to the Shop Number and inputs into the system the Job codes the are easy to see, Like PM, as I inspect the vehicle I will let inform him if new Job Code are needed, like brakes, which he adds to the same Work Order. I then on the back of the tie-up card will write down the Job Code and Labor time that I used for the work done per Job code. Then once I am finished with vehicle the service writer closes the Work Order. The reason I am making this Database is I get asked a lot about vehicles I worked on last week or last month, or if I remember what I did to such and such vehicle. Also I sometimes have to search vehicles for Drugs and or Money on suspect vehicles those do not get a work order so I would need to be able to input a made up Work Order number like 0001 maybe, that way if I get called into Court I can say exactly what I did to the vehicle. Hope that explain what you need to help me

Thanks John

OK, if you look in the DB I posted, you will see that I removed a lot of fields in your work table, but the basics of what your service writer inputs are there. Now as to the codes. Since many codes can apply to a work order, that describes a one-to-many relationship. Further, since a code can apply to other work orders as well, that describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (work orders and codes in your case), you have a many-to-many relationship which requires a junction table as follows

tblWorkOrderCodes
-pkWOCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkCodeID foreign key to tblCodes

I would assume that you would have a related labor charge for each code on a work order, so I believe you need a foreign key to the labor in the above table as well

tblWorkOrderCodes
-pkWOCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkCodeID foreign key to tblCodes
-fkLaborID foreign key to Labor table

Regarding the descriptions in the labor and code tables, I cannot help but wonder whether a relationship exists between the codes and the labor that has not been captured yet. Earlier you mentioned that some codes are related to other code, so we still have to take care of that as well. We will need some more detail from you on the labor and the codes to completely flush out any additional relationships.

Ok, if I understanding you correctly I need and will make another table with the fields from your second table.

I believe the relationship for Job Code and Labor should be=
Job Code 1 to Job Code 2
Job Code 2 to Labor

All of Job Code 1's can be done to Job Code 2's
but Labor is only from the relationship between 1 & 2 with Job Code 2 being the deciding factor

for example Brakes-
Symptoms= Brakes pedal goes to the floor
1) I Diagnose(code 1= 005) brakes(code 2= 013)
2) I replace(code 1= 015) brake pads(013) if I was imputing Code 3 Which I want to use text box instead cause there are so many Code 3's it would be for brake pads (code 3= 001 for front 002 for rear) which would take up two separate Entries where as for my purpose I can use one enter and after code 2 type in R/R front & Rear Brake Pads. Is this a good enough description or do you need me to try and elaborate more.

John

So it sounds like you have 2 types of codes, diagnosis codes and action/task codes (all codes still in the same table; just use a field to differentiate the by type). Further a code can be related to another code. I think you need a table to related the codes (Diagnose(code 1= 005) brakes(code 2= 013)


tblRelateCodes
-pkRelateCodeID primary key, autonumber
-fkPCodeID foreign key to tblCodes (this would represent the primary code; the 005 in your example)
-fkSCodeID foreign key to tblCodes (this would represent the secondary code; the 013 in your example)

Now tied to each work order you many have multiple diagnoses codes, so back to the work order/code table, I removed the labor reference and renamed

tblWorkOrderDiagnosisCodes
-pkWODCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkCodeID foreign key to tblCodes

So if you have a work order for which you diagnosed brakes you would have the key field for code 2 (013) in the above table. We know from tblRelateCodes, that code 2 (013) is related to code 1 (015), so we would not need to include both codes in tblWorkOrderDiagnosisCodes


Now, you have to capture the tasks/actions you do relative to the code 2 (013), which may involve more than 1 task/action I assume (please correct me if I am wrong). Further, you have a relationship between action codes: replace(code 1= 015) brake pads(013) which should also be captured in tblRelateCodes

tblWODianosisActions
-pkWODActionID primary key, autonumber
-fkWODCodeID foreign key to tblWorkOrderDiagnosisCodes
-fkCodeID represents code you wanted to input via a text box

Regarding the codes you wanted to input via a text box, these codes must also be in tblCodes. I do not see a way around that since relationships are involved. If you have a list of these codes in a spreadsheet, they can be imported easily into Access.

Ok, I will try to clarify. Code 1 is the Task code, whether is it me Diagnosing, Replacing, Repairing, or any other Code 1 description, Code 2 is what part of the vehicle I am doing the task too, Code 3 is a more specific description of Code 2.

For example I will use the Brakes again cause I happen to know what two of the 3 Codes are

Task (code 1) Replace---015
Part of vehicle(code 2) Brake System---013
More Specific part(Code 3)Front brake pads---001

I can not get a list of all the Code 3's my Service Writer does not know all of Code 3 he has to look them up in the Computer as he is putting them in, once he imputes Code 1-Code 2- for code three he has to look them up as he looks under Brake System for instance the computer pops up a list of all possible code 3's that relate to Brake System(Code 2). That is why I what to use a Text box for me to type in what I did that way when I look up what I did to a vehicle I know what I did cause I would not know what Code 3 means by looking at a number. Also when I am filling out my paper work All I am required to do is Code 1 & Code 2 then I write down a description of what I did. It is my service writers job to figure out which Code 3 is the best description for what I did, I have seen him use two different Code 3 to describe the same work done on two different vehicles. He has a space to input notes where he describes what I did to vehicle so even if code 3's do not match up between different vehicle getting the same job done reading the note lets you know. I have asked him about that and he told me that he does not always remember what Code 3 he used the last time that same work was done, The more common work done he remembers what Code 3 he used. I do not even have a complete list of Code's 1 & 2 I just have the most common Codes that we use and I will add to Code's 1 & 2 as I find more of them. I hope this helps clarify why I want to input Code 3 via a text box

Thanks again for all your help it is really appreciated

John

Is there any way to extract all the codes from the computer system you refer to?

Even if a code is not yet in your database, there are ways to use the Not in List event of a combo box to add it on the fly, that way it will be there from then on. That is a form issue, but you still have to have the table structure to support it.

Not that I know, When I first started with the city all they gave me was the small list I have of Code's 1 & 2. When I decided to make this Database I asked again for a Code List and All he gave me was Code 1 & 2 I asked about Code 3 He said there where too many and that he does not even have a list he has to look them up as he goes. I then asked about Labor and all he gave me was the small list I have imputed, I have asked him while doing work about Labor cause I am required to put that information on the back of the Tie-up card, and he looks at work I have done and tells me what Labor time to put down. So I am not even sure if I can keep up with labor correctly but worst case I can keep up with actual labor.(how long it really took as oppose to what they call book time, example- A PM = 0.9Hr. but it might only really take me 0.5Hr. we have to account for at least 6 1/2 to 7Hr.a day for the city to think we are putting out enough work the other 1Hr -1Hr 1/2 is accounted for doing paper work, getting the vehicle, and parking vehicle.

As I said, we can use the combo box's On Not in List event to add new codes when necesary. As to the labor, you will have to decide how you want to handle it. You can store it in the table with the respective code or you can just add directly for each job (so it can vary for the same code). If you want to store a set value associated with a code, then you will need a field in tblCodes. If the labor for a particular code is dependent on the earlier code selected, then the labor should be stored in tblRelateCodes. If you want to have the labor be variable then you would add a field to tblWODianosisActions.

I understand what you are trying to do, it's just that my service writer does not write down the Code 3 on the Tie-up card, and he does not immediately close the Work Order cause he is busy with 7 other Mechanics and helping Employees Tie-up there vehicle and signing for them when complete. He sometimes gets so busy he does not finish closing Work Order till the next day and all I am wanting to be able to do is input what I do as I do it, that is why Code 3 or instead of calling it Code three call it "What I did " via Text-box input no code. If the Textbox "What I did" would remember what was typed in then unless I am doing a total new job as I type it would/could auto fill in that way all my jobs that are the same would have same "What I did" typed in.

We can still have a field for you to input what you did (txtActionPerformed). We can leave the code field there, and the service writer can fill it in at a later time.

tblWODiagnosisActions
-pkWODActionID primary key, autonumber
-fkWODCodeID foreign key to tblWorkOrderDiagnosisCodes
-fkCodeID represents code you wanted to input via a text box
-txtActionPerformed

The thing is I am the only one filling this Database out, no one else this is for my personnel use my own records. My service writer will not do that cause he is not getting paid to do it for me and like I said he does not record the Code 3 on paper anywhere, and is not willing to do it for me with all of the work he has to do, he says he does not have time.

Ultimately, what you do with your database is up to you. I was providing the structure based on normalization rules. You can change the code (3) field to a text field if you wish, or you can leave it as is and add codes as you need them (via your form).

Ok, so do I make this table?

tblWODiagnosisActions
-pkWODActionID primary key, autonumber
-fkWODCodeID foreign key to tblWorkOrderDiagnosisCodes
-fkCodeID represents code you wanted to input via a text box
-txtActionPerformed

Or is there a change that need to be made for for Textbox entries only?

Also do I need to make these also, I am just not sure starting to get to technical so I am not sure what these are for and do?

tblRelateCodes
-pkRelateCodeID primary key, autonumber
-fkPCodeID foreign key to tblCodes (this would represent the primary code; the 005 in your example)
-fkSCodeID foreign key to tblCodes (this would represent the secondary code; the 013 in your example)

Now tied to each work order you many have multiple diagnoses codes, so back to the work order/code table, I removed the labor reference and renamed

tblWorkOrderDiagnosisCodes
-pkWODCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkCodeID foreign key to tblCodes


After making these changes what would be next to do??

If you intend to store codes 1 and 2 so that you can choose the codes when entering a new work order, then the above tables are necessary

For code 3, if you just want to note the code at the time you enter what you did rather than select it from a list then you would alter the table tblWODiagnosisActions
as follows:

tblWODiagnosisActions
-pkWODActionID primary key, autonumber
-fkWODCodeID foreign key to tblWorkOrderDiagnosisCodes
-txtCode
-txtActionPerformed




After your tables are set up and the relationships established, then you would start working on your forms for data entry and then eventually any reports you may want.

Thanks will make those tables now, will probably need help with the relationship part but will first look at the tables you made and see if I can figure it out. Just making sure these tables are to be made going up and down or side to side??

John

Not sure what you mean by this; it sounds like you might be thinking of row/columns in Excel.

Please post back with any questions & of course, we can look at your final table structure to make sure it is OK before you proceed to forms.

yes that was what I was meaning. I made mine just like you wrote them going from top to bottom, I am having trouble making the relationships. Can not get





fkPCodeID foreign key to tblCodes relationship no matter how much I tried. can not seam to figure it out.

this is the only one I have tried so far

John

Join the pkCodeID of tblCodes to the fkPCodeID of tblRelateCodes. To do the joining click on the pkCodeID field of tblCodes then while holding down your mouse button drag it to the fkPCode

Now, add tblCodes again to the relationship window; Access will give it a name: tblCodes_1 (or something like that). Join the pkCodeID of this table to fkSCodeID of tblRelateCodes.

For both joins, you will want to check the enforce referential integrity check box.

Ok, I did that and I get a message that says


" Relationship must be on the same number of fields with the same data types"

if I do not check the enforce referential integrity check box it makes a relationship what am I doing wrong??

I think I figured it out, just not sure about some other tables. The tables that are in the database that you made and I added to are:

Labor
tblCodeGroups
tblCodes
tblColors
tblMake
tblModels
tblRelateCodes
tblVehicles
tbleWODiagnosisActions
tblWorkOrderCodes
Work

I do not have one called "tblWorkOrderDiagnosisCodes" is this the same as "tbleWODiagnosisActions"?

I replaced tblWorkOrderCodes with tblWorkOrderDiagnosisCodes (see below) since the first and second codes are for the diagnosis; you would only store code 2 in the tblWorkOrderDiagnosisCodes since code 2 would relate to code 1 via tblRelateCodes. Then you may have many related action codes


tblWorkOrderDiagnosisCodes
-pkWODCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkCodeID foreign key to tblCodes

After some thought, I think the table I presented needs to be changed. Since a diagnosis could apply to more than 1 vehicle system (brakes, suspension etc.) it is the combination of code 1 & code 2 that classifies the work order diagnosis, so I think tblWorkOrderDiagnosisCodes should be as follows:

tblWorkOrderDiagnosisCodes
-pkWODCodeID primary key, autonumber
-fkWorkOrderID foreign key to Work table
-fkRelateCodeID foreign key to tblRelateCodes

I am getting the same error and can not seam to figure this one out

" Relationship must be on the same number of fields with the same data types"

To let you know what I did to make the first problem with "pkCodeID of tblCodes to the fkPCodeID of tblRelateCodes work was

In the table instead of it being up and down like this=

{______1}{pkRelateCodeID}{______}
{______2}{_____fkPCodeID}{______}
{______3}{_____fkSCodeID}{______}

I changed it to look like this=
[pkRelateCodeID][fkPCodeID[]fkSCodeID[]Click to Add]
{___________1}{________1}{_______2}{__________}

The { } are The Squares you type into. and the [] are the name of the fields

Please let me know what I am doing wrong

I'm not sure what you are doing. I usually create my tables in design view, so the field names are listed one per row (in 1 column). When you switch to datasheet view, the field names are across the top (like column headers). All fields with a prefix of "fk" are foreign key fields, and must be long integer number datatypes. I have attached the database from earlier with the tblRelateCodes and the relationships established (see relationship window) to illustrate.

Ok, I looked at mine and I was doing it in Datasheet view will remake all the tables I made again your way in Design view. Will let you know if I have anymore problem with the Relationships, hopefully not and will be asking you with help with forms and reports

oh, I do have a question? Sometimes when working on a vehicle I have to order a part that I might not get before end of work day sometimes it might take one or two days or even a week or two, Is there anyway I can continue the same job with the same work order but with a different date. What my service writer does is put in my labor time of what I did on the day I did it, for example Monday I have a vehicle that I have to do a PM(oil change) and also I Diagnose the A/C and need to replace the Compressor but they have to order one and it does not come in till Thursday. My service writer on Monday will long/input the time for the PM and Diagnose A/C and then on Thursday he will log/input the time for Replacing the A/C Compressor. I also, till this database is up and running, write it down on Monday then on Thursday I write shop # then note "continue" then note what I did "Replace A/C Compressor, Vacuum, Leak test, and Recharge to Spec"

Absolutely, you can add any number of records to capture all of the actions you do for each code combination for a work order. The tables I have shown are just the framework, you will still need to add other pertinent fields. For example you can add a date field to tblWODiagnosisActions to record the date when you did an action.

tblWODiagnosisActions
-pkWODActionID primary key, autonumber
-fkWODCodeID foreign key to tblWorkOrderDiagnosisCodes
-fkCodeID represents code you wanted to input via a text box
-dteActionPerformed (dte prefix = date)
-txtActionPerformed

Thanks, now I believe I can start to designing a form?? not sure where to start on this would like it to be like the one I made that had three tabs or three screens?? not sure about the correct terminology. It would be cool if I open database and on PC screen it comes up with (screen 1 or tab1) labeled Information which would have=

Date
Work Order
Shop Number
Year
Make
Model
Mileage
Engine
Color

then have a button labeled "next" that will go to the next (screen or Tab) labeled Symptoms or Problems which would have just =

Symptoms------(to fill in, but would still show date and Work order from previous screen or tab)

then be able to click "next" again to go to last Screen or tab labeled "Work Done" which would have=

Job Codes
What I did (Code 3)
Labor
but would still show date and Work order from previous screen or tab

then have a button that would save and automatically start new blank Form

Not sure where to start when I made mine I picked from one table that I made and this database has many and just not sure where to start

Form design and navigation is really a designer's choice. Everything you described is possible. For those items, that are now housed in tables such as make, model, color etc., you would use combo boxes instead of a plain text box. The codes (1&2) would be selected from a combo box as well using a technique called cascading combo boxes--do a search on this forum for that and you will find many references on how to do them.

This site from Crystal (another UA member) has some video tutorials that should help you design forms around your table structure.

Ok, Thanks for all your help. Hopefully I can finish the rest by myself, if not will post on this same thread once finished with database is there a way to close this as solved or whatever??

John

You're welcome. As far as I know there is no option to mark a thread as solved/complete.

ok, I am having trouble with making my form.

I have put in the "Make" and I am trying to put in the "Model" but can not make the Model drop down only show what belongs to the "Make"????

example="Make" drop down pick Ford on "Model" drop down all Models from all Make show not just Ford vehicle

also not sure how to add the codes 1 & 2 to my form?

John

To get the make and model combo boxes to synchronize, you need to use a technique called cascading combo boxes. There are many references on this website that address the topic. Roger Carlson has some demo databases on his site that illustrate the technique.

As to the codes, you will need a subform (the many side of a relationship is generally represented in a subform) based on the table tblWorkOrderCodes. You will need the cascading combo box technique again within that subform to handle the codes since they are related to one another