Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I am trying to understand how this DCount function is working. Step by step.

    I know that for technical people like you June7 it is easey but i am not seeing how this is working.

    Click image for larger version. 

Name:	Screenshot_58.png 
Views:	19 
Size:	98.7 KB 
ID:	35146

    If you creating function like this you see how it is counted in your head, what results will be.
    I am not seeing it

    I know that DCount function is counting all pairs of Workload & isTrue field.
    when we have one condition:

    "[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' we will get (screen in atachment).

    How this AND Val(MemoryGIB)<" & Val([MemoryGIB]) will change it?
    How this is working?

    Best,
    Jacek


    When we have MemoryGib < MemoryGib. So when you have in Work1-Yes 128, 1024, 324 - dcount is comparing all of it? so checking if 128 is smaller < 324 but it is also smaller than 1024...

    Best,
    Jacek
    Attached Thumbnails Attached Thumbnails Screenshot_59.png  

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The function runs for each record. With the < criteria referencing the ID of that record, only records prior to that record are considered. If you have 100,000 records the function runs 100,000 times. Domain aggregate functions can cause slow performance in large datasets.

    I attempted the second query only because the GIB values are not repeated for each Workload/isTrue pair.
    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.

  3. #18
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you June very much,

    Assume that we have :

    Click image for larger version. 

Name:	Screenshot_62.png 
Views:	15 
Size:	48.4 KB 
ID:	35155

    So here we will go for each record.
    so when we have 128 GB why here is 1?
    And why 324 GB is 2 and why 1024 is 3?

    Please elp to understand this entirely,

    Best,
    Jacek

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    128 returns 1 because 0 records meet the criteria then + 1 (or use <= condition and eliminate the +1)

    324 returns 2 because 1 records meet the criteria then + 1 (or use <= condition and eliminate the +1)

    1024 returns 3 because 2 records meet the criteria then + 1 (or use <= condition and eliminate the +1)


    Don't know how to better explain.
    Last edited by June7; 08-17-2018 at 02:59 PM.
    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.

  5. #20
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you June7,

    we are close

    The first condition here is easy:

    SELECT DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND Val(MemoryGIB)<" & Val([MemoryGIB]))+1 AS GrpSeq
    In post #18 we can see that there is [Workload] & [isTrue] is fullfilled as condition. For each memory you have fullfilled conditions.

    But what about this?

    128 returns 1 because 0 records meet the criteria then + 1 (or use <= condition and eliminate the +1)
    128 meets one condition: [Workload] & [isTrue]. But what about Val(MemoryGIB)<" & Val([MemoryGIB])?
    128 is smaller then what?

    Functions is taking 128 and comparing it with each of record?

    Best,
    Jacek

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The function counts records with GIB less than the value of the concatenated GIB field. This calc occurs for each record. Yes, the function compares the 2 parameters against every 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.

  7. #22
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    aaa understand this !!!

    thank you veru much for your help and patient ;-)

    So Function is checking for 128 GIB if there is smaller value then it?

    So function sees that we have 3 values and there are not smaller values then 128 GiB. So we have 0.
    For 324 GiB there is one smaller value = 128 GiB from our set of 3 values (128,342, 1024) so we have 1
    When functions "looks" into record with 1024 - there are 2 smaller values then it - so we have 2 here...


    Best,
    Jacek

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

Similar Threads

  1. Transform Data .. pls help!
    By Toble in forum Queries
    Replies: 14
    Last Post: 02-23-2015, 05:34 PM
  2. Replies: 17
    Last Post: 06-10-2013, 06:49 AM
  3. Difficult Query!
    By pastormcnabb in forum Queries
    Replies: 2
    Last Post: 04-09-2013, 09:40 PM
  4. difficult query
    By methis in forum Queries
    Replies: 2
    Last Post: 03-18-2013, 02:36 PM
  5. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 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