Multiple Indexed Fields to prevent Duplication

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Everyone,

I have created a form with a subform. The main form has customers with
address infomation & the subform has orders & dates of orders. I will from
time to time have the same customer name & therefore have created a mulitple
index for the fields that contain the address - Street number, Street Name &
Street Type(which is a drop down list) in an attempt to avoid duplication.
This seems to be working great. The only problem I have is there is constant
data entry into the database. Is there any way to go directly to the
existing form when the error comes up that there is a duplicate value?

Thank-you in advance for any assistance you may be able to provide.
 
Your table structure does not sound right. There's no way that you
should need a unique index on a street address.

This is what you would normally have for a customer orders database.

(1) A Customers table with a single record for each customer,
regardless of the number of orders that the customer has:

tblCustomer
CustID (Primary Key)
Forname
Surname
AdrsLine1
(etc.)

(2) An Order Headers table giving the data for each order that is
common to the order as a whole, regardless of the number of items
ordered:

tblOrderHeader
OrderID (PK)
OrderDate
CustID <- the customer that this order is for.
OrderTakenBy, whatever.

(3) An Order Detail table containing one record for each item ordered:

tblOrderDetail
OrderID } composite
LineNo } primary key
details of the ordered item, eg. ProductID

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
Thanks "TC" for all your information. I know it sounds strange but the
database I have created is really more structured to ensure no duplications
of addresses. I wish I could give a more detailed description of the
relevance for this but you would really have to see it to believe it. I
appreciate the time you spent giving me the information you supplied. I am
sorry I wasn't more specific.
 
Back
Top