I tried to create a UNION command to make the team names group together, but I can't figure out the final piece of the puzzle for the bottom line. Here's what I wrote...
SELECT *, TeamName AS GroupingField FROM tblTeams
<WHAT GOES HERE???>
I also just tried a union using the Date as the group field, but screwed that up too.
Last attempt was to try using a sum of the data from the other fields, which then displays totals by just team name, but it won't do that AND only pull data from a certain date range (Currently> Between Date() And Date()-42 to give me a rolling 6 week report). It combines all data in the recordsource.
Please help me out and let me know what I am doing wrong. This one is giving me a Friday headache!
Sorry I wasnt clear enough. Let me try this approach.
My data is stored with about 2 years of data and new data entered daily. I am interested in the last 6 weeks, on a rolling scale. Like I want to see a combination of data from now back to 42 days ago at any given time I open the database.
when I combine my data, I group by date, and then by team. What I need is to further combine all the team info into one total line per date.
I have the stuff right to sort by only the last 6 weeks. What I cant figure out is within those is how to combine all the team names into only one line of data as a roll up of their last 6 weeks of production.
Lemme try to show another example
team a. 45
Team b. 35
team a. 35
team b. 40
thats what it looks like all the through report
what I want is this
team a. 80
team b. 75
I need all 6 weeks rolled up into the sum of each team, not each team listed for each date.
I thought I could write a union cde to group all the names together into its sum, but cant figure it out.