?
Solved

Tricky DCount for me - maybe not you

Posted on 2000-02-20
12
Medium Priority
?
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 600 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
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: 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

777 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