Duplicate records that are not really duplicates

[Ryker7]

I have a child advocacy database where a child is entered with a unique case id. For Federal reporting, a count of the cases during a certain time period is required...no problem. I run a query with a date range parameter...design a report based on that query that will print the records for the date range and count them. The problem: Federal reporting only wants the child counted once during the reporting period. Let's say during the month of April, a child comes in three different times with three different problems. This child will be assigned three case id's and entered into the database three times. The report as I have it now will list and count the child three times for April. The Federal standards report only wants the child counted once for April. The exact wording is: "Do not count a child more than once even if different services were provided." What do I need to do in order for access to perform this operation?

Thanks

 

[scriverb]

You must have a field that is unique to that child to be able to count it only once. SSN#, etc. What is that field called.

We can create a query that will Group By this field thus summarizing the counts for that child down to a single incident.

Post back with table and field names that you need in the query and I can help put that together for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Ryker7]

The unique field is Social Security Number. The table is tblChild. For this particular report, I want Case ID, Date Entered, Name, Address, City, State and Zip. Birthdate and Gender.

Thanks

 

[scriverb]

Copy, paste, and save this SQL as a new saved query and name it qryCountSSN.

SQL:

Select A.[Social Security Number], Count(A.[Social Security Number] as CountSSN FROM tblChild GROUP BY A.[Social Security Number] HAVING A.[Date Entered] BETWEEN [Enter BegDate: ] and [Enter EndDate: ];

Now use the above saved query as input to your existing query and link them together with an INNER JOIN on [Social Security Number]. Add the CountSSN to your selection of fields from the new saved query. This field will have the same value in it for all of your records for your report. Just create a control with CountSSN as the control source to display a count of the records for this social security.

Since I haven't seen your existing query I don't know how you entered your date ranges. I included a date range in this query.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]