Adding date field ignores count fields?

[missypooh1969]

I have a query that contains a few different count fields. I am trying to set up a parameter where the user will enter a [start date] and[end date] to see the same counts. Here's what the query looks like now:

Company_Name	Total Of Surveys	A1	A2	A3	A4	A5	Satisfaction_Rating
Water Company	2	0	0	1	0	1	100.00%
Employee Comp	1	0	0	0	0	1	100.00%
Aqua Motel	59	0	0	21	8	30	100.00%
Need break	1	0	0	0	0	1	100.00%

Now when I enter the date field even as a hidden field, I getthis (the dates individual counts, when I need the total count for the timeperiod) I have tried the where clause but it doesn’t seem to do anything:

Company_Name	Total Of Surveys	A1	A2	A3	A4	A5	Satisfaction_Rating
Water Company	1			1			100.00%
Water Company	1	1					100.00%
Employee Comp	1	1					100.00%
Aqua Motel	1	1					100.00%
Aqua Motel	1	1					100.00%
Aqua Motel	1	1					100.00%

And ideas?I have alsotried starting from scratch, adding the date, with the same result.I have come across this before, but I do notremember how I handled it.I wouldrather stay away from reports, as I will be exporting the query results weekly,monthly, quarterly and yearly.
Thanks in advance!

[ItsMe]

Perhaps you can start by creating a separate query that only performs the count and then include that query as a subquery. In order to count records, you only want to include fields/columns that are unique to what you are counting. Adding additional columns to your query may produce counts that are not desired.

[missypooh1969]

The first screen shot is the first query. The second screen shot is actually a second query, using the first query's counts (supposed to be). I tried going the other way, had the dates in the first query, then made a second query to use those with the calculations. Both turned out the same. Then I tried making two queries, one with dates, one with calculations, then a third to pull the data together, still same result.

[ItsMe]

How I do it, is to create a query where its sole responsibility is to count (perform the aggregate function). Do you get the correct count when you only add the date field, a query with one column?

Add or remove columns to get the desired count.

[missypooh1969]

I have a query that has the exact solutions that I need. When I add the date to the query, it breaks those solutions up "by date". Take the date out, back to the solutions I need, but no way of narrowing it to the dates I need. Use two queries, one with the solutions, one with the dates, combine them, same result. I do not want to see a breakdown of every single day. I want to see the counts for a period of time set by user parameters. Instead of seeing, July 1, July 2, July 3 and on, with the counts for those specific days, I need to see July 1, 2014 - July 31, 2014 and the overall counts for that time period.

[ItsMe]

Do you get the correct count when you create a query with only the date field, a query with one column? Add or remove columns to get the desired count. Then, worry about all of the other stuff.

[missypooh1969]

The counts are correct. When I add the date either by combining two queries and using one query I come up with this:

Company_Name	Surveydate	Total Of Surveys	5	4	3	2
Water	7/22/2014	1			1
Water	7/22/2014	1	1
Water	7/22/2014	1	1
Aqua	7/7/2014	1	1
Aqua	7/8/2014	1	1
Aqua	7/14/2014	1	1
Aqua	7/14/2014	1	1
Aqua	7/14/2014	1			1
Aqua	7/14/2014	1	1
Aqua	7/14/2014	1			1
Aqua	7/14/2014	1			1
Aqua	7/15/2014	1		1

I want to see this: (which would have parameter [start date] and [end date] (07/01/2014, 07/31/2014)

Company_Name	Total Of Surveys	5	4	3	2	1
Water	3	2		1
Aqua	9	5	1	3

[ItsMe]

Create a query with these fields... This will be your subquery

Company_Name

Surveydate

Total Of Surveys

THEN

Create another query with
Company Name 5 4 3 2

While in design view of your second query, add the subquery that you created previously (the query with the count). JOIN on Company_Name.