Breaking Table Norms

[blyxx86]

Does this break the normalization of tables??

I have the following tables:

tblLocation
LocationID
LocationCode
AttnTo
Address1
Address2
Address3
City
State
Zip
Phone

tblAcq
AcqID
TrackingNumber
PONumber
AcqDate
LocationID
LocationCode
AttnTo
Address1
Address2
Address3
City
State
Zip
Phone
etc..

tblInvoice
InvoiceID
TrackingNumber
SONumber
InvoiceDate
LocationID
LocationCode
AttnTo
Address1
Address2
Address3
City
State
Zip
Phone
etc..

Now, I have these tables and when the user enters a valid LocationCode it populates the rest of the fields with the information that is available in tblLocations. I wanted to do this so that the user could change the Address/AttnTo if they needed, but does it break the normalization of my tables?

I am also going to lock the information in these fields once a TrackingNumber has been put in so that the information cannot be changed accidentally (or intentionally). I know I can do this with an OnCurrent event and that is not my issue.

Any thoughts on this?

Thanks!!

This is the code that I use to update the fields. I'm still working on the whole "Me.Undo" part, because I really only want to undo the change made to the LocationCode field, but when I try Me.LocationCode.Undo (which it allows) it doesn't perform any action.

Private Sub LocationCode_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim strSQL As String            'SQL Clause
    Dim intStoreNum As Integer
    Dim strLocCode, strLocName, strAttn, strAdd1, strAdd2, StrAdd3, strCity, strState, strZip, strPhone, strFax As String   'Address Clause
    Set db = CurrentDb
 
    strSQL = "SELECT tblLocations.CustomerID, tblLocations.LocationCode, tblLocations.StoreNumber, tblLocations.LocationName, " & _
            "tblLocations.AttentionTo, tblLocations.Address1, tblLocations.Address2, tblLocations.Address3, tblLocations.City, " & _
            "tblLocations.State, tblLocations.Zip, tblLocations.Phone, tblLocations.Fax " & _
            "FROM tblLocations " & _
            "WHERE (((tblLocations.CustomerID)=" & Me.CustomerID & ") AND ((tblLocations.LocationCode) = " & """" & Me.LocationCode & """" & "));"
 
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        With rs
            If .RecordCount = 1 Then
 
            'Store the values in the query to memory.
                strLocCode = "" & !LocationCode & ""
                intStoreNum = !StoreNumber
                strLocName = "" & !LocationName & ""
                strAttn = "" & !AttentionTo & ""
                strAdd1 = "" & !Address1 & ""
                strAdd2 = "" & !Address2 & ""
                StrAdd3 = "" & !Address3 & ""
                strCity = "" & !City & ""
                strState = "" & !State & ""
                strZip = "" & !Zip & ""
                strPhone = "" & !Phone & ""
                strFax = "" & !Fax & ""
 
            'Set the text boxes to the values found in the query.
                StoreNumber = intStoreNum
                LocationName = strLocName
                AttentionTo = strAttn
                Address1 = strAdd1
                Address2 = strAdd2
                Address3 = StrAdd3
                City = strCity
                State = strState
                Zip = strZip
                Phone = strPhone
                Fax = strFax
 
            Else 'What happens if a record cannot be found
                MsgBox "The previous address was saved since no record could be found for:" & vbCrLf & LocationCode, vbOKOnly + vbInformation
                Me.Undo
            End If
        End With
 
        rs.Close 'Close the recordset
End Sub
 

[mlcampeau]

deleted post and reposted in post #3 as it did not show up the first time

[mlcampeau]

In tblAcq and tblInvoice you should only have LocationID to link each AcqId and InvoiceID to the Location information. It breaks normalization to store the same information in more than one place. Check out this article:
http://www.thescripts.com/forum/thread585228.htm

[blyxx86]

That makes sense now that I've been thinking about it.

Would storing different AttnTo values be breaking normalization? Our database at my work is a mess of a database and I'm trying to fix the mistakes being made. This one became so common I questioned if it was correct or not. Phew! Glad I came out of this one.

It seems the people who created the original database didn't understand any type of RDBMS, but they created 700 tables somehow!

I will definitely just use the LocationID (as you can see it was being used, but I was just trying to store the information somewhere in case changes needed to be made.) However, changes should be made at the tblLocation part.

Okay then, how would you handle this...

A request is made to ship to a certain store, let's call it Acme #105.
A shipment is made against that request, but it's shipped to Acme #501 due to a human error, but the information entered into the database is Acme #105. What would be the best way (besides removing humans from the scenario) to accurately track this type of error. Any input on that?

[mlcampeau]

That makes sense now that I've been thinking about it.

Would storing different AttnTo values be breaking normalization? Our database at my work is a mess of a database and I'm trying to fix the mistakes being made. This one became so common I questioned if it was correct or not. Phew! Glad I came out of this one.

It seems the people who created the original database didn't understand any type of RDBMS, but they created 700 tables somehow!

I will definitely just use the LocationID (as you can see it was being used, but I was just trying to store the information somewhere in case changes needed to be made.) However, changes should be made at the tblLocation part.

Okay then, how would you handle this...

A request is made to ship to a certain store, let's call it Acme #105.
A shipment is made against that request, but it's shipped to Acme #501 due to a human error, but the information entered into the database is Acme #105. What would be the best way (besides removing humans from the scenario) to accurately track this type of error. Any input on that?

I think it would be fine to store different AttnTo's. Keep in mind that I'm fairly new to database design (despite my status being Expert) but I can't really think of another way to keep track of it. Unless you made another table but that seems redundent. I could be wrong on that though.
As for tracking human errors...it's exactly that, a human error, so I'm not sure how one would keep track of that in the system. Maybe when your shipment is made, have the shipper or the receiver on the other end fill out a "Shipped To" portion and then have that input into the system. I would assume the shipper or the recipient of the product would know they are at Acme #501 rather than Acme #105 so if that got marked down, it could get input, then you could run a query where the ShippedTo Location doesn't match the tblInvoice Location.

[blyxx86]

I can't think of another way to do it either.

I do know our current system at work (which is primarily used for Asset Management) doesn't do any sort of Asset Managing, hardly any inventory control and it requires manual inventory counts daily to accurately determine in-house equipment. So guess who's leading the way of the ideology that "Equipment In-House = Received - Shipped" haha.

I devised another way to go about the "human error." Instead of having the shipper enter the information by hand (thus transposing numbers) I am going to update the address books in the shipping stations so that it corresponds to the LocationCode (which is an Indexed field that allows no duplicates) and then on any sort of shipping request place a bar code that can be scanned into the shipping computer. Voila! If there are any errors at that point then it is checked against the request (done by data entry clerks) from the customer. Customer's make mistakes sometimes too.

I do like the idea of creating a query that the tblRequest.LocationID is not the same as the tblInvoice.LocationID. So that will help in the future.

Now I must devise a way to keep track of length of time spent on any particular case/request/phone call. Talk about a massive database lesson... I've only started using databases for little over 9 months and I'm taking on the task of creating a system for a corporation.

[mlcampeau]

I devised another way to go about the "human error." Instead of having the shipper enter the information by hand (thus transposing numbers) I am going to update the address books in the shipping stations so that it corresponds to the LocationCode (which is an Indexed field that allows no duplicates) and then on any sort of shipping request place a bar code that can be scanned into the shipping computer. Voila! If there are any errors at that point then it is checked against the request (done by data entry clerks) from the customer. Customer's make mistakes sometimes too.

That's a good idea!

Now I must devise a way to keep track of length of time spent on any particular case/request/phone call. Talk about a massive database lesson... I've only started using databases for little over 9 months and I'm taking on the task of creating a system for a corporation.

Good luck with the rest of your project. I'm new at working on databases too so I sympathize with you. If you have any more questions, there's lots of experts here who can help!

[blyxx86]

Thanks!

I've offered some of my own knowledge to some others here, just reading up on some random topics I know much about.

Hopefully it all goes well for you too!