Counting report using a date search

[Cillies]

Hi,

I have a simple DB with two tables one with basic information on it like date, contact, action etc.
A Second which stores values I want to use for a combo box (CategoryNameID & CategoryName).

I designed a query combining the two tables. (See SQL below)

I based a form on this query which allows the user to enter information such as: (DateReceived, DateDealtWith, CategoryName, Action, Other). On this form I have a combo box (CategoryName) from which the user selects the category that is specific to that request.

I then designed a report that shows the list from the combo box (see below) and it is these entities that I want to count. As in how many times each enquiry was requested over a given period of time.

CategoryName Count

A enquiry 2
B enquiry 3
C enquiry 3
D enquiry 0
E enquiry 0
F enquiry 2
G enquiry 5

But if the enquiry was not requested over a certain period of time I still want it to appear on the list e.g. D & E enquiry where not selected but are still displayed in the report.

Here is the SQL

SELECT Query3.CategoryName, Query3.Count, Query3.[MaxOfDate Recieved], First(Log2006.[Date Recieved]) AS [FirstOfDate Recieved]
FROM Query3, Log2006
GROUP BY Query3.CategoryName, Query3.Count, Query3.[MaxOfDate Recieved]
HAVING (((First(Log2006.[Date Recieved])) Between [Forms]![Form1]![BeginDate] And DateAdd("s",86399,[Forms]![Form1]![EndDate])));

Query3 SQL

SELECT Categories.CategoryName, Count(Log2006.[Date Recieved]) AS [Count], Max(Log2006.[Date Recieved]) AS [MaxOfDate Recieved]
FROM Categories LEFT JOIN Log2006 ON Categories.CategoryID = Log2006.CategoryID
GROUP BY Categories.CategoryName
ORDER BY Categories.CategoryName;

The report is returning the way I want it but with a very significant problem.

The problem is that I can't bring up the report using the dates.

I call the report through a date form, unfortunately when I enter the dates in, the report returns all the results within the database as opposed to between the dates I entered in the Call form. I am thinking it might have something to do with the DATE being used as the count record whereas I should be counting the CATEGORYNAME. I have tried tweaking the SQL but to no success.


Hope this is clear as to what I am hoping to achieve


Hope someone can help

 

[dRahme]

Hi, this may or may not be reasonably close:

SELECT Categories.CategoryName, Count(Log2006.[Date Recieved]) AS [Count], Max(Log2006.[Date Recieved]) AS [MaxOfDate Recieved]
FROM Categories LEFT JOIN Log2006 ON Categories.CategoryID = Log2006.CategoryID
GROUP BY Categories.CategoryName
HAVING (((Max(Log2006.[Date Recieved])) Between [forms]![form1].[begindate] And [forms]![form1].[enddate]))

union

SELECT Categories.CategoryName, Count(Log2006.[Date Recieved]) AS [Count], "" AS DateRcvd
FROM Categories LEFT JOIN Log2006 ON Categories.CategoryID = Log2006.CategoryID
GROUP BY Categories.CategoryName, ""
ORDER BY Categories.CategoryName;

hope that helps

 

[Cillies]

Thanks dRahme for your reply, unfortunately that didn't work either, as is still wasn't producing the results within the date range selected. However I have been tweaking with it and managed to get a solution which I have posted below.

Again many thanks!!

Below is the first query's SQL

SELECT Categories.CategoryID, Categories.CategoryName, Count(Categories.CategoryName) AS [Count]
FROM Categories LEFT JOIN Log2006 ON Categories.CategoryID = Log2006.CategoryID
WHERE (((Log2006.[Date Recieved]) Between [Forms]![Form1]![BeginDate] And DateAdd("s",86399,[Forms]![Form1]![EndDate])))
GROUP BY Categories.CategoryID, Categories.CategoryName;

I then used this query in another query:

SELECT Categories.CategoryName, Query5.Count
FROM Categories LEFT JOIN Query5 ON Categories.CategoryID = Query5.CategoryID;

This returns results, however, where a category has not been selected it fails to place a zero in the count column to indicate that it wasn't selected between the selected dates. (minor problem, I know!)