Access 2010 datasheet total fields blank

Gurus,

I have a query in Access 2010 which is the datasource for a form. I'm trying to get average fields to work in the form but they stubbornly remain blank. I've tried it several ways:


Using datasheet view and turning on the Totals row. I set all three columns totals to Average, but they stay blank.
datasheetUsing continuous form view, with fields in the footer. One example column field is Qty, so my formula in the footer is =avg([qty])... You can see my three footer fields glumly remaining blank.
continuous

As far as I can tell, all three columns are numbers, not text.

My source is a query that does do grouping and summarizing (Totals are turned on).
LVL 5
Michaelj42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

XGISCommented:
Hello Michaelj42....try the link below ..its access 2003 but that should not matter....

Aggregate Form Values

if you cant get it to work you could supply a cut down version with some dummy data, which will turn this issue around faster
0
Anthony PerkinsCommented:
I have a query in Access 2010 which is the datasource for a form
...
Topics: Microsoft Access Database, MS SQL Server


Since this question does not appear to be related to MS SQL Server you may want to request that Topic be deleted.
0
Jeffrey CoachmanMIS LiasonCommented:
<My source is a query that does do grouping and summarizingMy source is a query that does do grouping and summarizing>
...Then this seems to be the issue, the Totals functions wants to see raw values, not summarized values.

just create a continuous form, then in the form Footer do your summaries in a textbox with a controlsource of: =avg(Hours)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Michaelj42Author Commented:
Here is a sample database with the data just in a table.
Database3.accdb
0
Michaelj42Author Commented:
Thanks Hoag2000, please see the db I uploaded. The form is unchanged, the data is in a table rather than the query, but I still get #ERROR (before I was getting nothing at all, blank fields)
0
Jeffrey CoachmanMIS LiasonCommented:
You sad the source was a query with Grouping...
All that is in this db is a table...?

The table summaries worked for me, and in your form, the summaries are at the "Bottom" of the form
Database3.accdb
0
Michaelj42Author Commented:
I tried stripping it away from to query to isolate where the problem lay so in the sample db the source is simply 1 table that I made from the query I'm using in the live db.

The summaries don't work in the footer of the continuous form where they show error, or in the Totals row if I switch to datasheet, where they are just blank.

I'll try to get a sample db that also incorporates the query.
0
Michaelj42Author Commented:
Here is a sample db that includes the query etc.
Database4.accdb
0
XGISCommented:
i will take a look for you..
0
XGISCommented:
this sort of thing?
Database4.accdb
0
Michaelj42Author Commented:
Thank you XGIS, I hadn't thought of using DAvg, though, in the end, I don't think this was the problem.

I think the issue was that, in my sample db I set the criteria on the ID to 26132, but in the live db, that is found a combo box of drawings. Since, in my testing, I didn't have the combo box loaded, it would ask for that parameter, I would type 26132 and the data would be shown.

However, in testing your solution, I loaded the form with the combo box and changed the criteria back to looking there, and it worked. I then switched my summary fields from DAvg back to Avg([field]) and it still worked.

Does this make sense? Could it have only been a matter of not having access to the criteria combo box at the right moment?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XGISCommented:
No worries Michaelj42,
maybe it was a binding issue based on type..
i did notice the examples show the order to be reversed ie field,qry vs qry,field....
its programming..anything is possible...most times it makes no sense but it works..and if it does that is awesome..
0
Michaelj42Author Commented:
I want to accept multiple solutions, awarding you points for your help, but my own final analysis as the more correct solutions since it does seem to have been a bind order issue rather than a formula issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.