correct structure

[buratti]

This one is going to be confusing to explain, but in reality is simpler. I am wondering the correct structure for certain tables regarding customers.

I'll start with the end result of what I need. I have a customers form with, amongst general customer data has a service days field and a driver field. I need the AfterUpdate of the address field to "lookup" what days that street is serviced and enter that day/value in the service day field, also do the same for the driver field.

My question would be in the way to structure the "lookup". Right now I have a single table named Addresses that lists: all the streets we service (Street field) the days they are serviced (Days field), and the driver who services that street (Driver field). I originally started with just a dlookup function to COPY the value from the Addresses table to the field in the customers table. (This function completed through the afterupdate property of the customers table though.) Technically speaking this works, but I just cant get the syntax right for the dlookup function:

Private Sub Address_AfterUpdate()
Dim ServiceDays As String
Dim CurrentStreet As String
[COLOR=seagreen]'Removes the house number of the address leaving just the street name[/COLOR]
CurrentStreet = Mid(Nz([Address]), InStr(Nz([Address]), " ") + 1)
ServiceDays = Nz(DLookup("days", "addresses", "street =  " & CurrentStreet), "")
Me.Service_Days = ServiceDays
End Sub

But then I remembered reading somewhere that if you have a table with the same value repeated many times through the table, it is good practice to split that table into two or more. This would be the case, since the Addressed table; Days field are only 1 of 3 choices (Mon - Thurs, Tues - Fri, or Wed - Sat) each being repeated several hundered times. And then that data wold be repeated AGAIN in the Customers table. (Same principal for the Driver field). So if this WOULD be the correct way to structure it, what new tables would I need, how would I link them together, and finally how would I get the form to "lookup" the correct data for the address of the customer? Or if it is just easier with the dlookup function above, can someone tell me why that is not working?

 

[The_Doc_Man]

You are right, it was very confusing to explain.

You should not have to do it so hard. JOIN queries can to the trick a lot better if you have a way to link them. This is why we talk about Primary Keys and Foreign Keys.

Unfortunately, your description leaves me unable to fathom some of the details needed to advise you directly.

My first advice is to use your favorite browset to look up "database normalization" as a topic. Access Help just calls this "normalization" but for a web search, include "database" since other topics also use "normalization." Read articles on the topic until you see three detailed articles in a row where you feel you didn't learn anything. When reading, concentrate on articles from universities, colleges, and schools in the .edu domain, plus articles by vendors whose names you recognize.

Next, the lookup can be managed by a JOIN of the primary table you want linked to the table with the data you were going to look up. You do that by having a primary key on each record of the table containing the lookup data and a foreign key equal to the value of the primary key of the correct record. Access can help you here, too.

There is also nothing wrong with searching this forum for key words and phrases such as "JOIN" "normalization" "designing tables" etc etc. The Search function works OK. Not only that, you can do a search through a site such as (but not limited to) Google, which picks up topics in this forum.

Wish I could help you more, but I'm too vague on your problem. If I may offer one last thought, your description got into the weeds so fast that I never had a chance to admire the scenery.

 

[buratti]

Lets move on to my end result question in a minute and work on the normalization right now. I, "for the most part" understand normalization, but would have a few questions in my situation. I'll tell you what I have now and what I plan on changing it to and maybe you can assist me along the way if I am doing something wrong or I get stuck. My questions and uncertainties are marked in red.

What I have now (un-normalized):
Customers table - Fields:
Customer ID
Name
Address
City
St
Zip
Service Date 'right now is just copied/repeated data (not linked to anything)
Driver 'right now is just copied/repeated data (not linked to anything)
(and other fields data not revelant to this topic)


Addresses table - Fields: 'This table would need to be split?
Address Id
Street Name 'No repeating entries
Service Day 'Many repeated entries, one of 3 choices
Driver 'Many repeated entries, one of 3 choices

What my "normalized" DB should be???:
Service Days table - Fields: 'entire table would only have 3 records in it
ServiceDay ID
Service Day


Drivers table - Fields: 'also this entire table would have only 4 records(only 4 drivers)
Driver ID
Driver Name


Addresses table - Fields:
Address ID
Street Name
ServiceDay ID?? 'this field would have a relationship to the same named field in the Service Days table???
Driver ID?? 'this field would have a relationship to the same named field in the Drivers table???


Customer table - Fields:
Customer ID
Name
Address
City
St
Zip
ServiceDay ID ??? 'would this be related to the same named field in the Addresses table or the Service Days table???
Driver ID ??? 'Same question as above???
(and other fields data not revelant to this topic)


Forgetting about how to lookup the data later on in my form, would this be the correct normalization for these new tables and relationships? If not, where did I go wrong?