Is there anyway to download a field in a report to excel? I have average calcs by week in my report that I can't get from a query or a table download. Any thoughts would be great! Thanks!
You would have to do the calculation in a query and then export that. Write a Totals query that includes all the fields you want to export. If you need help then write back with your table structure. Without seeing your data, I'd guess the query would have
The only thing is that all my dates are daily entries (even many per day). The report was the only way I knew how to group it by week. If there is a way in the Query, could you go into detail of how to get the week grouping? Thanks a bunch!
what does the 'week' look like on the report? just an integer? I'm guessing you used the Report Wizard? if so, just look at the formula that the wizard put into the text box on the report and use that in a query:
=Format$([DateEntered],"ww"
look at help on "FORMAT function" and Totals Queries
so create a query based on your table
choose from the menu VIEW-TOTALS
in one field put the line i typed above (substitute your date field name for DateEntered) and leave the TOTALS row as GROUP BY.
then bring down whatever field you want to average on, and in TOTALS choose AVG.
This gives me a "Data Type Mismatch in Criteria Expression" error. I don't want to use Date Entered just to get one week's worth of data because I want the query to give me average productivity for ALL weeks included in my data tables. I guess I want the query to sort my dates by week like the reports sort by week. Here is an example of what I have:
Date Productivity
3/1/03 50
3/3/03 100
3/8/03 30
3/10/03 50
This is what I want to get from the query to create a chart.
Date Productivity
Week 1 (some #) 75
Week 2 40
Sorry if this is confusing. Thank you so much for your tips and help!
sorry-you are not confusing. I know what you want and i believe i've provided it.
what is the data type of your 'date' field?
what is your date field called?
what is the data type of your 'productivity' number? are they numbers or text?
what i have written will give you weekly averages of all your data, just like you wanted.
Field: "Week " & Format$([DateEntered],"ww"
Total: GROUP BY
Field: Productivity
Total: AVG
can you send me an excel file of your data table? i will concoct the query and send it back to you.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.