value displayed repeatedly

[J.J.]

I have a table that tracks work hours from which I would like to extract the
total number of hours worked per person per asignment based on the work order
(OrderAutoID). I need to know how many hours were spent in the previous
month. While the following query comes up with the correct total, this total
shows repeatedly for each date from the selected month. How do I get just
the one total to display?

SELECT DSum("[NewHoursCompleted]","TrackHours","[OrderAutoID]=" &
[OrderAutoID]) AS MnthSum
FROM TrackHours
WHERE (((DateSerial(Year([date]),Month([date]),Day([date]))) Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())-1,0)));

Thank you

 

[Allen Browne]

You say you want the monthly total hours per person, so I assume your
TrackHours table has a field for the date worked, and a field for the
person.

You can group the data like this:

SELECT PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth,
Sum([NewHoursCompleted]) AS Hours
FROM TrackHours
GROUP BY PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth;

In query design view, you depress the Total button on the toolbar, and
Access gives you the GROUP BY clause.

Also, if you really do have a field named 'date', this can cause you grief.
It is a reserved word, and in some contexts Access can misinterpret it as
the system date. Better to rename the field to (say) WorkDate.

 

[J.J.]

Hi Allen,

I am probably missing something in your suggested methodology. Although I
copied your script, it didn't seem to work as I got an error message. "Syntax
error (missing operator) in query expression 'DateSerial(Year..."
Actually I have a PersonID in the TrackHours table but as one person could
work at several jobs, each person is also given a unique OrderAutoID when
assigned to a job as each job may not be site or cost specific. It is this
OrderAutoID that I need to track. One person may have worked at several jobs
tduring the month and so costs can be more accurately tracked. Therefore, in
your example, I did substitute OrderAutoID for PersonID. Yes there is also a
date field. Thank you

 

[Allen Browne]

Needs an extra bracket:
DateSerial(Year([date]), Month([date]), 1)) AS TheMonth,

 

[J.J.]

Sorry Allen.
I tried every combination of opening parenthesis to support the closing
parenthesis you show below. I can't get it to work. I don't see what role
the extra bracket serves. Thank you

Needs an extra bracket:
DateSerial(Year([date]), Month([date]), 1)) AS TheMonth,

This is your original statement

SELECT PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth,
Sum([NewHoursCompleted]) AS Hours
FROM TrackHours
GROUP BY PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth;

 

[Allen Browne]

Okay, we can't see your table here, and so you will need use the right
field/table names. We need something more specific than 'can't get it to
work.' Describe what happens: error? wrong results? no results? other?

The brackets must match: if you have 4 opening brackets and 3 closing ones,
it won't work.

Your original question indicated a table named TrackHours, with fields named
NewHoursCompleted, date, and OrderAutoID. You were using a DSum() on the
same table. We don't know why.

You also seem to have a field named 'date.' This is a reserved word, and in
some contexts Access will misunderstand it to mean the system date. This may
be why it's not working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry Allen.
I tried every combination of opening parenthesis to support the closing
parenthesis you show below. I can't get it to work. I don't see what role
the extra bracket serves. Thank you

Needs an extra bracket:
DateSerial(Year([date]), Month([date]), 1)) AS TheMonth,

This is your original statement

SELECT PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth,
Sum([NewHoursCompleted]) AS Hours
FROM TrackHours
GROUP BY PersonID,
DateSerial(Year([date]), Month([date]), 1) AS TheMonth;

 

[J.J.]

Thank you so much for taking the time to answer.
Maybe I am stating what you have already garnered but I want to try and make
myself as clear as possible so that you have a complete understanding of what
I am trying to do. The table you asked about, in addition to other data
contains the following fields [AutoOrderID], [date] and [NewHoursCompleted].
I know you aren't happy using [date] as a name and neither am I. However, I
have a myriad of forms, queries, reports, macros and routines already running
including matching archiving tables etc. As everything works and as it would
be several days work to change, I am forced to live (and learn) with my
mistake as a beginner. So far, the choice of [date] has not caused a coding
problem.
To recap, I am trying to total up the hours for each previous month per
AutoOrderID. Again to clarify, each assignment may not be specific to a job
site or one cost hence this ID instead of simply employeeID. All this means
is that some employees will have several monthly subtotals while some
employees, who remained at one work function, will have one monthly total.
Yjso way we can accurately bill back.
The result of this query will be used in a sub-report contained in a report
that provides details of each employee that shows their combined total hours
etc but, also, per the subreport, how many hours on each assignment
(AutoOrderID). Per my initial request, my code provided the right answer but
repeated it for every date the employee worked. I just want the one monthly
total per AutoOrderID.
I was on the wrong path per your code suggestion which didn't work. You
then provided a correction in which you added one closing bracket but no
corresponding opening bracket. The second closing bracket was added just in
front of AS when you provided the following line>>> DateSerial(Year([date]),
Month([date]), 1)) AS TheMonth,<<< to replace the earlier code. Obviously,
this created an error, as you just pointed out since the opening and closing
brackets must correspond in numbers. In the line above there is now one extra
closing parenthesis. As I can't get a clear handle on how this code works
and I can't open it in design view, my question was where in the full code is
the matching opening bracket to be placed?
I trust that I have cleared up any ambiquities. Again, thank you so much.

 

[Allen Browne]

JJ, please feel free to just ignore the suggestion I gave, as it did not
prove useful.

I understand that you are seeking to sum the hours per AutoOrderID. The
GROUP BY query that does a SUM of hours is the only thing that comes to
mind. I don't know what else to suggest. Perhaps someone else has an idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for taking the time to answer.
Maybe I am stating what you have already garnered but I want to try and
make
myself as clear as possible so that you have a complete understanding of
what
I am trying to do. The table you asked about, in addition to other data
contains the following fields [AutoOrderID], [date] and
[NewHoursCompleted].
I know you aren't happy using [date] as a name and neither am I. However,
I
have a myriad of forms, queries, reports, macros and routines already
running
including matching archiving tables etc. As everything works and as it
would
be several days work to change, I am forced to live (and learn) with my
mistake as a beginner. So far, the choice of [date] has not caused a
coding
problem.
To recap, I am trying to total up the hours for each previous month per
AutoOrderID. Again to clarify, each assignment may not be specific to a
job
site or one cost hence this ID instead of simply employeeID. All this
means
is that some employees will have several monthly subtotals while some
employees, who remained at one work function, will have one monthly total.
Yjso way we can accurately bill back.
The result of this query will be used in a sub-report contained in a
report
that provides details of each employee that shows their combined total
hours
etc but, also, per the subreport, how many hours on each assignment
(AutoOrderID). Per my initial request, my code provided the right answer
but
repeated it for every date the employee worked. I just want the one
monthly
total per AutoOrderID.
I was on the wrong path per your code suggestion which didn't work. You
then provided a correction in which you added one closing bracket but no
corresponding opening bracket. The second closing bracket was added just
in
front of AS when you provided the following line>>>
DateSerial(Year([date]),
Month([date]), 1)) AS TheMonth,<<< to replace the earlier code.
Obviously,
this created an error, as you just pointed out since the opening and
closing
brackets must correspond in numbers. In the line above there is now one
extra
closing parenthesis. As I can't get a clear handle on how this code works
and I can't open it in design view, my question was where in the full code
is
the matching opening bracket to be placed?
I trust that I have cleared up any ambiquities. Again, thank you so much.

 

[J.J.]

Tahnks Allen. I have resubmitted my question under "Group date on employeeID."
By making the subject more generic, somone should have an idea. Again,
thank you vey much for your kind assistance. If you would like, I will post
the workable response when received.
Regards J.J.

JJ, please feel free to just ignore the suggestion I gave, as it did not
prove useful.

I understand that you are seeking to sum the hours per AutoOrderID. The
GROUP BY query that does a SUM of hours is the only thing that comes to
mind. I don't know what else to suggest. Perhaps someone else has an idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for taking the time to answer.
Maybe I am stating what you have already garnered but I want to try and
make
myself as clear as possible so that you have a complete understanding of
what
I am trying to do. The table you asked about, in addition to other data
contains the following fields [AutoOrderID], [date] and
[NewHoursCompleted].
I know you aren't happy using [date] as a name and neither am I. However,
I
have a myriad of forms, queries, reports, macros and routines already
running
including matching archiving tables etc. As everything works and as it
would
be several days work to change, I am forced to live (and learn) with my
mistake as a beginner. So far, the choice of [date] has not caused a
coding
problem.
To recap, I am trying to total up the hours for each previous month per
AutoOrderID. Again to clarify, each assignment may not be specific to a
job
site or one cost hence this ID instead of simply employeeID. All this
means
is that some employees will have several monthly subtotals while some
employees, who remained at one work function, will have one monthly total.
Yjso way we can accurately bill back.
The result of this query will be used in a sub-report contained in a
report
that provides details of each employee that shows their combined total
hours
etc but, also, per the subreport, how many hours on each assignment
(AutoOrderID). Per my initial request, my code provided the right answer
but
repeated it for every date the employee worked. I just want the one
monthly
total per AutoOrderID.
I was on the wrong path per your code suggestion which didn't work. You
then provided a correction in which you added one closing bracket but no
corresponding opening bracket. The second closing bracket was added just
in
front of AS when you provided the following line>>>
DateSerial(Year([date]),
Month([date]), 1)) AS TheMonth,<<< to replace the earlier code.
Obviously,
this created an error, as you just pointed out since the opening and
closing
brackets must correspond in numbers. In the line above there is now one
extra
closing parenthesis. As I can't get a clear handle on how this code works
and I can't open it in design view, my question was where in the full code
is
the matching opening bracket to be placed?
I trust that I have cleared up any ambiquities. Again, thank you so much.

 

[J.J.]

I finally figured it out myself where I was going wrong. The Group by was
the key.

Here's the revised yet simple code:
SELECT Sum([NewHoursCompleted]) AS MnthSum FROM TrackHours
WHERE (((DateSerial(Year([date]),Month([date]),Day([date]))) Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())-1,0)))
GROUP BY [OrderAutoID];

Again thank you

JJ, please feel free to just ignore the suggestion I gave, as it did not
prove useful.

I understand that you are seeking to sum the hours per AutoOrderID. The
GROUP BY query that does a SUM of hours is the only thing that comes to
mind. I don't know what else to suggest. Perhaps someone else has an idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for taking the time to answer.
Maybe I am stating what you have already garnered but I want to try and
make
myself as clear as possible so that you have a complete understanding of
what
I am trying to do. The table you asked about, in addition to other data
contains the following fields [AutoOrderID], [date] and
[NewHoursCompleted].
I know you aren't happy using [date] as a name and neither am I. However,
I
have a myriad of forms, queries, reports, macros and routines already
running
including matching archiving tables etc. As everything works and as it
would
be several days work to change, I am forced to live (and learn) with my
mistake as a beginner. So far, the choice of [date] has not caused a
coding
problem.
To recap, I am trying to total up the hours for each previous month per
AutoOrderID. Again to clarify, each assignment may not be specific to a
job
site or one cost hence this ID instead of simply employeeID. All this
means
is that some employees will have several monthly subtotals while some
employees, who remained at one work function, will have one monthly total.
Yjso way we can accurately bill back.
The result of this query will be used in a sub-report contained in a
report
that provides details of each employee that shows their combined total
hours
etc but, also, per the subreport, how many hours on each assignment
(AutoOrderID). Per my initial request, my code provided the right answer
but
repeated it for every date the employee worked. I just want the one
monthly
total per AutoOrderID.
I was on the wrong path per your code suggestion which didn't work. You
then provided a correction in which you added one closing bracket but no
corresponding opening bracket. The second closing bracket was added just
in
front of AS when you provided the following line>>>
DateSerial(Year([date]),
Month([date]), 1)) AS TheMonth,<<< to replace the earlier code.
Obviously,
this created an error, as you just pointed out since the opening and
closing
brackets must correspond in numbers. In the line above there is now one
extra
closing parenthesis. As I can't get a clear handle on how this code works
and I can't open it in design view, my question was where in the full code
is
the matching opening bracket to be placed?
I trust that I have cleared up any ambiquities. Again, thank you so much.