Distinct Record Count

[CampbellJD1]

I am using Access 2003 Professional. I have been working with Access for some time and I have created an MDB with a Linked Table and the Data there is temporarily transferred to other Table for different reports. I now have the task of a Daily report which I have been able to get to the point where it does 90% of what I need. I have been searching Google and Answer for the last three days on getting Access to do a Distinct Record Count in a Report and have attempted about 20 Suggestions, but none of them have produced the correct count.

On the report I count the number of Records in a Grouping and I can have from 1 to unknown # instances of the same record, but I need to count it only once. I am fairly competent when it comes to Access, but the Scripting gives my problems when it comes to getting it to work in Access Queries and/or Reports, so basic is best or simple English is also requested.

I greatly appreciate any assistance I can attach the MDB (Empty) if some can tell me how so you can view what is completed.

[BradHodge]

On the report I count the number of Records in a Grouping and I can have from 1 to unknown # instances of the same record, but I need to count it only once. I am fairly competent when it comes to Access, but the Scripting gives my problems when it comes to getting it to work in Access Queries and/or Reports, so basic is best or simple English is also requested.

I would suggest using a Query as your recordset for the report. When in Query Design, right click in the blank table area at the top, and choose Properties, and then Unique Values = Yes.

Then when you design your report, put a Text Box in the group footer that you want, and set it's Control Source to "=Count([YourField]".

That should do the trick for you.

Brad.

[CampbellJD1]

I would suggest using a Query as your recordset for the report. When in Query Design, right click in the blank table area at the top, and choose Properties, and then Unique Values = Yes.

Then when you design your report, put a Text Box in the group footer that you want, and set it's Control Source to "=Count([YourField]".

That should do the trick for you.

Brad.

I tried that and it did not work, here is what I have:
I have a Query with Ticket # and Assign_to_Group I then do one Ticket # for the count of the Records.
On the report the Tickets are organized by the Assigned Groups and each update to the Ticket is listed. I then do a Ticket count Unfortunately I get a count of every line on the Report for each Group, but I need it to be Distinct count as each ticket may get several updates by each group.
I have attempted about 20 different Queries with different settings, but they always give the same results (not a Distinct Count).
Thanks for the information so far.

[BradHodge]

I have a Query with Ticket # and Assign_to_Group I then do one Ticket # for the count of the Records.
On the report the Tickets are organized by the Assigned Groups and each update to the Ticket is listed. I then do a Ticket count Unfortunately I get a count of every line on the Report for each Group, but I need it to be Distinct count as each ticket may get several updates by each group.
I have attempted about 20 different Queries with different settings, but they always give the same results (not a Distinct Count).

Got a couple of questions for you...

1) You said that you have the query with ticket # and Assign_to_Group, and that you assign one ticket # for the count of Records. Is that the count of records for each group? For example would Group A have 3 tasks assigned to them and therefore all three tasks would be under ticket # 100?

2) What is the structure of the query (i.e. what tables or queries is it made up of, and what is their structure with regards to the fields in question)?

Brad.

[CampbellJD1]

Got a couple of questions for you...

1) You said that you have the query with ticket # and Assign_to_Group, and that you assign one ticket # for the count of Records. Is that the count of records for each group? For example would Group A have 3 tasks assigned to them and therefore all three tasks would be under ticket # 100?

2) What is the structure of the query (i.e. what tables or queries is it made up of, and what is their structure with regards to the fields in question)?

Brad.

My apologies for the long delay, but did not get to this over the weekend.
The Tickets may be updated by one of the Groups when it is assigned to them. A count would be anytime a Group updates the Ticket, but I need to count a single instance of the Ticket update per group , but also need to show their updates as the day progresses and as work is completed on the Ticket.

The Report comes from a Query with the Table ZRM2DailyT and all 9 of the Fields included. It groups on the Field Assign to Group and Counts the Tickets on Ticket #. I have attempted to get the Query to count Single instances by doing the Unique Record and Unique Value, but neither work. I have attempted to hide Duplicates in the Report, but that only hides that specific fields Duplicate, but counts it anyway since some of them are constant during the update process.

I could attach a copy of the MDB to this message if I knew how; I setup a folder on Friday hoping I could put a copy of it their, but no luck. I think it would be best if you could see the setup and could analyze it that way if that is possible.

Thanks in advance

[BradHodge]

My apologies for the long delay, but did not get to this over the weekend.
The Tickets may be updated by one of the Groups when it is assigned to them. A count would be anytime a Group updates the Ticket, but I need to count a single instance of the Ticket update per group , but also need to show their updates as the day progresses and as work is completed on the Ticket.

The Report comes from a Query with the Table ZRM2DailyT and all 9 of the Fields included. It groups on the Field Assign to Group and Counts the Tickets on Ticket #. I have attempted to get the Query to count Single instances by doing the Unique Record and Unique Value, but neither work. I have attempted to hide Duplicates in the Report, but that only hides that specific fields Duplicate, but counts it anyway since some of them are constant during the update process.

I could attach a copy of the MDB to this message if I knew how; I setup a folder on Friday hoping I could put a copy of it their, but no luck. I think it would be best if you could see the setup and could analyze it that way if that is possible.

Thanks in advance

So I haven't had time to play around with it, but...

What if you put a check mark on your update form, and it only got a check if the group had not made an update to that ticket yet. Then, you could just count the number of check marks and not the tickets themselves on your report.

You could use a DLookUp statement in VBA to decide whether the box got checked or not. For example...

If IsNull(DLookup("Check", "tblTicketUpdate", "[TicketID] = '" & TicketID & "' AND [GroupID]= '" & GroupID & "'")) Then
Me.Check=True
End If

The check box could of course be invisible on your form.

Brad.

[CampbellJD1]

Brad,
Meant to let you know yesterday, but things got very busy all day. Thanks for the assistance, everything is working great now, your Ideas were very helpful and I know i will also use them in the very near future.
Thanks again,
John

So I haven't had time to play around with it, but...

What if you put a check mark on your update form, and it only got a check if the group had not made an update to that ticket yet. Then, you could just count the number of check marks and not the tickets themselves on your report.

You could use a DLookUp statement in VBA to decide whether the box got checked or not. For example...

If IsNull(DLookup("Check", "tblTicketUpdate", "[TicketID] = '" & TicketID & "' AND [GroupID]= '" & GroupID & "'")) Then
Me.Check=True
End If

The check box could of course be invisible on your form.

Brad.

[BradHodge]

Glad to hear it!

Brad.