Deadline! Count clients only once who come multiple times?

[russi]

Aaagh! Deadline is tomorrow am. And I am stuck. (Job training agency. )

Have a master client table with IDnumber to which other tables are linked in a one-to-many relationship.

I have to show how many clients begin a category of services for a given week.
In the category (which is a separate table from the master one), there can be multiple records for a given client IDnumber, because there are multiple services that they can receive.

BOTTOM LINE is that I need to query by a parameter asking for week involved and come up with how many DISTINCT client IDnumbers received a service in that category FOR THE FIRST TIME. (There is a field in the cataegory table called StartDate.)

HELP. Thanks.

[This message has been edited by russi (edited 02-05-2001).]

 

[Rich@ITTC]

Hi Russi

Don't think it is possible in one query, but I think you could break it down into 2-3 queries.

First I will assume that your week is seven days from a Monday. On your form (from which you run the query via a command button) you could have a combo box or text box that allows you to put in the date for the week concerned (it could even be an unbound text box).

Your first query - qryClientThisWeek, has the date of service (I think you have called this StartDate) filtered by Forms!frmMyForm!txtWeek (i.e. you reference the date typed into the relevant control by the user). You will need to add the normal operators eg.

>=Forms!frmMyForm!txtWeek And < (Forms!frmMyForm!txtWeek+7)

This should bring up everyone who has used the service this week.

Next let's bring up everyone who has previously used the service. Create a second query based upon the same underlying table but this time call it qryClientPrevious. In the criteria you use the same reference, but "less than". So again [StartDate] is used with this criteria ...

<Forms!frmMyForm!txtWeek

Then you want to create a third query - a find unmatched query (I'd use the Wizard for this - it's quick and easy). So you want to find people in qryClientThisWeek who do not appear in qryClientPrevious.

This works on a left join (I think) and IsNull as the criteria in the ClientID column. The Access Wizard for this type of query does it all for you.

So you now have a query showing all people who have first made use of the service this week. You might even find duplicates here (one person using the sevice twice/three times etc), so you might want to use Unique Values in the property for the query.

Try it out ... I have made similar queries to find such information.

HTH

Rich

[This message has been edited by Rich@ITTC (edited 02-05-2001).]

 

[russi]

Thanks a lot, it worked!!!

BUT I must be a dummy!
Because I can't get query to add the number of resulting IDNUMBERS that show from the 3rd query. - Is it because of the IsNull criteria?

Your continued help in final resolution of this task would be humungeously appreciated.

 

[JHMarshIII]

If it is the null values creating your problem you might try using the Nz Function to pre-fill the nulls.