Results 1 to 4 of 4
  1. #1
    kwelch is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20

    Use query to find and sum data

    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.)

  2. #2
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows XP Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  3. #3
    kwelch is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20
    Quote Originally Posted by Rainlover View Post
    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 ---


    HTML Code:
    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
    Last edited by kwelch; 11-22-2009 at 11:59 AM. Reason: More data

  4. #4
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows XP Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    I really need to see the design of your tables.

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find Records Query
    By sullyman in forum Programming
    Replies: 1
    Last Post: 10-28-2009, 08:49 AM
  2. find specific data
    By hoachen in forum Queries
    Replies: 3
    Last Post: 08-19-2009, 08:54 AM
  3. Replies: 4
    Last Post: 07-16-2009, 02:42 AM
  4. Find out changed data
    By Papote in forum Programming
    Replies: 1
    Last Post: 04-27-2009, 08:19 PM
  5. Find data, load data, and save as a new record
    By hawzmolly in forum Access
    Replies: 0
    Last Post: 10-05-2008, 03:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums