DAvg Function

  • Thread starter Thread starter Martin Heal
  • Start date Start date
M

Martin Heal

I am trying to calculate an average price from a table of
transactions, where there is an effective price &
effective date for each transaction as well as an account
number and product.

What I want to find for each transaction the average price
paid by this account for this product in previous
transactions.

I have tried:

DAvg([tblTransactions].[Effective Price],"tblTransactions",
[tblTransactions].[Effective Date]<[MyTransactions].
[Effective Date])

Where [MyTransactions] is an alias of [tbltransactions]
but this just returns the price of that one record.

Maybe I should be trying to use a different function to
derive this value, but DAvg seemed to be the closest I
could find. I would be grateful for any suggestions.

TIA


Martin
 
DAvg([tblTransactions].[Effective Price],"tblTransactions",
[tblTransactions].[Effective Date]<[MyTransactions].
[Effective Date])
Click to expand...

Each of the three arguments to all of the Domain functions like DAvg
must be TEXT STRINGS. You're only passing the second as a text string!
In addition, Date/Time fields must be delimited by # characters. Try:

DAvg("[Effective Price]", "tblTransactions", "[Effective Date]<#" &
[MyTransactions].[Effective Date] & "#" )

I'm assuming that MyTransactions is the name of another table in the
Query and that it contains a field named Effective Date.
 
Thanks John,

I always wondered about when to use quotation marks in
these functions - Microsoft Help is a little ambiguous as
it shows examples without them.
Click to expand...

Well, the help is accurate. The point is that each of the three
domain-function arguments must be a Text String. This string might be
a string constant, e.g.

DLookUp("Field1", "Tablename", "[ID] = 123")

or it might be a String variable:

varFld = "Field1"
DLookUp(varFld, ...

or it might be a string created on the fly by concatenating constants,
variables, and form references:

DLookUp(varFld, "tablename", "[ID] = " & Me!txtID)

No matter how the string gets created, it must be a string
corresponding to a Fieldname for the first argument, the name of a
Table or a Query for the second, and a valid SQL WHERE clause (without
the word WHERE) for the third.
The table [MyTransactions] is an alias of the
[tblTransactions] table.
Click to expand...

How is the domain function supposed to know that? Where in the
expression do you define that alias?
I am trying to get for each
transaction, the average of previous prices paid for the
same product by the same account as the current record.

I fear that my approach of linking the two aliases of the
table by Account & Product and using the the effective
date from one compared to the same field in the alias
isn't achieving the results I want. In fact I can see from
the results that it isn't - I think it is averaging over
all transactions.
Click to expand...

If you can clarify this alias issue we might be able to progress.
 
Here is the SQL John:

SELECT tblTransactions.TransactionID, tblTransactions.
[Account No], tblTransactions.Fund, tblTransactions.
[Effective Date], tblTransactions.[Effective Price], DAvg
("[Effective Price]","tblTransactions","[Effective Date]
<#" & [Effective Date] & "#") AS [Average Price]
FROM tblTransactions
ORDER BY tblTransactions.[Account No],
tblTransactions.Fund, tblTransactions.[Effective Date];

Let me know if you need any further information.

Thanks


Martin
 
I am trying to get for each
transaction, the average of previous prices paid for the
same product by the same account as the current record.
Click to expand...

You need to include the account and the product in the criteria to
DAvg - something like:

SELECT tblTransactions.TransactionID, tblTransactions.
[Account No], tblTransactions.Fund, tblTransactions.
[Effective Date], tblTransactions.[Effective Price], DAvg
("[Effective Price]","tblTransactions","[Effective Date]
<#" & [Effective Date] & "# AND [Account No] = '" & [Account No] & "'
AND [Fund] = '" & [Fund] & "'") AS [Average Price]
FROM tblTransactions
ORDER BY tblTransactions.[Account No],
tblTransactions.Fund, tblTransactions.[Effective Date];

This assumes that Account No and Fund are both Text fields; leave off
the ' around them if not.
 
Back
Top