Counting records in a report

[Cillies]

Hi,

I am having problem with counting records.

On my form the user selects one value from a dropdown box (Which is controlled by a table). I want to be able to display all the values from the table on a report. And count the number of times they have been selected in the form. I will also need to display the items from that table that where not selected.

I can do this using a LEFT join query, then on the report I used a unbound text box using the Count(*) function, which displayed the correct values for the items that where selected. My problem is that for the items that were not selected (Which ARE displayed on the report) a figure of 1 is added in the count field.

Does any one know how to change the 1 to a 0 (which is the correct value. I know the Count(*) function is incorrect)

Here is an example of what I am getting in my report

Category Type Actual Count (What I Want)

X enquiry 2 2
y enquiry 3 3
z enquiry 3 3
a enquiry 1 0
b enquiry 1 0
c enquiry 2 2
d enquiry 5 5

The Items with a 1 as the actual count are items that where not selected, I want these items to be displayed as Zero

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

Also I will have a criteria date search.

Hope someone can help

 

[hneal98]

For items a and b, what is being counted? I know you said you wanted 0 instead of 1, but you need to show us the code that is pulling the counts.

You talk about left joins, unbound text boxes; but you don't indicate what you are using to get the results of the report. What does your query look like, if you are using a query? what is your query counting?

 

[dhookom]

As hneal98 suggests, you should show us your SQL. I expect you could just count() the records from a field in the table that might not have matching records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cillies]

Sorry about that folks!

Its a simple DB with two tables one with basic imformation on it like date, contact, action etc.
A Second which stores values I want to use for a combo box.

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. On this form I have a combo box which the user selects which entity is specific to that request.

I then designed a report that shows the list from the combox (see below) and it is these enities that I want to count. As in how many times each enquiry was requested.

But if the enquiry was not requested over a certain period of time I still want it to appear on the list.

Here is the SQL

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


EG of report

Monthly Statistics Report
Category Name Count

Enquires - Abroad 2
Enquires - UK 1
Canada 2
Collectors Cards/ Gifts 1
Complaints - Abroad 1
Complaints - UK 1
Email Address Request 1
History 1
HR 1
Ingredients 1
Inverstor Relations 1
Pensions 1
Press Enquiries 1
R&A Request 1
Ramdom Calls 1
Sales/New Business 1
Student Request 1

Total Monthly emails 21 (the total should be 4)

The only problem with counting these entites is that most of the entities have not be used through the form, but are still being counted on the report as 1 which is wrong as they have not been used.

Hope this clears up some of your questions

 

[dhookom]

Does this work? If not, what do you actually want to count?

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cillies]

Hi dhookom,

Thanks for the reply. Yea! it works fine, but I also need to show the values that where not selected from the drop down box in the form. (there are twelve values in total)

i.e. I want to count the number of times each item from the drop down box was selected over the period of a month.

The SQL above shows that, but it does not show the items that where not seleceted, and I need to show these items whether they where selected or not.

 

[Cillies]

Sorry there was a mistake in my last post "(there are twelve values in total)"

Its actually 17.

 

[dhookom]

Try create another query based on your table of unique CategoryNames and the above query. Set the join type to include all records from the "table of unique CategoryNames".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Cillies]

Thanks dhookom,

I managed to get it working, below is a copy of 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])));