entries are automatically duplicating

[Bud G]

I have set up a database with a members table and a
payments table. I need to be able to apply payments to
an individual member... all goes well until members have
the same last name, then the payment posts automatically
to both members.

The tables are linked by the namelast data field.

I have tried setting up a combo box which combines
namelast
namefirst
parentname

Thought this would differentiate between last names by
including first names but it doesn't.

Any thoughts or suggestions greatly appreciated!

 

[Rick B]

sounds like you need to redo your links (relationships). The two tables
must be combined based on a unique field. Obviously, last name is not
unique. You need to use a customer number, or the combined Last and First
names. I'd recommend customer number.

Rick B


I have set up a database with a members table and a
payments table. I need to be able to apply payments to
an individual member... all goes well until members have
the same last name, then the payment posts automatically
to both members.

The tables are linked by the namelast data field.

I have tried setting up a combo box which combines
namelast
namefirst
parentname

Thought this would differentiate between last names by
including first names but it doesn't.

Any thoughts or suggestions greatly appreciated!

 

[John Vinson]

I have set up a database with a members table and a
payments table. I need to be able to apply payments to
an individual member... all goes well until members have
the same last name, then the payment posts automatically
to both members.

The tables are linked by the namelast data field.

I have tried setting up a combo box which combines
namelast
namefirst
parentname

Names are NOT UNIQUE. I once worked with Dr. Lawrence David Wise and
his colleague, Dr. Lawrence David Wise. They do *not* make good keys!

In addition, people change their names (on marriage or for other
reasons). This can be handled with cascading updates, but this makes
for added complications (e.g. prior payments made under one name will
show up under the new name).

I would very strongly suggest using a "surrogate key" - an Autonumber,
a manually assigned number, or a programmatically generated one - as
the primary key of the members table; the name should be stored *only*
in the members table, and only a foreign key (Long Integer if you use
an autonumber) in the payments table.

 

[Bud G]

Would it work to have customer number in the members
table as an auto-numbered field, and have a field with
the same name in the payments table be a look-up field?

Otherwise I am at a bit of a loss how I would know who I
was doing the payment entry for...

Thanks,

Bud G

-----Original Message-----
sounds like you need to redo your links

(relationships). The two tables