Building an expression to find avg price spent

  • Thread starter Thread starter Richard Champlin
  • Start date Start date
R

Richard Champlin

I am doing an exercise that requires me to produce a datasheet that shows
customers whose average price spent on a vacation was greater than X. I
tried building an expression as follows: Average Spend:Avg «Expr»
[Vacations]![Price] and I received a syntax error. Access inserted the
"«Expr»".

What have I done wrong?
Richard Champlin
 
If everything else is correct then this will work --
Average Spend:Avg([Vacations]![Price])
 
sounds like you're doing schoolwork. i don't want to do it for you, but i
can suggest that you read up on Totals queries in Access Help.

hth
 
I'm not doing schoolwork, I'm trying to learn Access to enhance my skills.
Sometimes another pair of eyes with more experience can point out where I
screwed up.
--
Richard Champlin



tina said:
sounds like you're doing schoolwork. i don't want to do it for you, but i
can suggest that you read up on Totals queries in Access Help.

hth


Richard Champlin said:
I am doing an exercise that requires me to produce a datasheet that shows
customers whose average price spent on a vacation was greater than X. I
tried building an expression as follows: Average Spend:Avg «Expr»
[Vacations]![Price] and I received a syntax error. Access inserted the
"«Expr»".

What have I done wrong?
Richard Champlin
Click to expand...
Click to expand...
 
Richard said:
I am doing an exercise that requires me to produce a datasheet that shows
customers whose average price spent on a vacation was greater than X. I
tried building an expression as follows: Average Spend:Avg «Expr»
[Vacations]![Price] and I received a syntax error. Access inserted the
"«Expr»".

What have I done wrong?
Richard Champlin
Click to expand...


You're probably using the Expression Builder - which is a great way to
find your way around the structure of Access and in particular how to
refer to various objects like forms and controls. However, it does
introduce "placeholders" for arguments and you need to replace them with
something meaningful. Everyone who has ever used the Expression Builder
has ended up puzzling over <<Expr>> at some point!

I just created a new database and linked to an existing back-end, then
created a query to produce roughly the same sort of query that you need.
Obviously, the table names won't mean much to you, but the structure might:

SELECT tbl_Customer.CustomerID, tbl_Customer.CustDisplayName,
Avg(tbl_WorkPeriods.Charge) AS AvgOfCharge
FROM tbl_Customer INNER JOIN tbl_WorkPeriods ON tbl_Customer.CustomerID
= tbl_WorkPeriods.ClientID
GROUP BY tbl_Customer.CustomerID, tbl_Customer.CustDisplayName
HAVING (((Avg(tbl_WorkPeriods.Charge))>50))
ORDER BY tbl_Customer.CustDisplayName;

This query displays an ordered list of those customers whose labour
charges for individual work-periods average more than 50 quid. The
"AVG" function is built into Jet SQL, which underlies Access. The query
was created using the Query Builder in Access 2003, turning on the
"totals" button (the Sigma) to enable "grouping" options. I used "group
by" and "avg" as appropriate.

You may also use a simpler query (no grouping or avg) and base a Report
on it (use the Wizard, and look for "Summary Options"). This can give
you averages in group footers - I don't think that's what you need here,
but it's another way of looking at your data.

Hope that helps,

Phil, London
 
Back
Top