Results 1 to 15 of 15
  1. #1
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118

    Query Table for "*" and then Count all instances

    I am creating a drug abuse database. One results come back, I want to break down some stats. Table looks something like
    NAME - SSN - DRUG 1 - Drug 2 - Drug 3 - Etc

    I have a form for creation of the records, combo box for selecting THC-COC etc...
    I want to count all fields, for all records in the table for THC, and COC, and DMETH and 6Am, etc...

    How can I go about this?
    Count and Groupby is no working together, plus the BEST option would for it to have a user input for Criteria, where I type the drug i am looking for and get the results I need at that time.



    Any suggestions??

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you need to type the drup in every drug fields.

  3. #3
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    Does it matter how the data gets into the table? Its still THC or COC. The trick is getting a count from different fields/records into ONE result field

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I suggest you modify the structure of your table to make it have only one drug field:
    NAME - SSN - DRUG
    james 111 drug1
    james 111 drug2

    or split it into 2 tables.

    then you can get what you want easyly

  5. #5
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    the problem with that is everything else I have built into this uses my current table.
    besides that, in reality, its
    Positive Test 1 Drug 1 - Positive Test 1 Drug 2 - Positive Test 1 Drug 3 all the way to Positive Test 4 Drug 3

    Hence it would turn into 4 tables, then the query would be ridiculously long as well

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    No, No,No, you need only one table. at most 2 table to reduce redundancy.

    NAME - SSN - DRUG - test
    james 111 drug1 1
    james 111 drug1 2
    james 111 drug2 2
    ...

    drug is the name of drug, test maybe only a number 1,2,3 or 4.

  7. #7
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by Steven.Allman View Post
    I am creating a drug abuse database. One results come back, I want to break down some stats. Table looks something like
    NAME - SSN - DRUG 1 - Drug 2 - Drug 3 - Etc
    steve,

    another thing you might want to consider is normalizing your data. I assume that the "drug" named fields are specifying the number of drugs (and their names) that the person has abused?

    If that is the case, you would be much better off making a brand new table to keep records for instances of abuse. Connect it to the "abusers" table by an ID or person's name (id is better).

    Structuring this way will eliminate the inevitable situation where you have to pull horizontal data related to each person (e.g. - the "drug" fields).

    Not only that, but what happens when you have 5 fields for different drugs for one whole year and suddenly someone comes to you that has 100 different drugs on his record? Well....now you've got to write code that creates 95 more fields in your table!

  8. #8
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    You are kind of correct. My table currently has
    first test - first test drug 1 - first test drug one levels - first test drug 2 - first test drug 2 levels. This is broken down to 4th test drug 3 levels
    irony, is today i got a test result that had FOUR drugs in his system!!!!.
    makes me want to smack people in the face~!

  9. #9
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Consider our suggestions:
    Alternate the structure of your table:
    1 ClientTable:
    SSN Fname Lname ...

    2 abuseTable:
    SSN Drug Test TestLevel

    Create more tables if you need, such as Drug table, abuseInstance table etc.
    then you are flexible enough for even 100 drugs, 100 tests

  10. #10
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    i know your idea would work... im just not wanting to revamp all the queries I have built already...
    ugggg

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    so you need to add 4 more fields in you table

    do you have any more question or the issue is solved?

  12. #12
    Steven.Allman is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Feb 2010
    Posts
    118
    so how would I go about building a query that only showed people who have multiple tests.....?
    I am working the new table now and moving sensitive information (name-ssn-etc) but I need to have a report (from a query obviously) that shows people that mave more than ONE instance

  13. #13
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    What do you mean "have more than one instance"?

    do you mean any two or more fields from drug 1 test1 to drug4 test4?

    I think it will be a big query. I don't know how yet.

  14. #14
    Join Date
    Apr 2010
    Posts
    21
    The query wizard has a built in duplicates wizard. Make this query for a start and you could modify it to suit.

  15. #15
    oldman is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    17
    Try an approach that I use for other similar things. Set up a drug list table separate from the people table. Now you can easily set a drop down list in your data entry form for a drug by using the drug list as the source data and asking that it write the ID of that drug to the People list using a list box or a combo box. Note: the form design wizard will ask what you want the entry is to be used for and you simply designate the drug name field as a place to store the result.
    The end result is that each individual entry into the form requires that you go to a new record for people testing positive for more than one drug and repeat the unique identifier for that next record.
    By writing back to the main table from each separate form entry, you create the multiple entries that Weekend00 suggested that you make.
    Now you can create any query you wish from the table. You can list every positive test of person 1, or you can list every positive test for THC or COC or whatever way you want to sort the results. You could even sort the results into a giant result that lists a count by drug name if that is what you wanted by using a crosstab query.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-13-2010, 10:50 AM
  2. Query Results "Count Totals"
    By tipopilotas in forum Queries
    Replies: 1
    Last Post: 03-26-2010, 10:36 PM
  3. Replies: 0
    Last Post: 09-17-2009, 12:21 PM
  4. Replies: 21
    Last Post: 06-03-2009, 05:54 PM
  5. "Count" and "Countif" in Reports
    By JMantei in forum Reports
    Replies: 1
    Last Post: 06-20-2006, 02:20 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