This should work...but

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a simple query that I'm using as the source for a report. It has 5 of the fields from the source table in it - Line, DTCategory, DTHours, DataMonth, and DataYear.

DataMonth and DataYear are expressions that prompt the user to specify which month and year are to be compiled for the report. They are set using the Month and Year function and referencing the field DataDate from the source table

DTCategory has criteria "Is Not Null" to eliminate any blank fields that might be present. I have a Group By setting for all fields except DTHours which has Sum and I've set the result of the Sum(DTHours) to be DTHours. Now that I write that, I'm wondering if that's the problem...the Sum and the source field are named the same

It's a lot of info but I wanted to be sure I painted a complete picture of the query

Any help would be appreciated.
 
I think my problem is that I've got Group By in the Date field and it's Summing all the data for a particular date. I can't figure out how to change the query to allow the user to determine the Month/Year without Date in the query. What should the setting be rather than Group By so that it doesn't segregate by day
 
Post the SQL of the query. That will make it easier for everyone to
understand what you have right now.

--

Ken Snell
<MS ACCESS MVP>

Jeff Harbin said:
I think my problem is that I've got Group By in the Date field and it's
Click to expand...
Summing all the data for a particular date. I can't figure out how to
change the query to allow the user to determine the Month/Year without Date
in the query. What should the setting be rather than Group By so that it
doesn't segregate by day?
 
As requested the SQL for the query is below.

////code starts///
SELECT [SnD Data].Line, [SnD Data].DownTimeCategory, Sum([SnD Data].DTHours) AS DTHours, [SnD Data].DataDat
FROM [SnD Data
GROUP BY [SnD Data].Line, [SnD Data].DownTimeCategory, [SnD Data].DataDat
HAVING ((([SnD Data].DownTimeCategory) Is Not Null) AND ((Month([DataDate]))=[Please enter month (NUMBERS ONLY)]) AND ((Year(Date()))=[Please enter year (Example 2004)]))
//////code ends////

----- Ken Snell wrote: ----

Post the SQL of the query. That will make it easier for everyone t
understand what you have right now

--

Ken Snel
<MS ACCESS MVP

Jeff Harbin said:
I think my problem is that I've got Group By in the Date field and it'
Click to expand...
Summing all the data for a particular date. I can't figure out how t
change the query to allow the user to determine the Month/Year without Dat
in the query. What should the setting be rather than Group By so that i
doesn't segregate by day
 
Jeff,

I agree with your earlier disquiet about using the name of an existing
field as an alias. I would definitely change it to something like:
Sum([SnD Data].DTHours) AS TotalHours

What I am having difficulty in understanding is why you have the
DataDate field in the query at all... what purpose is it supposed to
perform? If you just remove it from the query, do you get the data you
need? If not, can you explain maybe with an example of how you want the
query to work.

Another anomaly is this section: Year(Date())=[Please enter year
(Example 2004)]
Should this be Year([DataDate])? Year(Date()) is a fixed value and
therefore doesn't really make sense for it to be selectable.

--
Steve Schapel, Microsoft Access MVP

Jeff said:
As requested the SQL for the query is below..

////code starts////
SELECT [SnD Data].Line, [SnD Data].DownTimeCategory, Sum([SnD Data].DTHours) AS DTHours, [SnD Data].DataDate
FROM [SnD Data]
GROUP BY [SnD Data].Line, [SnD Data].DownTimeCategory, [SnD Data].DataDate
HAVING ((([SnD Data].DownTimeCategory) Is Not Null) AND ((Month([DataDate]))=[Please enter month (NUMBERS ONLY)]) AND ((Year(Date()))=[Please enter year (Example 2004)]));
//////code ends/////

----- Ken Snell wrote: -----

Post the SQL of the query. That will make it easier for everyone to
understand what you have right now.

--

Ken Snell
<MS ACCESS MVP>

Jeff Harbin said:
I think my problem is that I've got Group By in the Date field and it's
Click to expand...
Summing all the data for a particular date. I can't figure out how to
change the query to allow the user to determine the Month/Year without Date
in the query. What should the setting be rather than Group By so that it
doesn't segregate by day?
Click to expand...
 
Back
Top