I have a series of queries that I build on top of each other.
From multiple tables, I build the first query to calculate the proper pay rate for each record of employee hours applied to a particular account.
In the next query (qryPmtEmpME), I use the one above to replace the pay date with the end-of-month date for that record (so I can group in the next query by month).
Fields used by the next query include:
AcctID, EMPayDt, GPay (in order, used for the values mentioned below)
Some of the fields in the above query are used to get a total chargeable, by month, to each account (lumping all employee payments together).
That last query (qryPmtNCIC) has fields:
AcctID: ID for the account (Group By)
TrDt: Renamed end-of-month date for the grouped amounts (Group By)
SumTrans: Total of individual transactions for that account in that month (Sum)
What I want to calculate is the total of all transactions for all previous months for each account ID, to be include along with the above values, so I can compare the total from previous months plus the transaction total for the current month against the maximum chargeable to that account, so the account won't get charged cumulatively more than that max.
If I put a DSum in the last query (qryPmtNCIC), it would look something like
DSum("GPay","qryPmtEmpME","EMPayDt<TrDt") - but just this doesn't work because Access doesn't recognize TrDt (renamed value of EMPayDt), and I don't know how to build in the test for matching account numbers.
I suppose I need to condition to be something like "EMPayDt<Me.EMPayDt And AcctID=Me.AcctID"
Any help in how to set this up would be greatly appreciated.
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, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding 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
...
-- Sigurd Andersen St. Johnsbury, VT 05819
Posted by: Sigurd Andersen <sigurd@solbakkn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar