Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Fields in report to excel
- Thread starter Tango524
- Start date
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
Week Data1 Data2
GroupBy Avg Avg
or something like that.
Week Data1 Data2
GroupBy Avg Avg
or something like that.
- Thread starter
- #3
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.
=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.
- Thread starter
- #5
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!
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.
datachick10@hotmail.com
g
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.
datachick10@hotmail.com
g
- Thread starter
- #7
- Status
Similar threads
