Re: [MS_AccessPros] Calculating total of all previous transactions

 

I looked more closely and now see that there's a missing quote before the first &ampersand. Looks like the query is working.

THANK YOU! I sometimes bumble around for ages with Access trying to get a piece of code-syntax just right.

Sigurd

On 4/3/2017 8:44 AM, Sigurd Andersen sigurd@solbakkn.com [MS_Access_Professionals] wrote:

 

I tried

Dsum("GPay","qryPmtEmpME","EMPayDt<# & [EMPayDt] & "# And AcctID=" & [AcctID])

because [TrDt] wasn't recognized in the earlier query - but whether I use [TrDt] or [EMPayDt] I get the error

"The expression you entered has an invalid date value." - with it highlighting the portion at the end of the expression,

# And AcctID= " & [AcctID])

Thoughts?

Thanks,
Sigurd

On 4/3/2017 6:26 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

 

Sigurd-

Try this:

DSum("GPay","qryPmtEmpME","EMPayDt< #" & [TrDt] & "# And AccountID = " & [AccountID])

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 

http://www.viescas.com/ 

(Paris, France)

On Apr 2, 2017, at 6:46 PM, Sigurd Andersen sigurd@solbakkn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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.

On 3/31/2017 4:32 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

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 

http://www.viescas.com/ 

(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  

--   --  Sigurd Andersen  St. Johnsbury, VT 05819  

--   --  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 (6)

---

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.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___