Reference table

[Paul]

Hi there.
I have a table with 2 colums, column 1 is Customer name
and 2 is partnumber. A costumer name may appear more then
once is this tabel so there is a possibility for
redundency error. I want to create a reference table with
customer names and link it to the data table, but I dont
want to change it manual where I read customer A that I
have to select from the pulldown Customer A.
Is there a way to make a search for customer and replace
this by a customer name BUT then from the reference tabel.

Regards,

Paul.

 

[Jeff Boyce]

Paul

I can't tell from your description, but it sounds like your data design
would benefit from a bit more normalization.

In the real world, people (customers) don't come with "partnumbers" (?is
this like a barcode tatooed across our foreheads?).

Access is a relational database -- consider reading up in Access HELP and at
the mvps.org website on the topic of normalization. While it may take a bit
longer to wrap your head around the concepts, and re-design your table
structure, you'll find it MUCH easier to use the full power of Access after
you've done so.

Or have I misunderstood...?

 

[Paul]

I think you misunderstood by my poor example.
The date is more for example purposes, I want to change
the existing date (which has no relational relation with
another table) with a SET command to make it Relational.

How must I Proceed

 

[John Vinson]

I think you misunderstood by my poor example.
The date is more for example purposes, I want to change
the existing date (which has no relational relation with
another table) with a SET command to make it Relational.

How must I Proceed

There is no "set" command to do this (or else I'm totally
misunderstanding your post).

Normally if one has a non-normalized table with repeating data, the
way to normalize it would be to create normalized tables (a Customer
table, a Parts table, and a Sales table say); you'ld then run Append
queries from the wide-flat table into each of the "one" side tables,
using the UNIQUE VALUES property on the queries to create only one
record for each customer or for each part, respectively. This process
may well entail adding Autonumber (or other) primary keys; if so,
you'll then need to add foreign key fields to the original table
(which will become the SALES table), and use Update queries to feed
the autonumber values back into the foreign keys.

If you mean that you might have "not quite duplicate" customer names
such as "Joe Smith" and "Jos. Smith" and "Joseph Smith" - you'll need
to resolve these manually, especially since you can't be sure whether
or not they are the same person or different people with the same (or
similar) names.

 

[Bas Cost Budde]

Hi there.
I have a table with 2 colums, column 1 is Customer name
and 2 is partnumber. A costumer name may appear more then
once is this tabel so there is a possibility for
redundency error. I want to create a reference table with
customer names and link it to the data table, but I dont
want to change it manual where I read customer A that I
have to select from the pulldown Customer A.
Is there a way to make a search for customer and replace
this by a customer name BUT then from the reference tabel.

You can have a query that does

select distinct customername from yourtable

which will give you the unique list of customers. But what about that
replace part you mention in the last sentence? What does that mean?