Results 1 to 12 of 12
  1. #1
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38

    language for counting records.

    I have a Table called 'Label Plate Data'. I need to count the number of records, which isn't a problem. This is the Code I used:



    SELECT Count(*) AS [Total Plates]
    FROM [LABEL PLATE DATA];

    But in this same query, I need to count the number of records where a field is NOT empty. That field is 'Installed By'.

    This query also needs to be populated by the decision of a users input for another field. That field is called 'Hull'.

    So, when the query is run, it will ask the user Which Hull, then user then inputs the hull number and two values will be returned.

    in specified hull-
    1: Total number of records and
    2: Total number of records where Installed by has data.

    Please help.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    didn't check syntax but something like:

    SELECT Count(*) as TotalPlates, sum(iif(isnull([installed by]), 1, 0)) as BlankInstalls
    FROM [Label Plate Data]
    WHERE [Hull] = [Enter the Hull Parameter]

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    SELECT Count(*) AS [Total Plates], Sum(IIf(IsNull([Installed By]), 0, 1) AS [Total Installed] FROM [LABEL PLATE DATA] WHERE [Hull]=[input hull ID];

    I never use dynamic input parameter popups. Can't validate user entry. Better to have input into control on form and reference the control as parameter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    This is real close to working- for BlankInstalls, I'm getting '5', but I should only be getting 1. Any ideas as to why?

  5. #5
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    LOL, this is working just the opposite of RPeares, I have 369 records, 364 installed. I should have 369 records, 1 installed.

  6. #6
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    I just took out the one record where installed by had a value, reran the query and blankinstalls went up to 6

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'blank'? Do you allow empty strings in tables? Empty string and Null are not the same thing.

    What do you mean by 'took out' - just changed the field value, not delete record?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    Quote Originally Posted by June7 View Post
    What do you mean by 'blank'? Do you allow empty strings in tables? Empty string and Null are not the same thing.

    What do you mean by 'took out' - just changed the field value, not delete record?
    Blank installs is what you named the field. It's actually number of plates installed, so I renamed it to "Plates Installed".

    Took out- I went into the table and deleted the value that was entered into 'Installed By' so I could see what value was returned. I expected 4 (1 less than the 5 that was returned by the query originally), but I got 6, one more.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Want to provide db? Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    believe me, I'd love to...however, I can't because of nature of my job, however, I may have it figured out...Allowed zero length needs to be set to 'No', is that right?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, if you want to prevent empty strings. But think you will have to scrub the data first by replacing empty strings with null otherwise will probably get invalid data error when try to save modified table.

    First, try:

    SELECT Count(*) AS [Total Plates], Sum(IIf(Nz([Installed By],"")="", 0, 1) AS [Total Installed] FROM [LABEL PLATE DATA] WHERE [Hull]=[input hull ID];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Jrw76 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2013
    Posts
    38
    Awesome!

    thank you

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

Similar Threads

  1. counting records
    By Jrw76 in forum Access
    Replies: 5
    Last Post: 01-14-2014, 02:11 PM
  2. Counting Records in a Query
    By bomich in forum Access
    Replies: 2
    Last Post: 11-16-2012, 03:00 AM
  3. Counting records
    By mjsabin in forum Access
    Replies: 1
    Last Post: 03-06-2012, 11:11 AM
  4. Counting Only Certain Records
    By jtphenom in forum Queries
    Replies: 9
    Last Post: 03-29-2011, 01:25 AM
  5. Counting Records
    By WhiteNite1971 in forum Access
    Replies: 1
    Last Post: 01-22-2011, 06:36 AM

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