Using 2 fields as primary key (1 Viewer)

[Jarichardusa]

So in my tblCustomers there are two fields. AddressLine1 and AddressLine2. There are more fields than just those two, such as CustomerID, which is curently the Primary key, but I think those two are the relevant ones to this quesiton. I'm unsure as to how to set this up so that when it comes to creating a form to input this information somebody doesn't go to create a new customer with the same address. Its possible any number of combinations could contain repeat values. There could be many "Unit 2" or many "55 hill street" if its an apartment building, but a combination of the two would be impossible. Orders are tracked by address not person, so if the person in that address moves and another moves in, it doesn't matter. Names of individuals are not maintained. Is this something that gets addressed at the table level, or in the form with some sort of check to make sure the same address isn't already in the table with a message or something if it is? Can you do predictive text on a textbox? So that if you are entering in an address you can see as you are typing if that address exists already and then some sort of attached button to automatically bring you to the order page for that customer?

I am very new to access. The basic creation of tables and forms and such I have down, but the vba side and intricate things not so much. Thank you for your assistance.

 

[theDBguy]

Hi. Typically, you would use a combobox to make sure only valid entries are entered. However, for addresses, you might go with code in the control's BeforeUpdate event to check for a duplicate record. If we're talking about a combination of both Address1 and Address2 in two separate controls, you may have to check each one separately (but using both during the check). And yes, you can also use the control's Change event to dynamically check for duplicates as the person enter each character into the box.

 

[Dreamweaver]

You could just set the AddressLine1 and 2 as indexed no duplicates I nomally put an address in one field I.E. House & Road

 

[Jarichardusa]

You could just set the AddressLine1 and 2 as indexed no duplicates I nomally put an address in one field I.E. House & Road

If I say no duplicates, how would that work as there IS the potential for duplicates. More than one address could have the same AddressLine1 or AddressLine2. It is only a combination of the two which makes it unique.

 

[The_Doc_Man]

Other than you calling it something that it shouldn't be called, there is a way to do this. You can surely make a two-field text index and set the index to "NoDups" easily. There is a limit of 255 bytes on the length of the combined fields in an index. So as long as your address fields total less than 255 bytes, you can do this.

There are perhaps some other issues, though, because you might end up with duplicates inadvertently if someone enters an address as "Main Street" and someone else enters the same address but uses "Main St." They are the same address but Access wouldn't catch that case, so this might not be the best choice anyway.

I infer from your question that you are relatively new to Access, so I will explain why I commented on your choice of words. Specifically in Access, a KEY is something you would use to link two tables together if they have a relationship. For reasons of practicality, a mixed-format pair of address fields is a poor KEY but can still be a good INDEX. There are a couple of reasons why.

Keys usually must be unique. To establish a relationship between two tables, the key on the "ONE" side of a ONE-TO-MANY relationship MUST be unique. The key chosen for this relationship is called the PRIME KEY on the ONE side. The corresponding key on the MANY side is a FOREIGN KEY. In our articles, you will often see PK and FK for these fields.

Almost always, if you have a naturally occurring unique field, it can be a candidate to become the PK of a table. If you have two CHOICES for PK, ie. two "candidate keys" and both are unique, the shorter one is preferred because Access keeps a separate list of key values to speed up searches. The shorter the key, the faster the search. If among your choices, some candidate keys might be edited and some might not, the ones least likely to change are preferred. This is because editing a key means you have to edit the index for that key, and that is an expensive operation. Sometimes the best possible key is synthetic, such as an autonumber integer. This is because of the combination of size and editing issues.

 

[The_Doc_Man]

In your follow-up question, you asked how it would work. When you declare a two-field index, the combination of the two fields is the thing that cannot be duplicated. You are quite correct that the individual fields COULD be duplicated. But if you make the two-field index then the combination is what is being tracked.

 

[Jarichardusa]

In your follow-up question, you asked how it would work. When you declare a two-field index, the combination of the two fields is the thing that cannot be duplicated. You are quite correct that the individual fields COULD be duplicated. But if you make the two-field index then the combination is what is being tracked.

Okay, thank you. This is used in a one to many relationship, but I'm using the CustomerID as the primary key for that relationship. I googled how to set up the multiple field index and was able to accomplish that, at the same time I will try to lookup how to set up the onchange event as suggested by thedbguy for during entry checking just so somebody doesn't have to go through typing out an entire address and trying to save the record before before being informed that it can not be saved for some obscure reason they won't understand.

 

[theDBguy]

Can you do predictive text on a textbox? So that if you are entering in an address you can see as you are typing if that address exists already and then some sort of attached button to automatically bring you to the order page for that customer?

And with regards to this question, take a look at these two resources on SoundEx and Levenshtein Distance.

Microsoft Access tips: Fuzzy matches - Soundex

VBA code to locate names that sound alike, inexact matches in queries in an Access database

allenbrowne.com

UtterAccess.com

www.utteraccess.com

 

[Gasman]

Plus MajP supplied a nifty way of searching from textbox, combo and listbox.

https://www.access-programmers.co.u...t-filter-combobox-itself.311342/#post-1691895

 

[Pat Hartman]

To make a multi-field index, you MUST use the indexes dialog. the options on the field in the table design limit you to a one-field index. Access supports indexes with up to 10 columns. Do NOT mush fields to create a single field to hold the multiple values. Do this the correct way.