Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Help on Grouping for output one particular value

    Hello,

    Can someone give me some assistance on grouping on 1 particular value in a query? I tried to use the “Group By” in the Total, but it did not work. What am I missing?

    I have 2 project numbers (12345 and 54321) and 10 work orders (5 wo10097331xx and 5 10097600xx) associated to those projects.

    Examples:

    Project: 12345
    wo100973310
    wo100973311
    wo100973312
    wo100973313
    wo100973314

    Project: 12345
    wo100976000
    wo100976001
    wo100976002
    wo100976003
    wo100976004

    I would like for my query to only display the two projects numbers.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You haven't said what "did not work" means, but if you just want the two project numbers, that has to be the only field with Group By in the Totals row.
    Paul

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Hi Paul,

    Sorry. I would like all of the data to output for the two projects. Right now, I am getting 10 work orders for the two projects as output.

    I added the Group By in the query, but it adds the Group By in all of the fields.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What do you want for the other fields? You either have to drop the other fields from the query or change Group By to Max or whatever.
    Paul

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure (but not certain) that I understand what you want.

    Back in the days when we used to chisel reports onto clay tablets, this was called "Group Indicated" reporting. The data in the report was sorted by N columns (the right-most was typically a date). The first N-1 columns of data only printed on the first row of a group. This made the groups easy to spot on the report output.

    I don't know of a really easy way to produce exactly what you've described, but I can get so close that it should not matter.

    1. Sort your report format on the N columns (apparently project number, then work order number).
    2. Build groups as needed, but be sure to build one that is one column shy of the level of detail that is important to you (in this case at your Project level).
    3. Put the project name, label, etc. in the group header for the project
    4. Add any counts, totals, etc. in the project footer.
    5. Include any detail (like work order numbers) in the detail section.
    6. Print and verify your report.
    7. Make any changes if needed, then go back one step
    8. Go have a or other suitable reward!


    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2008
    Posts
    150
    I am working with a query. This is the current output I am receiving.

    Project WorkOrder
    12345 wo100973310
    12345 wo100973311
    12345 wo100973312
    12345 wo100973313
    12345 wo100973314
    54321 wo100976000
    54321 wo100976001
    54321 wo100976002
    54321 wo100976003
    54321 wo100976004

    I would like to see this output in the query.

    12345 wo100973310
    54321 wo100976000

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    So like I suggested

    SELECT Project, Min(WorkOrder) As FirstOrder
    FROM TableName
    GROUP BY Project
    Paul

Posting Permissions

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