Solved

Tricky DCount for me - maybe not you

Posted on 2000-02-20
12
499 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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