Summing from either a table or a query

[Ty Tyler]

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

Note: I would like to total by operator

 

[Klatuu]

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

 

[Ty Tyler]

This looks alittle over my head buit i will try to figure it out.
Thanks

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Klatuu]

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP

This looks alittle over my head buit i will try to figure it out.
Thanks

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP

This looks alittle over my head buit i will try to figure it out.
Thanks

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Klatuu]

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &

Me.txtRunEnd & "#"

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Klatuu]

No, it goes in an event procedure in VBA. Are you using a command button to
run the reports? Do you have text boxes on your form to select the start and
end dates to include in the report? If so, please post the names of the
controls you are using.
Then I can show you exactly how the code should look.
--
Dave Hargis, Microsoft Access MVP

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"


:

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

Well my friend,
I have slipped deep into the twilight zone. I simply dbl click the report
icon and the questions for the start and end date come up. I dont have a form
for the query and the report is only reflecting what the query is doing from
the table. Maybe I need a class huh?

No, it goes in an event procedure in VBA. Are you using a command button to
run the reports? Do you have text boxes on your form to select the start and
end dates to include in the report? If so, please post the names of the
controls you are using.
Then I can show you exactly how the code should look.
--
Dave Hargis, Microsoft Access MVP

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"


:

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Klatuu]

Okay. Well, then there is not much you can do about it, then. For a good
application, users should not be working directly with the database objects.
That is, like what you describe where you go to the reports pane of the
database wiindow and click on a report.

Users should only be able to access functionality of the application through
forms you create.
--
Dave Hargis, Microsoft Access MVP

Well my friend,
I have slipped deep into the twilight zone. I simply dbl click the report
icon and the questions for the start and end date come up. I dont have a form
for the query and the report is only reflecting what the query is doing from
the table. Maybe I need a class huh?

No, it goes in an event procedure in VBA. Are you using a command button to
run the reports? Do you have text boxes on your form to select the start and
end dates to include in the report? If so, please post the names of the
controls you are using.
Then I can show you exactly how the code should look.
--
Dave Hargis, Microsoft Access MVP

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

:

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"


:

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

Ok I use a form for the data entry and you say I should have a form for the
report and the query as well? Is there a place on MS.com that can teach me?
Is this advanced information. I have really been interested but my company in
general isn't
Point me aND i WILL GO THERE. aND THANKS FOR ALL YOUR HELP.
Ty

Okay. Well, then there is not much you can do about it, then. For a good
application, users should not be working directly with the database objects.
That is, like what you describe where you go to the reports pane of the
database wiindow and click on a report.

Users should only be able to access functionality of the application through
forms you create.
--
Dave Hargis, Microsoft Access MVP

Well my friend,
I have slipped deep into the twilight zone. I simply dbl click the report
icon and the questions for the start and end date come up. I dont have a form
for the query and the report is only reflecting what the query is doing from
the table. Maybe I need a class huh?

No, it goes in an event procedure in VBA. Are you using a command button to
run the reports? Do you have text boxes on your form to select the start and
end dates to include in the report? If so, please post the names of the
controls you are using.
Then I can show you exactly how the code should look.
--
Dave Hargis, Microsoft Access MVP


:

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

:

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"


:

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Klatuu]

You don't need a form for the query.
You could either create a new form to run the report or you could just put
the two text boxes and the command button on your current form to run the
report.
I don't have a specific site I can refer you to, but I will be happy to help
you get it done.

Do you want to run the report from the current data entry form or do you
want to run it from a new form?
Also, how are you opening the data entry form?
--
Dave Hargis, Microsoft Access MVP

Ok I use a form for the data entry and you say I should have a form for the
report and the query as well? Is there a place on MS.com that can teach me?
Is this advanced information. I have really been interested but my company in
general isn't
Point me aND i WILL GO THERE. aND THANKS FOR ALL YOUR HELP.
Ty

Okay. Well, then there is not much you can do about it, then. For a good
application, users should not be working directly with the database objects.
That is, like what you describe where you go to the reports pane of the
database wiindow and click on a report.

Users should only be able to access functionality of the application through
forms you create.
--
Dave Hargis, Microsoft Access MVP

Well my friend,
I have slipped deep into the twilight zone. I simply dbl click the report
icon and the questions for the start and end date come up. I dont have a form
for the query and the report is only reflecting what the query is doing from
the table. Maybe I need a class huh?

:

No, it goes in an event procedure in VBA. Are you using a command button to
run the reports? Do you have text boxes on your form to select the start and
end dates to include in the report? If so, please post the names of the
controls you are using.
Then I can show you exactly how the code should look.
--
Dave Hargis, Microsoft Access MVP


:

I could not get it to work. Does the expression goes into the query Field
box? I tried it in the criteria box and it did not like it. The only date
criteria I have was: Between [start date] and [end date].
Also, is the example you sent inclusive of spaces and caps? Does it make a
difference?
I am starting to think I should be using a pivot table but I didn't want the
limitations on the data.
I wish i could show you what I have....
Ty

:

the names I use are for example only. You should substitute your actual names.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Should I be copying what you posted only using the proper field names?
example :
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"


:

Feel free to post back if you have further questions.
--
Dave Hargis, Microsoft Access MVP


:

This looks alittle over my head buit i will try to figure it out.
Thanks

:

If you are using this query to produce an Access report, take any filtering
you have in the query that relates to the date out of the query. Then you
can filter it by and date range you want to include.

For a daily report you could use:
strWhere = "[Run Date] = #" & Me.txtRunStart & "#"

For a weekly report you could use:
strWhere = "[Run Date] BETWEEN #" & Me.txtRunStart & "# AND #" &
Me.txtRunEnd & "#"

Then use the Where condition of the OpenReport method to filter the report:

Docmd.OpenReport "RunReport", acViewPreview, , strWhere

--
Dave Hargis, Microsoft Access MVP


:

Hi all and thanks in advance.
I have a table of production data that is recording run date, press id,
operator name, shift id, start count, end count, good (net) copy count, hours
worked and various fields of down time items. I have built a query that will
do the math for a gross copy count (ending count from starting count), total
waste (net from gross) and average waste per job.
This works very well for a daily report.
What I can't figure out is how to sum those calculations for a weekly report
as it still lists every record that is within the date parameters asked in
the query.

 

[Ty Tyler]

First: Thanks! Second: Before we go further, I want to go back to the basic
question I started with. My report will not aggregate(?) the data under the
op name field for consecutive days (records) of production., which is ok if
I am only posting one days worth of production. When I open the data base
the input form appears. To run the daily report, as I said, I dbl click the
report object and the textbox (or is it a combo box) asks for a start date
and an end date (2 different boxes). I believe you are saying is not the
smartest way to do it but I dont know another way. I as I grow in this
program I realize how intensive it really is.
So back to my report: The query is doing the calculations for waste count
and percentage and gross copy, and this is where I get stuck. The aggregate
functions of summing more than one day or production for each operator on one
report.
Whew! I am willing to start over if you would like.
Ty