Getting Started

NeoNemesis

Registered User.
Local time
Today, 11:22
Joined
Nov 18, 2002
Messages
19
I am creating a database in Access to store all of my customers that I do business with and also storing all the details of the visits that I make to them.

I am planning to add bits extra to it after the main bulk has been done.

Currently I have two tables as follows,

Customer Details:
  • CustomerID - Autonumber (Primary Key)
  • CustomerName
  • All the address fields
  • Phone
  • Fax

Visit Details:
  • VisitID - Autonumber (primary key)
  • CustomerID
  • CustomerName
  • VisitDate
  • VisitAttendees
  • VIsitAgenda
  • ActionItem1
  • ActionItem2
  • ActionItem3
  • ActionItem4

They are related by the two Customer ID fields.

The problem that i am having is that I want the name of the CustomerName to appear automatically in the VisitDetails Table when I enter the CustomerID but for some reason I just cant make it happen!

Any pointers please? I'm only a novice when it comes to Access so help from you guys may be an ongoing thing if that is ok :).

Thanks in advance.

NeoNemesis
Joe
 
There is a normalization problem here. You should not have customername in both tables. Delete customer name out of your visit details table. Having the customer id in the visits table will get you all the information you need about the customer.

On the form where you enter your visit details, make the customer field a combo box that lists your customers. The rowsource will be the customer detail table. Here are some of the properties for the combo box

Rowsource - customer details table
column count 2
column width 0;2.5 (the customer ID will not be displayed, only the name)
bound column 1

When you select the customer from your list, the customer id will be stored in the visit detail tables, customer id field, but it will display the customer name.
 
Cheers mate, will try it out in the morning!
 
To carryon with the normalization aspect, I would think that you should have a table for your action items. My understanding is that having anything1, anythig2, anything3, etc is not efficient. What if there was only one item to action...then you have 3 other cells of wasted space. The oposite is of course, what if there are 10 things to action from a visit?

I would recommend that you create an other table for your action items and relate it to your VisitId. This way you could check the Action Items from a particular visit verses all the action items from that customer.

Navyguy
 
If the Customer is a person then split the field into forename and surname.
 
VisitAttendees gives the impression you are intending to store non-atomic data in a cell.

This should be many-to-many relationship - you'll need a junction table.
 

Users who are viewing this thread

Back
Top Bottom