Populating a field in one table based on multiple fields in another (1 Viewer)

[dal1503]

Posting this in General as I wasn’t sure whether it best fit under Tables, Queries or Forms.

In table ‘People’, upon a user entering their house number and postcode (UK zipcode), I need the ‘Area’ field to be populated accordingly based on the addresses stored in the ‘FS_Area’ table.

What would be the best way for me to achieve this? If the area was based solely on postcode I think I could do this using some sort of lookup, but the problem is that one postcode can contain more than one Area so the house number needs to be taken into account. For example in postcode LA1 1AA, house number 30 might be in AreaOne and house number 31 might be in AreaTwo.

Another requirement I have is that if the user enters an address that does not exist in the ‘FS_Area’ table, I would want their record in the ‘People’ table to show “Not in Area” under the ‘Area’ field.

From the user’s perspective the information will be entered using the a Form interface.

Any help would be greatly appreciated, I’m still very much a newbie when it comes to Access developing so I’m learning as I go along.

If there’s any info I’ve missed or isn’t clear, please let me know and I can give more detail.

Thanks in advance.

 

[sneuberg]

Normally I'd suggest a subform but since “Not in Area” is a possibility I think you maybe stuck with a DLookUp and Nz to handle the null case. So you could try something like:

Sub GetArea()

Me.NameOfAreaTextBox = Nz(DLookup("[Area]", "[FS_Area’ table]", "[AddresFieldName] = Forms!NameOfTheForm!NameOfAddressTextBox And [PostCodeFieldName] = Forms!NameOfTheForm!NameOfPostCodeTextBox"), "Not in Area")

End Sub

You would call this subroutine in the afterupdate events of the address and postcode textboxes.

 

[dal1503]

Normally I'd suggest a subform but since “Not in Area” is a possibility I think you maybe stuck with a DLookUp and Nz to handle the null case. So you could try something like:

Sub GetArea()

Me.NameOfAreaTextBox = Nz(DLookup("[Area]", "[FS_Area’ table]", "[AddresFieldName] = Forms!NameOfTheForm!NameOfAddressTextBox And [PostCodeFieldName] = Forms!NameOfTheForm!NameOfPostCodeTextBox"), "Not in Area")

End Sub

You would call this subroutine in the afterupdate events of the address and postcode textboxes.

Thanks for your response. Would this also write the result (e.g. AreaOne, AreaTwo, Not in Area etc.) to the 'Area' field in the 'People' table?

 

[sneuberg]

Thanks for your response. Would this also write the result (e.g. AreaOne, AreaTwo, Not in Area etc.) to the 'Area' field in the 'People' table?

No this would just populate the textbox; however, if the textbox is bound to a field in the People table then it would be saved (written) along with the other data that was entered into the form.

I suggest that you consider whether you should store it in the People table. If the Area is always based on the Address and Postal Code then it would violate normal form to store it. A update anomaly would occur if you updated the Area in the FS_Area’ table as the value would not be updated in the People table. Sometimes these separate values are desired. If for example these Areas change over time and you want the area in the People table to remain the area it was when the record was entered. But it seems more likely to me that you want it to be always the current Area.

If you don't store it in the People table you can alway get it went you need it with a join or DLookup from the FS_Area’ table.

 

[dal1503]

No this would just populate the textbox; however, if the textbox is bound to a field in the People table then it would be saved (written) along with the other data that was entered into the form.

I suggest that you consider whether you should store it in the People table. If the Area is always based on the Address and Postal Code then it would violate normal form to store it. A update anomaly would occur if you updated the Area in the FS_Area’ table as the value would not be updated in the People table. Sometimes these separate values are desired. If for example these Areas change over time and you want the area in the People table to remain the area it was when the record was entered. But it seems more likely to me that you want it to be always the current Area.

If you don't store it in the People table you can alway get it went you need it with a join or DLookup from the FS_Area’ table.

I need it to be stored somewhere so that I can run queries based on the data e.g. number of people that lived in AreaOne. What would be the best way for me to structure my data?

Sorry for all the questions, I'm struggling with this so thanks for your help

 

[sneuberg]

Could you upload your database or give us a screen shot of your relationships. With that I think I can explain how you would create the query that would get number of people that lived in AreaOne for example.

Also if you don't understand database normalization then I suggest checking out this series of Web pages sometime.

 

[dal1503]

I suggest that you consider whether you should store it in the People table. If the Area is always based on the Address and Postal Code then it would violate normal form to store it. A update anomaly would occur if you updated the Area in the FS_Area’ table as the value would not be updated in the People table.

Do you have any suggestions on how I could store it? The area would always be based on house number and postcode, and if the areas changes I would want it to update the record in the People table. However, I can't think of a way of storing this data without violating normal form.