Solved

Tricky DCount for me - maybe not you

Posted on 2000-02-20
12
493 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now