Newbie needing Help

[MikeyBoi]

Ok guys, im going to be flat out honest, Im new to this whole database thing. I've been doing a lot of studying and Decided that I am going to try my hands at a real simple and straight foward database. It seemed fairly simple but i guess not so im asking for help. I think my problem lies in understanding how the keys work, and what exactly they do for the database itself. And two the relationships, im not really sure of, i understand how to set them, but again i dont know what they do for the database in the end. Ok so heres what i got:

The object of this DB was to collect, store, retrieve, edit and display data for a fictious Print center that deals with Black & White copies and Color Copies.

3 tables:

Company
Customer
Job

these tables contain:

Company Table:
CompanyID Set to AutoNumber *SET AND PRIMARY KEY*
CompanyName Set to Text
Address Set to text
City Set to text
State Set to text with 2 Character limit for input
ZipCode Set to text with 5 characrtr input
Description Set to Memo
Webaddress Set to hyperlink

Customer Table:
CustomerAccountNumber Set to autonumber *SET AND PRIMARY KEY*
CompanyName Set to Text
JobNumber Set to number
FirstName set to text
LastName Set to text
PhoneNumber Set to text with mask set
FaxNumber Set to Text with mask set
Email adddress Set to text

Job Table:
Jobnumber Set to autonumber *SET AND PRIMARY KEY*
Jobname set to text
Jobtype Set to text
JobDescription Set to memo
DueDate Set to Date and time

Ok now hers the relationships I set up:

Company Table: CompanyID ------> Customer table:CompanyName

Customertable:JobNumber ------> JobTable:JobNumber

I tried and thought this would keep things simple but for some reason when i go to create my querys, i get this thing telling me that i have other data from another tabel and cant be edited, i figured out that i had to put the job in first to get a job number and when i go to enter the customers information i have to manually enter the job number in. (Kinda Sucks) I entered some sample data, and tried to make a form, I used the autoform and created a general form but when I flip to the last record in the DataBase it wont let me enter any new information, I thought i was suppose to do that, so i went to the table properties and set the table to Data Enrty Only, and i still couldnt enter new data.

Ok so theres my problem, I made a boat with duct tape and now im wondering why im sinking to the bottom. Any help would be GREATLY APPRECIATED and if possible a broken down real world explanation on how the keys and relationships work, and what they actually do for the database. I also probably didnt set up the data in the fields correctly, I was trying to normalize everything but im not sure.

Thanks guys! Im really greatful for any help you can offer! ~[MikE]~

 

[Rai]

Hey could you email me the DB?
Ill take a look. PM me if you would like that,
Thanks
RAI

 

[Rai]

Ok, you created the form from a query. Now im not too sure you can enter data into a query.
I would suggest re thinking your design.

 

[ColinEssex]

You're wrong on several counts.

Firstly I presume that a Company can have many customers (within it) - or why else have you a Customer table?

The Customer AccountNo should not be an Autonumber. You should self generate these. The AutoNumber is purely a linking field between tables. You also need a CustomerID (AutoNumber)


You do not need JobNumber in the Customer table because that means you will be repeating all the Customer details for each job. A Customer should appear only once in the table.

In the Customer Table there needs to be a CompanyID field to link the Customer to the (parent) company. Your link will not work, its linking an AutoNumber to Text.

In the Job table you need

JobID (AutoNumber)
Jobnumber (as Number or Txt & Number)
CustomerID (as a Number)
DateReceived (as a Date)
Jobname set to text
Jobtype Set to text
JobDescription Set to memo
DueDate Set to Date and time

JobType could be a ComboBox presumably. Is the job description very long? (i.e. over 250 characters) if not use a text box not a memo

I would have it so I selected the Company first - then Cascaded to the Customer within that Company - then do the job details (there may be more than one job requested on that order) I would maybe use a Sub-Form for Job details.

I confess I only looked quickly through this, there may be more little things I've missed on first glance

The relationships will be

A Company can have Many Customers

A Customer will have Many Jobs

Col

 

[Kelemit]

Company Table:
CompanyID Set to AutoNumber *SET AND PRIMARY KEY*
CompanyName Set to Text
Address Set to text
City Set to text
State Set to text with 2 Character limit for input
ZipCode Set to text with 5 characrtr input
Description Set to Memo
Webaddress Set to hyperlink

Customer Table:
CustomerAccountNumber Set to autonumber *SET AND PRIMARY KEY*
CompanyName Set to Text
JobNumber Set to number
FirstName set to text
LastName Set to text
PhoneNumber Set to text with mask set
FaxNumber Set to Text with mask set
Email adddress Set to text

Job Table:
Jobnumber Set to autonumber *SET AND PRIMARY KEY*
Jobname set to text
Jobtype Set to text
JobDescription Set to memo
DueDate Set to Date and time

Ok now hers the relationships I set up:

Company Table: CompanyID ------> Customer table:CompanyName

Customertable:JobNumber ------> JobTable:JobNumber

Not bad for a start... but, in some ways.. all wrong .

In order to link relationally, the items have to be SET THE SAME!.. so a text -> autonumber or vice versa will not work, (as was stated above).

So while you have right idea Company -> customer etc.. you are linking it wrong.

As for the customer table -> job table.. you have the link backwards.

In the Company table, make another FIeld name Company Account #, and DON"T make this a Autonumber or primary key. This is something that is Chosen and can be edited by your printer business as they need / want to. They will probably want to choose a number that is someone meaningful to them, so it shouldn't be autonumber, and if, someday in the future ( And yes.. this can happen.. has happned to me.. SIGH) they want to change the number, then it CAN"T be the primary key.

In the Customer table, add another field named CompanyID and set it as a long number (BUT NOT autonumber) and NOT PRIMARY Key. Then add another field called CustomerID and make it autonumber / Primary key. KEEP customer Account Number but change it to regular number / text and allow the user to do as they want with it. REMOVE JOB number from this table.

In the Jobs table, add a field Called CustomerID and make it a long number, but NOT AUTONUMBER NOR Primary Key.


Now, in the relationship window, bring all 3 of those tables up. Link CompanyID in the Company Table to CompanyID in the Customer Table. CompanyID in Company table SHOULD be bold because it is the Primary Key, but SHOULD NOT be bold in Customre Table because IT IS NOT the primary key for that table.

In the window that comes up, enforce referential integrity and cascade delete / update.

Then, from customer Table, linke CustomerID (which is Primary Key for this table) to job table CustomreID number (WHICH IS NOT Primary Key for Job Table). Again, enfore referential integrity and cascade update / delete.

What did this all do???

Basically, When you make a company, you can have multiple customers under that company (I'm assuming thats what you want, because this is the way I just set this up.)

IE Joe Blow from company ABC came in. He is new and so is his company. You type in all ABC's information in the Company table. When you go to add Joe Blow, it will ask which company, and then He will be linked to ABC Company by the CompanyID number created by Access.

Joe Blow askes for a new job, that Job will be created under Joe Blow's Customer ID, also created by Access as an autonumber.

So if you want to know who Job # 123 belongs to, you look for Customer # 123 on the Customer Table. And Customer 123 is Joe blow. he belongs to company 24. Look up 24 in CompanyID on the company Table and you find ABC.

If Suzy Sunner comes in from Company ABC, you already have ABC in your company table, so when she comes in, you just say she is form Company ABC and Access puts in 24 in the COmpany ID field of the Customer table.

SO now, in the customer table, you have TWO 24's. Why? You have TWO people from ABC, but ONLY ONE company ABC, and if you look in the Company table, you SHOULD only find ONE ABC company with Primary Key Autonumber CompanyID field value of 24.

So... how do you make it useful / easy for a user.

Simple...

Create 3 forms.

Form 1. Just use a quick Autowizard from link it to Jobs. Choose Tabular As the layout for this form. Name it Jobs Listing subform, or some such.

Form2. Again, Autowizard, and link it to Customer, Choose Columnar or Justfied (probably best idea for this form). And Name it Customer pick subform.

Form3. Again, Autowizard and this time link to Company. Choose again Justfied, or Columnar, and save as Job Picker/Schedular/Maker or something (This will be the form that when a customer comes in and wants to order a new job, you will input all the data into that form)

Next, Open Form1 and edit it how you like. Make sure that "Display multiple records" is set. CLose form1, save changes of course

Now. Open form2 in design mode and edit it how you want to. Make it look pretty and tight, keep things nice yet compact. Now, move the form design window to the side so you can still see the database window. Now grab form1 (the jobs listing subform) and drag it onto Form 2 PREFERABLY below all the text boxes for Customer name / phone / contact etc. Close form2. Save changes of course

Now open form3 in design mode and edit as you like. Again, like form2, make sure its compact and well designed. Now move it to the side so you can see the database window again. Drage FORM2 (Make sure ITS FORM 2) into the form design window, again, making sure that it is preferable below all the text boxes for Company address / phone / website / etc.

Once you have that done.

Go ahead and save.

Open now, form3. If you have records in the list, you should now see companies. If that company has records for customers, you should ONLY see the current customers assigned to that company. And if there are jobs for those customers, you should see only the jobs ordered BY THAT person currently displayed.

You should also see ( unless you turned them off) 3 record navigation sets. One set for the main form to move through the list of companies, as you do this, the customers linked to those companies will shown and ALL OTHER customers NOT linked will be hidden / removed. Same with the jobs listed.

So, if Joe Blow comes in, lets say that you ALREADy have his company and customer info. And he has a job that just finished. You hit the Company navigation buttons to go over till you see ABC company. Then go to the Customer navigation buttons, and go over till you find JOe Blows name.

Now, in the jobs listing, all jobs that Joe Blow has ordered will be displayed.

So, Joe comes in to pick up his order AND TO MAKE a NEW one. So, you open up this form, navigate to his company, then when you get to ABC, navigate to his name.

DOwn in the jobs listing subform, all his jobs will come up. CLick on the one he is to pick up and mark it complete / done / or whatever, then in the blank like at the bottom of this listing, type in the new info / job / etc that Joe wants to do.

Viola! The old job was marked done, the new job was entered.. etc.

But what if New customer / company comes in.

Well... go to the company navigation buttons, hit the new record button ( or scroll ALL the way to the end, then click one more time ) and add in the new company.

Then add in the new customer, then add in the new job.

Or, what if its a new customer but under an old company (already inputed company). Well, navigate to the company you want to add the new customer to. Then go to the custmore navigation and click on the "new record" button (or manually click all the way to the end) and then type in the new customers name.

Again, bam.. new customer under old company. All the linkages will be done automatically...

You will see the CompanyID automatically applied to the new customer, and the CUSTOMERID automatically applied to a new job. I often Hide all primary keys AFTER i have verified they are working right. This saves ALOT of space, and keeps poeple from messing with it. No need to have people messing with primary keys / relationship based numbers.

There are, of course, many different ways to navigate through listings of people.. but that is much later.

Just get a hold / hang of relationships first...

hope this helps

Kelemit

 

[MikeyBoi]

Hey thanks Guys

Hey just wanted to say thanks guys, i've been away for a few days and havent gotten back to the comp till now. Thanks for all the tips, ill try again and see what happens! ~[MikE]~