Sigurd-
Without knowing the structure of your tables, I would suggest that you could use a DSum in the query to get the total of all previous months.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Mar 31, 2017, at 10:10 PM, Sigurd Andersen sigurd@solbakkn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have a set of queries that take records with employee hours worked,
calculate how much their time is charged for a client (using a separate
table of pay rates that have different rates for each employee for each
fiscal year), then totals all the payments for each month for each
project to which hours are charged.
Each project has a maximum amount that can be charged - so if there are
hours worked that would take the total (from the inception of the
project) over this amount, the project is not charged for anything over
the project maximum.
So I want to calculate, for each project's monthly total, the sum of all
the previous month's charges so I'll know whether that month went over
the maximum or not.
I have this calculation working in a monthly Invoice report (using, in a
footer, =Sum() of a value in the detail section and the starting and
ending dates for the invoice period). How can I do the equivalent in a
query that includes all projects for all the months in which work was done?
Each row of the desired query would have the total chargeable for a
given month and as a separate value the total chargeable for ALL
previous months, something like this:
Project ID, total for Jan 2017, total for all months before Jan 2017
Project ID, total for Feb 2017, total for all months before Feb 2017
Project ID, total for Mar 2017, total for all months before Mar 2017
I'd be grateful for any suggestions as to how to approach this.
--
Sigurd Andersen
St. Johnsbury, VT 05819
------------------------------------
Posted by: Sigurd Andersen <sigurd@solbakkn.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
calculate how much their time is charged for a client (using a separate
table of pay rates that have different rates for each employee for each
fiscal year), then totals all the payments for each month for each
project to which hours are charged.
Each project has a maximum amount that can be charged - so if there are
hours worked that would take the total (from the inception of the
project) over this amount, the project is not charged for anything over
the project maximum.
So I want to calculate, for each project's monthly total, the sum of all
the previous month's charges so I'll know whether that month went over
the maximum or not.
I have this calculation working in a monthly Invoice report (using, in a
footer, =Sum() of a value in the detail section and the starting and
ending dates for the invoice period). How can I do the equivalent in a
query that includes all projects for all the months in which work was done?
Each row of the desired query would have the total chargeable for a
given month and as a separate value the total chargeable for ALL
previous months, something like this:
Project ID, total for Jan 2017, total for all months before Jan 2017
Project ID, total for Feb 2017, total for all months before Feb 2017
Project ID, total for Mar 2017, total for all months before Mar 2017
I'd be grateful for any suggestions as to how to approach this.
--
Sigurd Andersen
St. Johnsbury, VT 05819
------------------------------------
Posted by: Sigurd Andersen <sigurd@solbakkn.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar