Use query to find and sum data

[kwelch]

I have a medical database in which I enter procedure codes (5 digit code, e.g. 31276) for each patient record. In my table, I have 10 fields which hold these codes. For example, a patient may have undergone 6 procedures and each field would hold the code for each procedure.

I've been able to design a query to look for a code of my choosing, but only if that particular code is listed in a certain field (say, field 1). I want the query to examine all 10 fields for a code that I specify and then tally up the occurrences of that code.

Ultimately, I want to be able to ask how many times I have performed 31276 (or any other procedure code) procedure.

Thanks for any help.



(And BTW, I have picked up Access only by trial and error and am quite the novice.)

[Rainlover]

This field of CODE should be store in a separate Table in the one field with a Relationship to the Parent Table.

If I lost you post a Copy of your Database and I will show you.

PS Convert to 2003 before posting.

[kwelch]

This field of CODE should be store in a separate Table in the one field with a Relationship to the Parent Table.

If I lost you post a Copy of your Database and I will show you.

PS Convert to 2003 before posting.

The procedure codes are stored in a separate table and are inserted into the large table.

The database is 26 MB. It has patient information in it anyway, so I can't post it. Sorry.

Here's a sample:

Master table ---

patient name | date surgery | code 1 | code 2| code 3 | code 4
----------------------------------------------------------------
joe blow       1/1/01         31276   31209   31276      30000
jane doe       1/1/01         20520   31276   61795
homer          1/1/01         31255   31276   31255      31276

So, I see a total of 5 occurrences of "31276". That's what I want to automate. all the codes are in a separate table and arranged like:

1 | 30000 removal of this
2 | 30001 removal of that
3 | 30002 excision of this
4 | etc

[Rainlover]

I really need to see the design of your tables.

If you can't post your data base please explain your table structure.