New to Access

[alukacs]

Hello, I am new to Access and am setting up a "simple" database for my friends Gutter Business. I have been reading the Access 2010 for Dummies book for 2 days now. What I would like to do is set up a table for Customer Information and then a second table that would be job specific. That does not seem too difficult, however I am not sure of the logic for setting up the DB so that we can go back to a specific customer, say a year later and add another record, "a second job for the same customer". Your thoughts on this would be greatly appreciated.



Thanks,
Alex II

[jzwp11]

Welcome to the forum!

What you are basically saying is that a customer can have many jobs (over time). This describes a one-to-many relationship, so you need the two tables you describe (one for customers and one for jobs) but you have to join them

tblCustomers
-pkCustomerID primary key, autonumber
-txtPrimaryName (individual's last name or company name)
-txtSecondaryName (individual's first name)
-txtAddress
etc.

tblJobs
-pkJobID primary key, autonumber
-fkCustomerID foreign key that relates the job back to the customer (this field has to be a long integer number datatype)
-dteJob (date of the job)
etc.

You will have to create forms for data entry since the users should not enter data directly into the tables.

For the setup above, you would have a main form based on the customer table and a subform based on the jobs table. I generally set the subform to show in datasheet view so that I can see all related records at once.

Each customer would be a record that you would see in the main form and all of their jobs would show in the subform.

[orange]

Its sounds like you may need 3 tables.

Customer -->CustomerJob [<---Jobs may not be required ]

[alukacs]

JZ.... thanks for the quick reply. I certainly can move along on this project with your advice. I guess the first one is always the hardest. I have been looking at a lot of samples, and yes I did intend on creating Forms for data entry.

Thanks again...
Alex II

[jzwp11]

Per Orange's suggestion about a third table. That would be true if you perform the exact same job for more than one customer or if you perform the same job many times for 1 customer. Can that happen in your case? Is each job unique?

[alukacs]

Hi....

So far I have created my 2 tables.

Table 1 is my Customer Information table and Table 2 is my Job Specific table. I have also created 2 forms. Form 1 is for entering all the Customer Information and Form 2 is my Job Specific information. I did attempt to set up a relationship between them, however I must have something wrong. As far as testing now, after entering information for Customer Id1, how do I get the application to open the form for customer id 1, job specific 1 for this customer?? I read something about "linked form". Is this what I missed? Also, for testing, is there a way to reset the data? I am seeing customer id 1 but job specific id 5???

I am hoping to be able to:

1. Enter a new customer
2. Enter a new job for that particular customer
3. Go back later and enter the job for a customer from 1
4. Go back later and enter a new job for any existing customer

Thanks, Alex.. I am enjoying the struggle!!!

[jzwp11]

I did attempt to set up a relationship between them, however I must have something wrong

It is critical to have the relationships set up correctly before doing your forms.

Did Access give you an error?


Also, you never answered these questions which will determine if you need a third table:

That would be true if you perform the exact same job for more than one customer or if you perform the same job many times for 1 customer. Can that happen in your case? Is each job unique?

[alukacs]

JZ, sorry I did not fully see the question. The Job Specific table has check boxes for say.... Full Install, Partial Install, Repair or Cleaning. I did not recall any errors when setting up the relationship. There was the line between the two fields. Are you saying that you must do the relationship(s) before even starting the forms?

Thanks, Alex

[jzwp11]

It is best to set up the relationships first, then Access will automatically link a main form to any subforms which would be the best approach for what you are doing. You would base the main form on the customer table. You would then create another form based on the job table. You would then drag & drop the job form into the customer form. Access will automatically link the two so when you add a new record to the main form and then move to the subform the data will be linked.

But before you do that, can you post the field names in both of your tables?

Also, regarding the check boxes, it would be better and more versatile if you put those choices ( Full Install, Partial Install, Repair or Cleaning) as records in a table and then relate them back to the job. Can more than one of these choices apply to a job (i.e. one-to-many relationship)?

[alukacs]

JZ, I have attached a screen capture of the fields from the relationship app. The customer is not looking for anything fancy. I can request that they just make a new record for each job. i.e., Customer1 Cleaning, another for Customer1 Repair........ not a combination, if that is your question.

Thanks again for all your help.

Alex II

[alukacs]

JZ, also have question? Do I have to disgard the Forms I already created and start with ones. That is not a problem because I am getting used to working with them.

Alex

[jzwp11]

Looking at the relationship diagram you posted, I think that it would be best to start fresh.

First the customer table should hold only information about the customer (nothing about the job should be in the customer table)

Also, it is generally recommended to not have spaces or special characters in your table or field names.

So the customer table:

tblCustomers
-pkCustomerID primary key, autonumber
-txtFirstName
-txtLastName
-txtAddress
-txtCity
-txtZip
-txtState (you could also have a table that holds all states and reference the corresponding state via a foreign key here)
-txtCounty (you could do the same here as with the state)
-Notes (pertaining to the customer not the job)

Since a customer has many contact methods (business phone, home phone, mobile phone e-mail etc.) that describes a one-to-many relationship, so those numbers/e-mail address should be in a separate but related table

First a table to hold the various contact method types. One record for each type (home phone, fax, mobile phone)

tblContactTypes
-pkContactTypeID primary key, autonumber
-txtContactType

Now relate the multiple ways of contacting the customer

tblCustomerContactInfo
-pkCustomerContactID primary key, autonumber
-fkCustomerID foreign key to tblCustomers (must be a long integer number datatype field)
-fkContactTypeID foreign key to tblContactTypes (must be a long integer number datatype)
-txtContact (field to hold the actual phone# or e-mail address)

Now since a customer can have many jobs, we need a table to hold those jobs

tblCustomerJobs
-pkCustomerJobID primary key, autonumber
-fkCustomerID foreign key to tblCustomers (long integer number datatype)
-txtJobName
-fkJobTypeID foreign key to tblJobTypes
-Quote (?)
-FinalCost (?)
-Tax (?)
-Deposit (?)
About the (?), I would need to know how you determine these values. BTW what type of data is in the quote field?

tblJobTypes (2 records at present Residential, Commercial)
-pkJobTypeID primary key, autonumber
-txtJobType


Now as to the tasks associated with the job, I assume those are the fields you call full install, partial install, repair, cleaning. I assume that a job may consist of more than one of these tasks (one-to-many relationship). I will also assume that a particular task may appear on many jobs (another one-to-many relationship).

First a table to hold all of the tasks

tblTasks
-pkTaskId
-txtTaskName

Now relate the particular tasks to a job

tblJobTasks
-pkJobTaskID primary key, autonumber
-fkCustomerJobID foreign key to tblCustomerJobs
-fkTaskID foreign key to tblTasks


I'm not sure what the early, fall, and late fields are in your job specific table. Could you please explain further?

[alukacs]

JZ.... Wow, I did not think that this was going to get this complicated. For the States, it will be mostly NJ and maybe PA. The early, fall and late fields are just simple notes as to when the gutter cleaning was done. As stated before, I would like to keep this on the simplier side. They told me that it didn't have to get too fancy. The quote is just a price quote for the job.

Thanks,
Alex

[alukacs]

JZ, the costs are just $$ amounts that they would be entering in. No calculations.

[jzwp11]

The early, fall and late fields are just simple notes as to when the gutter cleaning was done

If you have the date completed, I don't think you really need the early/fall/late fields which will simplify things a little.

For the States, it will be mostly NJ and maybe PA

Having the states in a table will allow the user to use a drop down box. This saves on typing but also eliminates typographical errors which could impact results when conducting searches. The table for states would be essentially stagnant once all were entered, so the user would not need to do anything with that table.

You show a deposit field, what about the rest of the amount? Do they want to track payments?


I have found that once you create a good database, people generally want to know what else it can do for them. I would guess that you will be asked to add more functionality in the future, so it is best to set up a good sound structure that offers some flexibility.

You might also add in the capability to capture events related to the job rather than just started and completed events. Just my 2 cents.