my brain hurts!

[nicko]

hi,
i am developing a db in access 2000 and i am stuck on what i consider to be a basic requirement.
in the table i have 12 similar text fields called "client contact week n" where n is a number from 1 to 12. the data stored in these fields is limited within the input form to 7 types. for example: letter, telephone, face to face etc.
i need to produce a report that displays the total records for each type of contact for each week - 12 weeks * 7 types of contact = 84 totals.
i have successfully created 84 queries - 1 for each which delivers a numeric total for each type for each week but i am struggling to get all the results onto 1 report. i have tried to create a simple table to store these totals and then base the report on that but to no avail. update queries failed with the message "query needs to be updateable" or something like that but i have full access to all aspects of the db!! an amend query failed i think due to having 84 separate source queries as i was greeted by "query is too complex". i created 12 further queries to group the individual ones hoping that it could handle 12 but still no joy.
this is frustrating me because i think i am doing it right but access cant handle 84 individual queries!!
please help
cheers

[FishVal]

hi,
i am developing a db in access 2000 and i am stuck on what i consider to be a basic requirement.
in the table i have 12 similar text fields called "client contact week n" where n is a number from 1 to 12. the data stored in these fields is limited within the input form to 7 types. for example: letter, telephone, face to face etc.
i need to produce a report that displays the total records for each type of contact for each week - 12 weeks * 7 types of contact = 84 totals.
i have successfully created 84 queries - 1 for each which delivers a numeric total for each type for each week but i am struggling to get all the results onto 1 report. i have tried to create a simple table to store these totals and then base the report on that but to no avail. update queries failed with the message "query needs to be updateable" or something like that but i have full access to all aspects of the db!! an amend query failed i think due to having 84 separate source queries as i was greeted by "query is too complex". i created 12 further queries to group the individual ones hoping that it could handle 12 but still no joy.
this is frustrating me because i think i am doing it right but access cant handle 84 individual queries!!
please help
cheers

84 queries ??!!!

You definitely should take a look at this
Database Normalisation and Table structures

[ramaswamynanda]

hi, i am posting an mdb file. i have created a master detail relationship, with client and client contact table, and a query - query1, which gives u all data with a single simple join. i have used only 4 contact types, but u can extend easily.

I agree with fishval. take a look at the normalization doc.

let me know if that solved ur problem

thx
nanda