Relationship problem!

[jamal numan]

Relationship problem!

Please, have a look on the attached screenshots and the attached Access file so that the description below becomes clearer.

I’ve a table named “services” which contain 4 fields, Ref_C, A, B and C. The fields A, B and C are assumed to read their values from the table named “choices”. In the relationship, I couldn’t link the fields A, B and C to the same table (choices) through the field "Ref_Choices" to “enforce referential integrity”. “choices” table has 3 fields, Ref_choice, Choice_engl and Choice_arab. I linked the A, B and C fields to the Ref_choices and both of them are “numbers”

My point is how can I link the three fields A, B, and C (contained in the Services table) to the same field "Ref_Choices" contained in the table "choices"?

Thank you in advance,



Regards

Jamal

[June7]

Service table is not normalized but might be okay for your needs.

If you want to set relationships, add Choices to the Relationships window 3 times. Join to A, B, C fields. Unfortunately, Access already thinks there is a relationship between Choices and Service and will create a duplicate of Service in Relationships. Will have to copy Service, delete the original (yes to remove relationships) and rename the copy then set Relationships.

[jamal numan]

Service table is not normalized but might be okay for your needs.

If you want to set relationships, add Choices to the Relationships window 3 times. Join to A, B, C fields. Unfortunately, Access already thinks there is a relationship between Choices and Service and will create a duplicate of Service in Relationships.

Will have to copy Service, delete the original (yes to remove relationships) and rename the copy then set Relationships.

Thank you June7 for the useful answer.

i tried the solution that you have provided and it worked very well (screenshot is attached).

I couldn't understand what did you mean by:

Service table is not normalized but might be okay for your needs.

Will have to copy Service, delete the original (yes to remove relationships) and rename the copy then set Relationships.

[/QUOTE]

all the best

Jamal

[June7]

Multiple similar named fields is indication of non-normalized structure, especially when the fields actually hold the same data, hence the triple link on Choices. Normalized would be like:

Service
ID
Ref_Community(foreign key)
Ref_Service (foreign key)
ServiceType (A, B, C) this field might not be necessary if A, B, C have no more meaning than choice 1, 2, or 3
ServiceDate

This is a junction table that allows unlimited choices. Your original structure allows only 3 choices. Depending how you want to manipulate the data for output, this might be okay. If you want to do any kind of statistics or searching on services, will be more difficult. If you have to allow more choices in the future, could mean significant effort to modify the database structure.

[jamal numan]

Multiple similar named fields is indication of non-normalized structure, especially when the fields actually hold the same data, hence the triple link on Choices. Normalized would be like:

Service
ID
Ref_Community(foreign key)
Ref_Service (foreign key)
ServiceType (A, B, C) this field might not be necessary if A, B, C have no more meaning than choice 1, 2, or 3
ServiceDate

This is a junction table that allows unlimited choices. Your original structure allows only 3 choices. Depending how you want to manipulate the data for output, this might be okay. If you want to do any kind of statistics or searching on services, will be more difficult. If you have to allow more choices in the future, could mean significant effort to modify the database structure.

Hi June7,

Please, have a look on the attached screenshots and the Access file.

The main concept behind this project is to attach the availability of the service in each community.

The problem with the normalization is that we need to repeat choosing the same “community” (in this case) more than one time to choose all the services and then to choose its availability. this is very time consuming

Am I right?

All the best

[June7]

Actually, no, with properly related tables, should not have to repeatedly enter the community.

Are you the only user of this project? Developers don't build databases that allow users to interact directly with tables. Build forms for this. You could have a form/subform arrangement to speed up data entry in normalized table. Main form bound to Communities table, subform bound to the junction table. The Master/Child link properties of the subform container control synchronize the related records. Select a choice in the subform and the community will automatically fill in according to the link. Access Help has guidelines on building form/subform.

Whether you enter the data through the tables or forms, this behavior should be possible. If you do in tables, open Communities table. Go to the desired community record, click the + sign to open the junction table and only records related to that community will show. Enter new record and the community key will automatically populate, even though it doesn't show.

I have projects that don't fully normalize data because that works for my needs. It is a balancing between normalization and ease of data entry/output. Easier input with non-normalization might mean harder output. In my project the additional normalization actually would make input more complicated (needing subforms) and output more difficult for most of my reporting needs. However, this data is all numeric (lab test results) and don't have to do sort/filter/search on it. Output only got tricky when I had to create graphs with the data.

Your data is descriptive in nature and I expect you will want to do sort/filter/search, e.g. 'Find all communities that have sewarage service'. Having to search for that value in 3 fields complicates the effort. I expect normalization would be best for you.

[jamal numan]

Actually, no, with properly related tables, should not have to repeatedly enter the community.

Are you the only user of this project? Developers don't build databases that allow users to interact directly with tables. Build forms for this. You could have a form/subform arrangement to speed up data entry in normalized table. Main form bound to Communities table, subform bound to the junction table. The Master/Child link properties of the subform container control synchronize the related records. Select a choice in the subform and the community will automatically fill in according to the link. Access Help has guidelines on building form/subform.

Whether you enter the data through the tables or forms, this behavior should be possible. If you do in tables, open Communities table. Go to the desired community record, click the + sign to open the junction table and only records related to that community will show. Enter new record and the community key will automatically populate, even though it doesn't show.

I have projects that don't fully normalize data because that works for my needs. It is a balancing between normalization and ease of data entry/output. Easier input with non-normalization might mean harder output. In my project the additional normalization actually would make input more complicated (needing subforms) and output more difficult for most of my reporting needs. However, this data is all numeric (lab test results) and don't have to do sort/filter/search on it. Output only got tricky when I had to create graphs with the data.

Your data is descriptive in nature and I expect you will want to do sort/filter/search, e.g. 'Find all communities that have sewarage service'. Having to search for that value in 3 fields complicates the effort. I expect normalization would be best for you.

Many thanks June7 for the elaboration and the informative answer. this really helps

all the best

Jamal