Calculate Unique Values in a Report 1

[bethabernathy]

Hi - I have a report that is based on a temp table that pulls data from several append queries. The report sorts out by different types. Some of the records are reported more than once as they might be listed in more than one of the groups.

I need to put a control on the report that counts the unique values. Unique fields include:

=Count([Expr1])
=Count([incidentID])

But, again these records may appear more than once in the report.

Any help is greatly appreciated.

Thanks, Beth

 

[CosmoKramer]

Beth,

If you put the count statements in the group footer, it will only count for that group...Is that what you're looking for??

 

[bethabernathy]

Hi Cosmo,

This one is a real pain. I don't want it to count by group as the record may even appear more than once in the group.

What I have done now is to create another query that runs off the final query in my macro. I set the field [Expr1] to Total by Groupby. Then that query runs a make table that pulls out the unique records.

So then I put the control on my report from the new temp table:

=DCount("Expr1","tbltemp1&quot

This is working, but the secondary query is running off a query that prompts you for a date range. So now when I run the macro, I am prompted for the date range twice, which I guess is o.k., but could cause data entry errors, if the dates aren't entered exactly the same for each prompt.

I started another thread asking if there was away to run a query off of a query with a date range and not be prompted twice for the date range. So that is a question as well?

I am thinking there must be another way to handle this -

Any ideas?

Thanks, Beth

 

[CosmoKramer]

Beth,

Are you running this report from a form?? If so, couldn't you add a text box for the user to enter a date into and refer to that text box in your query's criteria instead of a parameter inside the queries??

 

[bethabernathy]

Hi Cosmo - I don't really understand this method. So, I create a form that runs the report. Then they enter the date criteria into the form, but how does that handle pulling the unique data?

Beth

 

[CosmoKramer]

I guess I'm confused...Did you solve your original problem by using the second query?? Is your only problem now the fact that you are prompted twice to enter the dates??

If so, replace the date parameters in your queries with the names of your text box(es):

Between [Forms]![frmForm]![BeginDate] And [Forms]![frmForm]![endDate]

 

[bethabernathy]

Hi Cosmo - Yes I did solve my problem and the only problem that exists is eliminating two prompts to enter the date range.

So, I have the control/text box on the form counting:

=DCount("Expr1","tbltemp1&quot

Can you explain your syntax using the expression above?

Beth

 

[CosmoKramer]

OK - The example I posted above won't do anything for/to your counting logic. It's just to hopefully have the user only enter the date range once. Somewhere in your queries you must have parameters something like this:

Between [Enter Begin Date] And [Enter End Date]

Use a form with two unbound text boxes, one for the begin date and one for the end date. Where you currently have the parameters defined in your queries, change them to reference the two text boxes on your form:

Between [Forms]![frmForm]![BeginDate] And [Forms]![frmForm]![endDate]

Both queries will refer to those two fields. In the OnClick event of a command button, run this report. I have done this several times, usually using some sort of calendar control so that the user can click on dates they want, but that can be for another day......Let me know if this helps

 

[bethabernathy]

Hi - I am slowly starting to understand. This is where I am at:

I created a form:

frmdate

I added two unbound text boxes onto the form named:

begindate
enddate

Do I remove the date range criteria from the base query? i.e.

Between [Enter Start Date] And [Enter End Date]

and then add this into the criteria section of the date field in the queries:

Between [begindate]![frmdate]![BeginDate] And [enddate]![frmdate]![endDate]

It is working but when I run my macro, I am still prompted to enter the dates for both queries as they are running in the macro.

Am I on the right track at all?

Beth

 

[CosmoKramer]

Yep...Replace the prompts with the form references; except that it should probably be:
Between [Forms]![frmdate]![BeginDate] And [Forms]![frmdate]![endDate]

 

[bethabernathy]

Hi Cosmo: O.K. I changed the syntax and put it into both queries. At the end of my macro I set it to open the form. At the completion of the macros run, I get a prompt to enter:

[Forms]![frmdate]![BeginDate]
And then
[Forms]![frmdate]![endDate]

that takes me to the form where I enter the dates as well. It is working, but I'd like to eliminate the prompt at the end of the macro and go right to the form as I am still entering dates twice? I am feeling like I am missing just one little thing.

Beth

 

[CosmoKramer]

Beth,

The form should be the first thing to run. The user enters the two dates. Then in the OnClick event of a command button, run your macro. The queries will grab the dates from the form......

 

[bethabernathy]

Cosmo -

Once Again, Perfection.

Thanks so much. Beth