DAvg Function

[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

 

[John Vinson]

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

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.

 

[John Vinson]

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.

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.

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.

If you can clarify this alias issue we might be able to progress.

 

[Martin Heal]

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

 

[John Vinson]

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.

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.