Solved

Tricky DCount for me - maybe not you

Posted on 2000-02-20
12
494 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

932 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

10 Experts available now in Live!

Get 1:1 Help Now