Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101

    Using another method for totals on form other than DCOUNT

    I am working on a database that keeps track of deployed and inventory of IP phones. I have 3 systems and I have a continuous form for each system system showing all configured IP phones. On each form I have command buttons to perform various functions. Two of these command buttons bring up a pop-up form. 1 for counts of phone types and the other counts of licences used.
    Pop-up 2, License counts. I have 14 different licenses and 4 conditions to check for each license. My current pop-up has 61 control boxes, 15 four each condition which includes a total at the bottom and one control box to total all licenses with all control boxes using DCOUNT and checking 3 criteria; platform, license and condition. If 3 is to many to check I can build queries for each platform.

    Example code:
    Code:
    =DCount("[platform]","qry_licenses_NonCare","[licenses]='I3_LICENSE_BASIC_STATION' and [cicstatus] = 'up-to-date'")
    This all works great except for the delay when opening the form. I read on this forum that using an aggregate query would work better. I created one query that gives me the counts and now I am trying to get the correct formula in my control boxes.
    I tried the following but get an error "#NAME?"



    Code:
    nz(=IIf([qry_licenses_reg]![platform]="non-care",iif([qry_licenses_reg]![licenses]="I3_ACCESS_ACD_MEDIA_1",[qry_licenses_reg]![countofcicstatus],"0"))

    One more thing, a condition will not have a value for every license in that case I want to return a 0 value.



    Any help or other suggestions on how to accomplish this task would be greatly appreciated.

    Here is a partial screen shot of my pop-up forms.

    Click image for larger version. 

Name:	example_417.JPG 
Views:	38 
Size:	32.4 KB 
ID:	33617

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Use at your own risk. I made this from time to time handing off the value to a variable. As long as you name the count field and reference it !Name at the end should work. also this is intended for vba.
    =currentdb.openrecordset("Select Count(*) as Counter from tblTableName")!Counter

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Base your form on the query, don't set control values based on the query.

    If you do that you can use the format property of the control in the form to display 0 if there is a null value.
    See here for some more examples https://support.office.com/en-gb/art...4-1fe286636668
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thank you Perceptus for your suggestion but what I am trying to do I don't think that VBA would give me the results I am looking for.

    And thank you Minty for your reply. Using the query itself as the data source for the form won't give the results I am looking for. It will need to be set up as a continuous form and each item would show as a separate record for each condition and won't look like the screen shot provided in my post.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Provide sample of the raw data.

    Possibly a CROSSTAB query can produce the desired output. Will probably have to include both the Licenses lookup table as well as the data table in order for every license to always be included.
    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.

  6. #6
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    June7, in what format would you like the sample data?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You can build tables in post with the Advanced editor to provide sample data or attach db or Excel. Follow instructions at bottom of my post.

    A table in post would be preferred if that will provide adequate sample.
    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
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101

    Requested information

    Relationship is set as a 1 to many from tbl_ManagedIP table to tbl_Licenses with the PhnIndex field.
    If you need any other information please let me know. Thanks again.


    PhnIndex StationName Platform MAC Address Type CICStatus SIPRegistration PhoneModel StationExten PhoneLabel User FirstName LastName Extension Comments
    1581 ST1111 Non-Care a1b1c1d1e1f1 Managed IP Up-to-date
    Polycom VVX500 1111 ST1111




    1582 ST2222 Non-Care 001122334455 Managed IP Up-to-date
    Polycom VVX310 2222 ST2222




    1583 ST3333 Non-Care 223344556677 Managed IP Not Registered
    Polycom IP330 3333 ST3333




    1584 ST4444 Non-Care aabbcc223344 Managed IP Up-to-date
    Polycom IP335 4444 ST4444




    1585 ST5555 Non-Care 334455667788 Managed IP Not Registered
    Polycom IP330 5555 ST5555




    3114 ST6666 Care 00aa11bb22cc Managed IP Not Provisioned
    Polycom IP330 6666 ST6666




    3115 ST7777 Care ef1200b0d0aa Managed IP Up-to-date
    Polycom IP330 7777 ST7777




    3116 ST8888 Care 99aabb225522 Managed IP Up-to-date
    Polycom VVX500 8888 ST8888




    3117 ST9999 Care 1234567890ab Managed IP Up-to-date
    Polycom VVX500 9999 ST9999




    3118 ST1000 Dialer 1234567890cd Managed IP Up-to-date
    Polycom VVX311 1000 ST1000




    3119 ST1001 Dialer 1234567890ef Managed IP Up-to-date
    Polycom VVX311 1001 ST1001




    3120 ST1002 Dialer 1234567890aa Managed IP Up-to-Date
    Polycom IP335 1002 ST1002




    3121 ST1003 Dialer 1234567890bb Managed IP Soft Phone
    ININ Soft Phone 1003 ST1003




    3122 ST1004 Non-Care 776655443322 Managed IP Up-to-Date
    Polycom VVX311 1004 ST1004




    3123 ST9900 Care 1234567890ab Managed IP Up-to-date
    Polycom VVX500 9900 ST9900




    3124 ST6600 Care 00aa11bb22cc Managed IP Not Provisioned
    Polycom IP670 6600 ST6600





    ID PhnIndex Platform StationName ValueName Licenses Path Class
    2505 1581 Non-Care ST1111 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    2506 1582 Non-Care ST2222 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    2507 1583 Non-Care ST3333 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    2509 1584 Non-Care ST4444 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    2510 1585 Non-Care ST5555 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    2511 1585 Non-Care ST5555 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7626 3114 Care ST6666 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7627 3114 Care ST6666 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7628 3114 Care ST6666 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7629 3114 Care ST6666 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7630 3114 Care ST6666 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7631 3114 Care ST6666 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7632 3114 Care ST6666 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7633 3115 Care ST7777 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7634 3115 Care ST7777 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7635 3115 Care ST7777 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7636 3115 Care ST7777 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7637 3115 Care ST7777 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7638 3115 Care ST7777 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7639 3115 Care ST7777 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7640 3116 Care ST8888 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7641 3116 Care ST8888 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7642 3116 Care ST8888 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7643 3116 Care ST8888 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7644 3116 Care ST8888 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7645 3116 Care ST8888 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7646 3117 Care ST9999 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7647 3117 Care ST9999 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7648 3117 Care ST9999 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7649 3117 Care ST9999 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7650 3117 Care ST9999 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7651 3117 Care ST9999 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7652 3117 Care ST9999 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7653 3118 Dialer ST1000 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7654 3118 Dialer ST1000 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7655 3118 Dialer ST1000 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7656 3118 Dialer ST1000 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7657 3118 Dialer ST1000 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7658 3118 Dialer ST1000 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7659 3118 Dialer ST1000 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7660 3119 Dialer ST1001 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7661 3119 Dialer ST1001 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7662 3119 Dialer ST1001 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7663 3119 Dialer ST1001 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7664 3119 Dialer ST1001 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7665 3119 Dialer ST1001 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7666 3119 Dialer ST1001 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7667 3120 Dialer ST1002 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7668 3120 Dialer ST1002 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7669 3120 Dialer ST1002 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7670 3120 Dialer ST1002 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7671 3120 Dialer ST1002 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7672 3120 Dialer ST1002 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7673 3120 Dialer ST1002 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7674 3121 Dialer ST1003 Counted Licenses I3_ACCESS_ACD_MEDIA_1
    Workstation
    7675 3121 Dialer ST1003 Counted Licenses I3_ACCESS_CLIENT
    Workstation
    7676 3121 Dialer ST1003 Counted Licenses I3_ACCESS_DIALER_ADDON
    Workstation
    7677 3121 Dialer ST1003 Counted Licenses I3_ACCESS_INTERACTION_SCRIPTER_ADDON
    Workstation
    7678 3121 Dialer ST1003 Counted Licenses I3_ACCESS_RECORDER
    Workstation
    7679 3121 Dialer ST1003 Counted Licenses I3_ACCESS_RECORDER_CLIENT
    Workstation
    7680 3121 Dialer ST1003 Counted Licenses I3_LICENSE_BASIC_STATION
    Workstation
    7681 3122 Non-Care ST1004 Counted Licenses I3_LICENSE_BASIC_STATION

    7682 3123 Care ST9900 Counted Licenses I3_LICENSE_BASIC_STATION

    7683 3123 Care ST9900 Counted Licenses I3_ACCESS_CLIENT


  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post the sql statement you use for the aggregate counts.

    Could also post what the output should be for the sample provided.

    In your attempted expression, the = sign is misplaced but why use Nz() when you have the nested IIf()? Referencing query name is only valid if the query is the form's RecordSource.
    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
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thanks again for the effort. There will be a case where there is no license for a particular condition so I wan to return a 0 value showing no licenses used.
    Here is the SQL as requsted.

    Code:
    SELECT Tbl_Licenses.Platform, Tbl_Licenses.Licenses, Count(Tbl_Licenses.Licenses) AS CountOfLicensesFROM Tbl_Licenses
    GROUP BY Tbl_Licenses.Platform, Tbl_Licenses.Licenses;
    Output

    Platform Licenses CountOfLicenses
    Care I3_ACCESS_ACD_MEDIA_1 4
    Care I3_ACCESS_CLIENT 5
    Care I3_ACCESS_DIALER_ADDON 4
    Care I3_ACCESS_INTERACTION_SCRIPTER_ADDON 4
    Care I3_ACCESS_RECORDER 4
    Care I3_ACCESS_RECORDER_CLIENT 3
    Care I3_LICENSE_BASIC_STATION 5
    Dialer I3_ACCESS_ACD_MEDIA_1 4
    Dialer I3_ACCESS_CLIENT 4
    Dialer I3_ACCESS_DIALER_ADDON 4
    Dialer I3_ACCESS_INTERACTION_SCRIPTER_ADDON 4
    Dialer I3_ACCESS_RECORDER 4
    Dialer I3_ACCESS_RECORDER_CLIENT 4
    Dialer I3_LICENSE_BASIC_STATION 4
    Non-Care I3_ACCESS_RECORDER 1
    Non-Care I3_LICENSE_BASIC_STATION 6

    Click image for larger version. 

Name:	output_0419.JPG 
Views:	26 
Size:	62.0 KB 
ID:	33643

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    CROSSTAB query provides summary data for the licenses in tbl_Licenses.

    TRANSFORM Count(tbl_Licenses.PhnIndex) AS CountOfPhnIndex
    SELECT tbl_Licenses.Licenses
    FROM tbl_Licenses INNER JOIN tbl_ManagedIP ON tbl_Licenses.PhnIndex = tbl_ManagedIP.PhnIndex
    GROUP BY tbl_Licenses.Licenses
    PIVOT tbl_ManagedIP.CICStatus;

    The sample provided doesn't have all the licenses shown in your desired output. If you want every license even if there is no data in tbl_Licenses then need a table of all licenses that can be included in the CROSSTAB query. This would be a 'lookup' table where each record is unique license ID and description.

    Use expression in textbox to return 0 when field is Null: =Nz([Up-To-Date],0)
    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
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thanks again June7, I will give this a try over the weekend. I am think it might be fine to only show what is being used instead of the way I was presenting the data.
    I will let you know how it ends up.

  13. #13
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    June7 thanks for your assistance the cross tab queries work great. Performance has improved when bringing up the data. One question I do have and don't know if this is possible but I would like to get the row and column totals. I changed my pop-form to use the new cross tab query as the data source and made it a continuous form. I tried adding a control box and making the data source add the four column. It showed the correct totals but not for all rows.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    By 'control' box you mean text box?

    Did you place textboxes in form footer with aggregate expression for column totals? That works?

    Arithmetic with null will return null. Is that what happens for row totals? Use Nz function to handle null.
    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.

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    don't know if you resolved this

    There will be a case where there is no license for a particular condition so I wan to return a 0 value showing no licenses used.
    but if not, use the format property for the control to show a zero if null

    number have four format options, positive, negative, zero, null. Many developers are only aware of the first two

    so to show a zero for null

    0;0;;"0"

    It can also be used as a prompt for a user to enter some data

    0.00;0.00;0.00,"Enter a price"

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  2. Dcount or Totals
    By mpaulbattle in forum Queries
    Replies: 16
    Last Post: 11-30-2016, 03:04 PM
  3. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  4. Query help - Totals by Delivery Method by Hour
    By jdrumgold1983 in forum Queries
    Replies: 3
    Last Post: 05-06-2014, 02:25 PM
  5. Replies: 5
    Last Post: 12-06-2011, 11:18 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