Help please

[Sarah Byrne]

Hi There, I am a bit stuck at the moment with my forms. Getting #Error! and #Name? messages left, right and centre. I have put the tables that I have and have attached my relationships with a brief description of the outcome that I want. I apologise for the essay but any help will be appreciated dearly.

I have started a access database that does or I want to do the following.

What my aim is that you can add in all the addresses and all of our job numbers and details and you can also add in all the parts that we have used on that job. When you go onto my main page you can access the job form which comes up with all the information needed. I want to be able to add new jobs which I have added a button to do this but it doesn't want to work. I also want to be able to put part details. For example: Bosch Boiler, Blue, 20L, Serial No 35435736 Model No 900i bla bla bla. and for this to come up on the job, but then if I want to add a new part to this I can it will come up under it so that you have the boiler and a valve and so on. But for the parts, as they come up often, I want to be able to say that Job 6789 had a part no 1,2,3,4,5.... to save him time typing this up for each part. He also wants to be able to have a report that has all of this information on it at the click of a button that will pdf it and then he can email it. I have the email to pdf but have to save it via the email. And the information doesn't actually come up, it just looks pretty at the moment. I want to achieve, telling the computer to do something and it do it

. Basically in a nut shell the final outcome is to have a piece of paper that has all of each jobs details on it. So that you can pull out a file and no exactly what is going on in the job. If he needs to order a new part for a job that happened 2 years ago he can find it with a click of a button. But to do all of this I need to be able to input information and it stay there and add to the other information. And for my report to show all the information that it should rather then either blank or #Name? or #Error! both of which are rather annoying when from what you can see it is correct. I am a noob to this and have bitten off a rather large bite.

tblAddress
AddressID (PK)
AddressType (Combo box)
AddressLine1
AddressLine2
Town
County
Postcode

tblContact
ContactID (PK)
Company
Titel (Combo box)
FirstName
LastName
Email
JobTitle
Department
Phone
Mobile
Fax
Notes
Attachment

tblContactAddress
ContactAddressID (PK)
ContactID (FK)
AddressID (FK)

tblJob
JobID (PK) (Sage Code)
SiteName
AddressID (FK)
JobType (Combo box)
Comments
RelatedIssues
PartID (FK)
Manufacturer
Model
Colour
PartType (Combo box)
Size
Install Date
SerialNo


tblJobAddress
JobAddressID (PK)
JobID (FK) (Sage Code)
AddressID (FK)

tblJobPart
JobPartID (PK)
JobID (FK) (Sage Code)
PartID (FK)

tblJobSheet
JobSheetID (PK)
JobID (FK) (Sage Code)
OpenedBy
AssignedTo
OpenedDate
Status (Combo box)
Priority (Combo box)
BriefDescription
DueDate
Attachments
Comments

tblJobSheetContact
JobSheetContactID (PK)
JobSheetID (FK)
ContactID (FK)

tblPart (Added this to tblJob as it was annoying me that it wouldn't do what I wanted it to do

PartID (PK)
PartInfoID (FK)
Installdate
Comments
SerialNo
AddressID (FK)

tblPartInfo (Added this to tblJob as it was annoying me that it wouldn't do what I wanted it to do
PartInfoID (PK)
Manufacturer
Model
Colour
PartType
Size

Hope this information helps.

Thanks in advance

 

[plog]

Oh boy. Relationships shouldn't look like that. They should resemble branches on a tree, not a spider web--there should only be one way to get from one table to the other. For example, I can find 4 paths from tblJobAddress to tblAddress.

Before you start even thinking about forms, reports and emailing items out of here, you need to get that structure correct. I think you need to wipe everything away from that image and start with just a few tables. What do you believe is your main table? And then what other tables connect only to that table?

Explain the difference between tblJob and tblJobSheet.

 

[Sarah Byrne]

I didn't believe that the relationship was the issue. My main table is for the job to job sheets. The tbljob is the jobs that we have going on and then the tbljobsheet is a table that then creates a report based on the parts and the job. I wanted them separate so that each job can then be associated with a report based on what is going on in that job. Im trying to explain this as best as I can but don't think I am doing too well .

Hope this helps.

 

[plog]

No it didn't help. Like I said, put forms, reports and anything else out of your mind until you have your structure correct. Another thing, pretend databases don't exist, when describing things tell me how you're real world exist not how you think it fits into the model of a database.

So, let's start from the beginning. What is a job? What is a job sheet? Why do you need seperate tables for them?

 

[Sarah Byrne]

Sorry Right, we are a installation firm and "jobs" are the jobs that we are working on. For each job we install lots of different things. i.e. boilers. "jobsheets" are to show what has been installed on that job i.e. boiler, valve, pipework, lighting... Job sheets are just a save and print and for his record. Each table holds its own. So for example,

Job 6789 at 17 Long Road, Hertfordshire, HA0 7AL has a boiler, valve, pipework, lighting. On the 17/02/2014 the boiler Model 900i, in blue, 20L exploded, we need to go back on the 20/02/2014 to replace it this is "URGENT" and is a "Mechanical Job". Joe Blogs at Address Bla Bla installed the last one on ././. and Jane Doe needs to go back to this site on this date.

I want to be able to have most of the information separately and be able to click here and click there and poof there's a sheet of pretty headed paper that states all of the above that I can save and send. But due to the parts coming up often I want that separate and the jobs I also wanted separate to the job sheets.

 

[plog]

I want to be able to have most of the information separately and be able to click here and click there and poof there's a sheet of pretty headed paper that states all of the above that I can save and send.

From where you are now to where you want to be, you are in over your head. I would seriously consider hiring someone for this project.

Access can do all that you want, but I think creating the system you want would take you 6 months of full-time work. If you want to try it, I suggest you read up on normalization: http://en.wikipedia.org/wiki/Database_normalization. Work through a few tutorials, then chop your ambitious goal of an all encompassing system into a few manageable parts and work on getting those parts to work one at a time.

 

[Sarah Byrne]

So far most of my forms work ok, its just a case of getting the information to go to the job sheet, to be able to add new information without an error and for the information that I am adding into frmParts/frmPartsInfo to go into frmJobSheets/frmJobs and stay there in a list. From what I can see im half way there, I just need a nudge in the right direction.

 

[EternalMyrtle]

Like I said, put forms, reports and anything else out of your mind until you have your structure correct.

Plog is right. There are a lot of things I didn't notice about your table structure yesterday when you were posting in the forms section (I was worn out by the end of the day). Glad you took my advice and posted here.

Here are a few things I am noticing now:

1. tblJobs has an AddressID field but you also have a whole junction table called tblJobsAddresses. This doesn't make sense. Can a job have more than one address (if so how?). If not, you don't need the junction table

2. You have an AddressID field in tblParts. Since when do parts have addresses? tblJobParts will link your jobs and parts. Since the AddressID is in the Jobs table you do not need it here.

3. Since you have a junction table linking Jobs and Parts (tblJobParts) you should not have a PartID field in tblJobs. Keep the junction table if you can have many parts on one job (usually the case).

These are the kinds of things you need to think through. Make sure your structure makes sense before you move forward

 

[plog]

I'm not nudging, I'm shoving: Normalize your data properly. Your relationships are not correct. That is the right direction.

You are not halfway there. You've probably accomplished half of what you want, but what you think is the last half is going to be 10 times as difficult to achieve (especially with your structure) than the first part.

I can hit a golf ball 250 yards, that doesn't mean I score a 2 on every 500 yard hole. Usually its around 6 because the closer I get to the hole, the harder each shot is. And if my drive is 250 yards in the wrong direction, in a pond or out of bounds, I'm screwed from the beginning.

 

[Sarah Byrne]

Starting again or even going backwards just sounds like a headache but I see where you are both going. Thank you again for your help and im sure you will see me again next week with hopefully a better structure

 

[Sarah Byrne]

I'm not nudging, I'm shoving: Normalize your data properly. Your relationships are not correct. That is the right direction.

You are not halfway there. You've probably accomplished half of what you want, but what you think is the last half is going to be 10 times as difficult to achieve (especially with your structure) than the first part.

I can hit a golf ball 250 yards, that doesn't mean I score a 2 on every 500 yard hole. Usually its around 6 because the closer I get to the hole, the harder each shot is. And if my drive is 250 yards in the wrong direction, in a pond or out of bounds, I'm screwed from the beginning. From where you are now to where you want to be, you are in over your head. I would seriously consider hiring someone for this project.

Access can do all that you want, but I think creating the system you want would take you 6 months of full-time work. If you want to try it, I suggest you read up on normalization: http://en.wikipedia.org/wiki/Database_normalization. Work through a few tutorials, then chop your ambitious goal of an all encompassing system into a few manageable parts and work on getting those parts to work one at a time.

Less then 24 hours later with a couple of hours work and I have made my database work 6 months squished just a bit and no need to hire a single sole Thanks for all your help.