Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2014
    Posts
    3

    Unanswered: Grouping by Date and Team name

    Long time lurker, first time poster. I normally find what I need and don't need to add to the page count. Thanks for the great help thus far. Here's what I need:

    I am making a new database with multiple reports. One of the reports is a rolling 6 week results page. Got all the data I want from the query, but not the way I want it to look.

    Right now, when I run the query, it breaks down all the data by date, and then by team, and displays the results. The end result is something like this:

    2 Feb 14 Avengers XXX XXX XXX XXX
    Terminators XXX XXX XXX XXX
    3 Feb 14 Avengers XXX XXX XXX XXX
    Terminators XXX XXX XXX XXX

    What I need is for it to look like this:

    Avengers XXX XXX XXX
    Terminators XXX XXX XXX

    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
    UNION ALL
    <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!

    Thanks in advance for the assistance!

    -Craig

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    not really clear what you are trying to accomplish

    but from the limited facts you could either not group on the date but group on the team

    then sort by date

    or you could maybe look at a crosstab query that would pivot your data by team.

    In addition you could lose the grouping in the query and generate a report and group within the report
    Dale Houston, TX

  3. #3
    Join Date
    Feb 2014
    Posts
    3

    more info

    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

    1 feb
    team a. 45
    Team b. 35

    2 feb
    team a. 35
    team b. 40

    thats what it looks like all the through report

    what I want is this

    1-2 feb
    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.

    Hopefully this helps.

    Thanks again.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    what you need is a

    ok I think I have it and I am going to give you an example follow these instructions exactly

    create a table called importfile
    create 3 fields with their data type
    Team- text
    Widgets - number
    Orientation_Day - Date/Time - shortdate format

    then enter data for about 10 rows

    using 2 or 3 teams
    7 to 10 different dates Between 1/1/2012And 2/7/2014
    and 10 different widget amounts

    then in the sql view of a new query paste this sql statement

    SELECT Team, Sum(Widgets) AS SumOfWidgets
    FROM ImportFile
    WHERE Orientation_date Between #1/1/2012# And #2/7/2014#
    GROUP BY Team, Orientation_date

    then look at the data sheet view

    if it does help - go to the design view of the query and look how it is done

    those groupings and sums are done by clicking the summation button at the top of the screen in design mode.

    I hope this helps
    Dale Houston, TX

  5. #5
    Join Date
    Feb 2014
    Posts
    3
    Awesome. Thanks. I will check it out first thing Monday morning and let you know.

    Have a good weekend.

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Sounds like you need a cross tab

    go to query - create or new and choose the wizard
    have the wizard walk you through the process - if this is not what you want reply back and maybe I can look at a sample file for you.
    Dale Houston, TX

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •