DCount issue

[SimplyDemented]

I am attempting to write some reports and am pretty much teaching myself access here at work. I am currently stuck on a counting issue.

I have a table where among other things it has a name and a number. Lets say for example:

Joe 2
Bob 3
Betty 4
Bob 5
John 1

As you can see, there are 2 Bobs in there. I have made a form where you have a drop down with all the names in the table, as well as a "*". If I choose an individual name and run it with this function in my report...

=DCount("[Rep_Name]","[tbl_listening]","[Rep_Name] = Forms![Listening_Retrieval_Form]![Combo18]")

...I get the total amount of times said person appears in the table. The problem occurs when I choose the *. I want it to display all the people along with their individual counts, but it ends up doing a total count. So in the example above, each name would just show 5 for the count.

I understand why it is happening, I just can't for the life of me figure out how to fix it. Unfortunately, my SQL is rusty and I never learned VB (plan to soon) so I really am working with very little. I know things like C, C++, Java, etc so I can follow things online, but writing it myself the syntax gets butchered.

Anyone know if there is just some tweaks I can make so that it will do what I want?

Thanks in advance for any help.

 

[WayneRyan]

SD,

You want the DSum function instead.

Wayne

 

[SimplyDemented]

Wayne,

Appreciate the help, but I can't seem to get it to work that way either. I looked up DSum and it seems to be for running totals where the total is calculated by adding up whats in the fields rather than how many times the fields show up.

Correct me if i'm wrong, but DSum would be good if I wanted to a running sum of the scores a person was given. So like here

Bob - 5
Joe - 3
Bob - 2

DSum would be good for getting that Bob came out to 7 or the total score is 10. I'm just trying to get it so that it tells me how many times bob was in the database, and how many times Joe was.

Again, sorry if i'm wrong here, that's just the way i'm understanding the explanations online.

Again, I could be way off on this

 

[WayneRyan]

SD,

Oops sorry ... I'm getting confused now.

None of the DCount, DSum functions will do what you want.

Select PersonsName, Count(*)
From   YourTable
Group By PersonsName
Order By PersonsName

That will give you the number of records for each unique person in the
table. What do you want to do with that list?

Wayne

 

[SimplyDemented]

I am making a report that just displays some basic information. One of the things I want it to display is all the peoples names and to the right, how many times they show up in the DB in a given time range.

So right now I could enter "*" for name, then "6-1-07" "6-7-07" and it would show me all the people that are in the DB within those date ranges. Now I just want it to show how many times they were in there in that date range.

Eventually I may also have another test of how many times person x shows up with some more given criteria, but for now, just getting it to show the count would make me very happy.

Forgive me for being very new at this, but where would I put the code you gave me? In a new query?

 

[WayneRyan]

SD,

For the moment, base your report on a query.

Add a new column:

HowMany: DCount("[Rep_Name]","[tbl_listening]","[Rep_Name] = '" & [Rep_Name] & "'")

That should do it.

Just include the new field HowMany in your report.

Wayne

 

[SimplyDemented]

Wayne,

You are like my new hero. Worked like a charm. Thank you.

I know it was such a simple little fix, but I just don't have the access experience to have figured it out.


I only have one other issue if you don't mind my whining. I have a button on my form that I tied to report preview through the form wizard. For some reason though, sometimes things get added to my table when all I want to do is retrieve data. So I have my form with a drop down with the names (and a * as well), and 2 date fields. I hit execute, it gives me my report (with the now working count display ), and occasionally it saves the name and/or star and one of the dates in the table.

I assume this other issue is just turning an option to 'no' somewhere.

Again, I really appreciate all the help!

 

[Mackem Nat]

Dear SD & Wayne

I have been search through the forum to find the answer to my problems amd you both seem to be the closest that have solved the problem.

I have a table with a range of fields (around 50).

I have then set a query which just copies most of the data, but if a few cases does a calculation, i.e. converts a date of birth to an age range some falls into etc. Most importantly the entry date of the form has a parameter set up against it so I can filter results by an entry range.

Finally I have a report which accoring to my Dummies book (initially useful as I have started from scratch but now just doing my head in !) I have tried adding in a dcount formula into the control source book related to the query.

I started with an easy one of count male gender. All done or so I thought. The syntax is correct, you open the report and it asks for start and finish date, and the result............

#error

I have tried using th original table instead of the query for the dcount and it works, but I loose the date range paramter. Can either of you help as I am not sure what to try next ?!???!??!

Just to make the water a bit muddier, I am not the end used, so I need for final product to be very user friendly. For example: the way the parameter box pops up to ask a start date is good, going into the query and modifying cirteria is bad!

 

[WayneRyan]

MN,

The best solution is to bring up a Dialog Box form from the command button
that initiates your report. This form will have places for the user to enter
any dates or other parameters for your report.

The query for your report can use these parameters for its criteria:

Forms!YourNewPopupForm!YourParameter

The DCount on your report can also use the above syntax in its criteria.

Wayne

 

[Mackem Nat]

Hi Wayne

I have taken some of your advice and an trying a not to elegant solution. I was worried that basing the count of the query would still give a #error regradless of where the parameter can from.

I have therefore gone down the route of:
Using a form to ask a start and finish date
Using a report with text boxes to count data straight off the table.
The reason for the "not so elegant" remark is the length of expression required in the control source box to filter the report (like the query).

I know the start/end date question in Form!Welcome! are working cos they are filtering the search results on another query I have.

Below are 2 samples I have:

1/
=IIf([Date] Between Forms!Welcome![Start Date] And Forms!Welcome![End Date],DCount("[Gender]","Brief Contact Table","[Gender]='M'"),0)

2/
=DCount("[Gender]","Brief Contact Table",("[Gender]='M'" And ([Date] Between Forms!Welcome![Start Date] And Forms!Welcome![End Date])))

No. 1 = Is counting all the "[Gender]='M'") OK when I use a very early start date, and late end date. BUT when I add an end date in the middle of some entries the result returns 0.

No. 2 = Counter all entries against Gender (either M or F) and when I enter a end date in the middle of entries I get 0 again.

Can you help me sort this formula ??

so near yet so far

Nat