Solved

Tricky DCount for me - maybe not you

Posted on 2000-02-20
12
497 Views
Last Modified: 2008-03-10
I have this DCount (Access97) which works just perfectly - except that the client has asked it be modified so
that it doesn't count repeated ID numbers - (example this count gives me all the attendees of the an event whose elegibility is CSS between the two dates which the user specfies)
I need however the count to only output those with distinct ID nums:

=nz(DCount "ATTENDEE","405UnitsServID","[ELEGIBLITY] = 'CSS' And Date Between #" & [Forms]![405ls2cp]![startdate] & "# And  #" & [Forms]![405ls2cp]![enddate] & "#"),0)

I did try this which didn't work: (note 405junc is where the IDs data is stored)

=nz(DCount("ATTENDEE","405UnitsServID","[ELEGIBLITY] = 'CSS' And [Forms]![405junc]![ID] Is Distinct And Date Between #" & [Forms]![405ls2cp]![startdate] & "# And  #" & [Forms]![405ls2cp]![enddate] & "#"),0)

also in the underlying query 405UnitsServID I tried to place this line in the criteria for
ID:

(SELECT DISTINCT [ID])

but nothing has worked - any suggestions or help I would be grateful - I need to put
this project to rest -

Thanks

Kevin
0
Comment
Question by:kpu8
  • 7
  • 5
12 Comments
 
LVL 12

Expert Comment

by:Trygve
ID: 2541336
You can't do this using the current query (I think)

Try changing your 405UnitsServID query to a group query with the columns described below. If it is not possible because the query is also used for other purposes, then make another query for this purpose.

ELEGIBLITY - Grouped
ID - Grouped
FirstDate - Min([Date]) - NB: You should change the name of the Date column since Date is a reserved word in Access. Use RegisteredDate, InputDate or some other combined name.
LastDate - Max([Date])

Then you can probably use something like this for querying:

=nz(DCount "ATTENDEE","405UnitsServID","[ELEGIBLITY] = 'CSS' And FirstDate  >= #" & [Forms]![405ls2cp]![startdate] & "# And And LastDate <=   #" & [Forms]![405ls2cp]![enddate] & "#"),0)


0
 
LVL 1

Author Comment

by:kpu8
ID: 2558242
OK - actually they re-explained what they need -
the dcount I had originally:

=nz(DCount "ATTENDEE","405UnitsServID","[ELEGIBLITY] = 'CSS' And Datem Between #" & [Forms]![405ls2cp]![startdate] & "# And  #" &
[Forms]![405ls2cp]![enddate] & "#"),0)  

Needs to have some line added to it so that is counts distinct ID

Any suggestions
0
 
LVL 12

Accepted Solution

by:
Trygve earned 150 total points
ID: 2560732
If you like, you can EMail me the form, table and queries involved along with a description of a selection of dates an the restuling count of IDs you expect and I will have a look at it for you. EMail address in my profile.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 1

Author Comment

by:kpu8
ID: 2585511
Adjusted points to 150
0
 
LVL 1

Author Comment

by:kpu8
ID: 2585515
Thanks - that has appeared to work -

0
 
LVL 12

Expert Comment

by:Trygve
ID: 2585555
You are most welcome!
0
 
LVL 1

Author Comment

by:kpu8
ID: 2620597
Well - the client is saying the solution doesn't work right-
I believed it worked but the count for those individuals
who are of CSS status between 1/1/00 and 1/31/00
is around 80 they say and we both get 17 or so -
very strange - Someone suggested I use DcountDistinct off of this site:

http://members.ricochet.net/~jfoster/

What do you think?

And do you think I could implement it easily in my db

0
 
LVL 1

Author Comment

by:kpu8
ID: 2620719
The more the merrier so I posted a question:

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10311481 

for 200points because I don't see how to implement that function -
and if I can implement it - I'm golden - simply golden  
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2622632
I have looked closer at the MDB you sent med, and I don't understand where the two queries came into the problem. The DCounts are performed directly against the 405CLINT table which they should.

Is it possible for you to EMail me your latest MDB and point out where the difference between "want" and "get" is?

One point I found is that the StartOfMonth count is like this

=DCount("[ELEGIBLITY]";"405CLINT";"[ELEGIBLITY] = 'CSS' And [TERM_DATE] Is Null And [ADM_DATE] >= #" & CDate([cstartdate]) & "#")

The date check is checked to be larger than the start date in the form. Is this correct?
0
 
LVL 1

Author Comment

by:kpu8
ID: 2634588
Actually I got help with the dcountdictinst function it worked and
thus far gave the client the right numbers -

In regards to your question about start of the month - you are correct in that the dcount is in fact wrong -

The count is suppose to count all those people whose elegibility in the 405client table is equal to CSS and whose amd_date is either equal or greater to the start date which a user enters -

I'm thinking maybe I need to use between like this <> ?

 
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2635034
WHERE ... AND [ADM_DATE] >= #01-mar-2000# AND [ADM_DATE] <= #31-mar-2000#

Between does the same thing, but is not 100% compatible with SQL Servers, ASP files etc. so you might as well go with >= <=
0
 
LVL 1

Author Comment

by:kpu8
ID: 2637233
Thanks a million - that has solved
that problem - I will post you some free points -

I may be posting more soon - I have
one semi-tricky report to do
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Passing variables to a function 6 35
Delete Records on a Form in Microsoft Access 5 39
Turn off MS Access Default=0 for Numerics 6 25
MS Access from Delphi 31 29
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question