Need Info

[marbet]

I am trying to set up a databse for our home based business. My concerns is
when I create the customer table with all the information that I need (eg.
first name,last address and so on) how MS Access recognize two customers with
the same last name-I might not have the first name of these two customers let
say with the same last name "Smith".
I hope I make myself understandable.
Please get back to me on this

 

[Gina Whipp]

Marbet,

Customers would be defined by the Primary Key not by their names. Based on
your question I have some suggested reading...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[KenSheridan via AccessMonster.com]

The easy way is to include an autonumber CustomerID column as the table's
primary key. This assigns a unique arbitrary number to each customer. In
other tables which relate to Customers, e.g. Orders you then include a
numeric CustomerID column as a foreign key, but in this case not an
autonumber. You don't need any other columns in Orders to identify the
customer as each CustomerID value uniquely does so and you can simply join
the tables in a query to return columns from both tables.

This does not mean that you have to remember the CustomerID value for each
customer when entering an Orders record; you can simply select the customer
from a combo box's list (BTW do not under any circumstances be tempted to use
the 'lookup wizard' data type in table design view of Orders to add the
CustomerID column). You would do this in an Orders form which includes a
combo box bound to the CustomerID column, but shows the customers by name.

So, you are now thinking that this takes you no further forward as you won't
be able to tell one John Smith from another in the list, so how do you select
the right one? The answer is that you include other columns in the list
which differentiate each customer, e.g. the first line of their address. To
do this the properties of the combo box on the Orders form would be set up as
follows:

Name: cboCustomer

ControlSource: CustomerID

RowSource: SELECT [CustomerID], ([FirstName] + " ") & [LastName],
[Address1] FROM [Customers] ORDER BY [LastName], [FirstName];

BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;3cm;3cm
ListWidth: 6cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column. Experiment with the other two dimensions to
get the best fit. The ListWidth is the sum of the ColumnWidths.

After selecting a customer from the list you'll see 'John Smith' in the
control, but not the address. You can show this in an unbound text box on
the form, however, by setting its ControlSource to:

=cboCustomer.Column(2)

The Column property is zero-based, so Column(2) is the third column, Address1.


Its very unlikely you'll have two customers with the same name and the same
first address line, but when identifying what combination of columns do
uniquely identify a record (what's known as a 'candidate key') it can be
necessary to think carefully. As an example I've often referred here to the
occasion when I was present at a clinic where two patients turned up on the
same day, both female, both with exactly the same names and both with the
same date of birth!

Ken Sheridan
Stafford, England