Query Counting Number of Items Using Custom Dialogue Box ?? Help!

[Neil]

Hi there,

I have a query in my database, which i use in a report, which counts the
number of times a specific item in one of my tables has been selected.

E.g. - Its a database for logging issues with a system, such as "user logged
out" - when logging an issue, there is a drop down menu (which uses a look up
field from my "tbl_problems" table, this table holds the descriptions of my
problems), the user simply selects a problem from the list. This query works
fine, the code is below:

SELECT Count(tbl_issue_log.problem_type) AS CountOfproblem_type,
tbl_problems.problem
FROM tbl_problems INNER JOIN tbl_issue_log ON tbl_problems.ID =
tbl_issue_log.problem_type
GROUP BY tbl_problems.problem;


I then have a report, which reports on the number count of each problem -
this works fine aswell.......

But, i have now made a custom dialogue box, which takes a date range via 2
txt boxes (txtBeginningDate and txtEndingDate) - it passes these dates once
entered, into another query, which i set up to accept these (i done all these
out a book i have, im by no means a coder! )......the code of this query
is:

SELECT Count(tbl_issue_log.problem_type) AS CountOfproblem_type,
tbl_problems.problem, tbl_issue_log.date
FROM tbl_problems INNER JOIN tbl_issue_log ON tbl_problems.ID =
tbl_issue_log.problem_type
GROUP BY tbl_problems.problem, tbl_issue_log.date
HAVING (((tbl_issue_log.date) Between
[forms]![dlg_box_problem_count_date_range]![txtBeginningDate] And
[forms]![dlg_box_problem_count_date_range]![txtEndingDate]));

What i want to do now, is basically have the report i made originally, but
when it runs, i want it to ask for the date range, and only show the amount
of problems counted for that date range......the problem just now is, that if
i run this query, it returns a problem count of one but repeats this for the
amount of counts. E.g. - if there have been 3 "locked out" instances, then
the query returns 3 different rows, with a count of 1, instead of one row
with the description, and a count of 3.

Any idea's?

many thanks,

Neil

 

[Jim C.]

Hi Neil,

Try removing tbl_issue_log.date from the Group by statement. This will
definitely cause the results you are getting if the dates are different.

Jim