0

I am trying to link two separate tables based on a City and State field. I originally had a combined field of City, State however, I thought it was a best practice to have these two fields broken out. My problem is I do not know how to make the relationship link between two tables using two fields. If I link just the City without the State this is incorrect, because many cities share the same name across multiple states. I thought I could leave the City and State as separate fields and create a third field City, State for the purposes of linking the two tables, but my guess is there might be a better way of doing this. I attempted a Lookup, but I wasn't sure if this was the correct and/or best route. Is the combined CityState field the correct way to achieve this link? I assume I would still, later on, be able to Query on just City or just State.

Thank you.

Chris2015
  • 1,030
  • 7
  • 28
  • 42
  • 1
    Try http://stackoverflow.com/questions/11730619/reference-to-composite-primary-key-in-access-2007/11737106#11737106 – Fionnuala Feb 18 '15 at 15:39

1 Answers1

0

Create an intermediate table with City and State fields. This will allow you to pull the state name by the value in the City field. The combined field is NOT recommended as this is unnecessarily repeating data on a record.

ZX9
  • 898
  • 2
  • 16
  • 34