Possible Relationship Issue?

[Bill Sperry]

I have a table that uses an identifying number for each customer. I also have an identifying number for a geographical area (1-8) When I create reports etc, I need the geographical number to be automatically populated with the customer ID. Note: (I don't see how this makes a difference, but the customer ID is a 4 or 5 digit number. It is unique, but not sequential). I am new to access and have multiple tables created and/or to create. Iuse these tables in a HIPAA/FISMA secure environment and so each table must be limited to 3 or 4 fields. Thank you.

[June7]

Does some part of the customerID include the geographic area code? I doubt this and then there is no relationship between the customer and area. Access can't read minds. If it does include the area then there is no need to populate another field. The area can be extracted from the ID whenever needed.

[Bill Sperry]

Does some part of the customerID include the geographic area code? I doubt this and then there is no relationship between the customer and area. Access can't read minds. If it does include the area then there is no need to populate another field. The area can be extracted from the ID whenever needed.

No, the geographical area is not a part of the customer ID. I need to lock the 2 fields together so that whenever I refer to or need the customer ID, the geographic area will populate with it.

[June7]

That requires a table. The geographic area can be an attribute of the customer and include a field in the customer info table with that value or it can be an attribute of address (tied to city, county, state, or zip) and have an addresses table. Save the address ID to customer table. Whichever, must select something for the customer info record. Then the value will be available whenever the customerID is referenced as foreign key in other records. Just join tables in query.

[Bill Sperry]

I understand the use of tables. I have created the tables with the nescessary data as fields. What I need is a way to lock the Customer ID# and the geographical # together so that when I am using other tables, forms, reports, etc. they remain locked together. Example: I need a report that breaks down daily field appointments by geographical area (simular to zip code). When I use the customer ID, I need the report to automatically recognize and report the geographical code. I believe I have constructed the tables properly, but when the report or query generates it still wants to give me to options (1-8) for the geographical code rather than the absolute that is tied to the customer.

[Bill Sperry]

I believe I have found my answer. By creating a query with the proper fields from the customer table, and then using that query instead of the table to populate additional forms...does this make sense? thank you!

[Bill Sperry]

I believe I have solved my problem, but I don't see how or where to mark this thread complete.

[June7]

It's on the Thread Tools dropdown above the first post.