Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50

    Question query is excluding an entire group


    I am new to this forum and actually joined to learn new tips and tricks. I have been designing Access database for some time but still consider myself a novice. I am not a programmer by profession.
    I have a query that is excluding an entire population. I think it is because it is not counting certain fields because they do not meet my criteria. i.e. I want it to count how many records meet the criteria of CCS>0. The answer is NONE but instead of telling me "0" it will not pull in ANY of the other data elements (fields) at all. So I get no results from the query.
    I would greatly appreciate some guidance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the SQL of the query? A query criteria restricts the records returned by the query. If you have >0 on a field named CCS, no records will be returned unless the value in that field is 0 or less.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    SELECT Count(TableName.CSS) AS CountOfCSS
    FROM TableName
    WHERE TableName.CSS>0;

  4. #4
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    It's pretty messy because this query is based on several other queries used to sort out my data for the final report. But, here it is.
    All of my queries within the query are joined by Unit (or agency as my queries call it)

    SELECT Qry_PLA2_byAgency.UNIT, Qry_PLA2_byAgency.SumOfCountOfID1 AS [Total Number Screened], [Qry_CountOfPLA2 >=200_byAgency].CountOfID11 AS [# with PLA2 >=200], Round(([CountOfID11]/[SumOfCountOfID1])*100,2) AS [% Positive PLA2 >=200], Qry_CountofCCS_byAgency.[Cardiac Calcium Scores Performed], [Qry_CountOfCCS>0_byAgency].[CCS >0], Round(([CCS >0]/[SumOfCountOfID1])*100,2) AS [% Positive CCS], ([CCS >0]+[CountOfID11]) AS [Ttl CCS >0 + PLA2 >=200], Round((([CCS >0]+[CountOfID11])/[SumOfCountOfID1])*100,2) AS [% Positive For Preclinical CAD], Round([Count of BMI]/[SumOfCountOfID1]*100,2) AS [% BMI >= 30], [Qry_CountOfBMI>30_byAgency].[Count of BMI] AS [BMI >30], [Qry_CountOfInsulin>15_byAgency].[CountOfInsulin>15] AS [Insulin >15], Round(([CountOfInsulin>15]/[SumOfCountOfID1])*100,2) AS [% Prediabetic], Qry_CountOfHTN_byAgency.CountOfHTN AS [Poorly/Uncontrolled BP], Round(([CountOfHTN]/[SumOfCountOfID1])*100,2) AS [% Evidence of HTN]
    FROM (((((Qry_PLA2_byAgency INNER JOIN [Qry_CountOfPLA2 >=200_byAgency] ON Qry_PLA2_byAgency.UNIT = [Qry_CountOfPLA2 >=200_byAgency].UNIT) INNER JOIN Qry_CountofCCS_byAgency ON [Qry_CountOfPLA2 >=200_byAgency].UNIT = Qry_CountofCCS_byAgency.UNIT) INNER JOIN [Qry_CountOfCCS>0_byAgency] ON Qry_CountofCCS_byAgency.UNIT = [Qry_CountOfCCS>0_byAgency].UNIT) INNER JOIN [Qry_CountOfBMI>30_byAgency] ON [Qry_CountOfCCS>0_byAgency].UNIT = [Qry_CountOfBMI>30_byAgency].UNIT) INNER JOIN Qry_CountOfHTN_byAgency ON [Qry_CountOfBMI>30_byAgency].UNIT = Qry_CountOfHTN_byAgency.UNIT) INNER JOIN [Qry_CountOfInsulin>15_byAgency] ON Qry_CountOfHTN_byAgency.UNIT = [Qry_CountOfInsulin>15_byAgency].UNIT
    GROUP BY Qry_PLA2_byAgency.UNIT, Qry_PLA2_byAgency.SumOfCountOfID1, [Qry_CountOfPLA2 >=200_byAgency].CountOfID11, Round(([CountOfID11]/[SumOfCountOfID1])*100,2), Qry_CountofCCS_byAgency.[Cardiac Calcium Scores Performed], [Qry_CountOfCCS>0_byAgency].[CCS >0], Round(([CCS >0]/[SumOfCountOfID1])*100,2), [Qry_CountOfBMI>30_byAgency].[Count of BMI], [Qry_CountOfInsulin>15_byAgency].[CountOfInsulin>15], Round(([CountOfInsulin>15]/[SumOfCountOfID1])*100,2), Qry_CountOfHTN_byAgency.CountOfHTN, Round(([CountOfHTN]/[SumOfCountOfID1])*100,2);

  5. #5
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    thanks Joe. this works just fine to give me the total of all records with CCS of >0. However, if the said agency has no ccs >0 I would like for Access to say ZERO records from this agency have ccs results that are >0. What ends of happening is the other data fields that I am trying to get from the same query/report do not show up at all. Instead, that whole agency just gets eliminated from my results query altogether.
    I tried running this code but when I used the criteria specific unit (or agency) it gave me the same result Nada!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    thanks Joe. this works just fine to give me the total of all records with CCS of >0. However, if the said agency has no ccs >0 I would like for Access to say ZERO records from this agency have ccs results that are >0.
    The simple query that I posted does, in fact, return a zero if there are zero records. But obviously, as seen from post #4, you are trying to do a lot more than that in your SQL code.

    What I recommend doing is the following:
    - Create a query that returns ALL the groups you want to see.
    - Do a Left Join from this query to your other tables/queries that you want in your final query.
    - Use the NZ function to return "0" for records which do not have counts (see: http://www.techonthenet.com/access/f...dvanced/nz.php)

  7. #7
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    I think what this will do is give me a total count of records regardless of whether the field is NULL, 0.00, or >0 correct?
    I can already get that count without a problem. What happens is I get no results for all of the other fields when I try to count how many records are greater than 0 in the CCS>0 field when there are NONE.
    thanks for taking you time to try to understand my problem Joe.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I can already get that count without a problem. What happens is I get no results for all of the other fields when I try to count how many records are greater than 0 in the CCS>0 field when there are NONE.
    I understand, and that is what the process I suggested should do.
    Have you tried it?

    I have set-up a simple example below, and it looks like you may not even need to use NZ, if you have a one-to-many relationship where you are have to Group the records. You would use NZ if you have a one-to-one relationship. In either case, the key is that you MUST do a Left Join.

    So let's say that you have two Tables. Table1 is a client listing. Here is some sample data:
    Code:
    ClientName
    abc
    lmn
    rst
    xyz
    Let's say that Table2 is a Sales table, that shows the number of sales you have made to each client:
    Code:
    ClientName	SaleDate	ItemSold
    abc	          1/1/2016	10
    abc	          3/1/2016	20
    abc	          5/1/2016	50
    xyz	          4/1/2006	100
    Then, if we create a query where we do a LEFT JOIN from Table1 to Table2, and do an Aggregate Query grouping by ClientName and counting the ItemsSold field, we get this result:
    Code:
    ClientName	CountOfItemSold
    abc	          3
    lmn	          0
    rst 	          0
    xyz	          1
    The SQL code for that query looks like:
    Code:
    SELECT Table1.ClientName, Count(Table2.ItemSold) AS CountOfItemSold
    FROM Table1 LEFT JOIN Table2 ON Table1.ClientName = Table2.ClientName
    GROUP BY Table1.ClientName;
    Isn't that what you are looking for (in a simplified scenario)?

  9. #9
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Holy Cow Joe. I think I see where I was going wrong all along. I was setting my query to count 'client ID' and not 'items sold'. In other words, in my query I was asking Access to count ID of CCS>0 when I should have been telling it to count CCS>0. Does that make sense? when I made a new query that counted the CCS>0 field it started giving me zeros. I will have to go in and restructure things a bit to see if it will now pull in the rest of the data as well.
    I will let you know.

  10. #10
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Click image for larger version. 

Name:	2016-06-29_10-16-50.png 
Views:	28 
Size:	14.1 KB 
ID:	25058
    Joe, I still can't fix this problem. here is a single query that I want to give me results of number of rows that have a calcium score >0.
    If there are no rows for a specific unit then the unit does not show up in the results of the query. I want the unit to show up and count to show 0.Click image for larger version. 

Name:	2016-06-29_10-26-42.png 
Views:	28 
Size:	29.0 KB 
ID:	25059

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Any chance you can upload a copy of your database for analysis?
    I can download it on my home computer and take a look.

  12. #12
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Quote Originally Posted by JoeM View Post
    Any chance you can upload a copy of your database for analysis?
    I can download it on my home computer and take a look.

    I wouldn't mind but I am concerned about HIPAA
    I could just put in a dozen or so fake names and leave the rest of the data. that should work

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I wouldn't mind but I am concerned about HIPAA
    I could just put in a dozen or so fake names and leave the rest of the data. that should work
    Yes, remove all private/sensitive data before uploading your database (if deleting data, be sure to do a Compact & Repair before uploading the database to purge all those deleted records).

  14. #14
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    Quote Originally Posted by JoeM View Post
    Yes, remove all private/sensitive data before uploading your database (if deleting data, be sure to do a Compact & Repair before uploading the database to purge all those deleted records).
    Joe,
    It won't upload my file. not sure why.
    I have it all secured. I just made a copy and then ran an update query to change all the names.

  15. #15
    MLangendorf is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jun 2016
    Posts
    50
    could it be because I don't have enough posts yet?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  2. Query for excluding TOP N records
    By dolovenature in forum Queries
    Replies: 1
    Last Post: 09-14-2012, 03:49 PM
  3. Excluding phone numbers using a query
    By Scott O'Neal in forum Queries
    Replies: 1
    Last Post: 09-06-2012, 11:04 AM
  4. Insert entire row from query into new row in another table
    By chris.williams in forum Programming
    Replies: 3
    Last Post: 10-13-2011, 01:38 PM
  5. Excluding data in a query
    By lpdds in forum Queries
    Replies: 1
    Last Post: 12-16-2009, 08:06 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