Results 1 to 9 of 9

Thread: Query help

  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Unanswered: Query help

    I'm working on project that deals with upc numbers along with a quantity sold and retail numbers and such. My problem is that I have the same upc number on multiple times, I need these upc numbers to only appear once and have the other corresponding data to add up and appear on that same line.

    Here is the code I am using:

    PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
    SELECT HIDATA60404.UPC, HIDATA60404.LONG_NAME, HIDATA60404.INV_SDESC, HIDATA60404.DEPARTMENT, HIDATA60404.CATEGORY, HIDATA60404.QSOLD, HIDATA60404.DSOLD, HIDATA60404.TOTALCOST, HIDATA60404.PROFIT, HIDATA60404.GPROFIT
    FROM HIDATA60404
    WHERE (((HIDATA60404.ELSDATE) Between [Beginning Date] And [Ending Date]) AND (([HIDATA60404]![ELSSTORE]) Like [store]) AND (([HIDATA60404]![CATEGORY])=[cat])) OR ((([store]) Is Null));

    Thanks for any help you can provide.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you looking for Sum() and GROUP BY ?

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    Honestly, I'm not sure where to go from here, It's been about 4-5 years since I've done any of this and I'm just trying to remember what I've done in the past. I thought it was somewhere along the lines of Select Distinct, but I have no clue if that's right.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you post a sample that shows around a dozen rows of data, and the corresponding result set that you'd like to see?

    -PatP

  5. #5
    Join Date
    Jun 2004
    Posts
    20
    Using the following code, I get 40 rows returned. Combining the results should make 17.

    SELECT HIDATA60404.UPC, HIDATA60404.LONG_NAME, HIDATA60404.INV_SDESC, HIDATA60404.DEPARTMENT, HIDATA60404.CATEGORY, HIDATA60404.QSOLD, HIDATA60404.DSOLD, HIDATA60404.TOTALCOST, HIDATA60404.PROFIT, HIDATA60404.GPROFIT
    FROM HIDATA60404
    WHERE ((([HIDATA60404]![ELSSTORE])="10" And ([HIDATA60404]![ELSSTORE])="10") AND (([HIDATA60404]![CATEGORY])="141" And ([HIDATA60404]![CATEGORY])="141") AND ([HIDATA60404]![ELSDATE]>#5/23/2004# And [HIDATA60404]![ELSDATE]<#5/27/2004#));

    I need the corresponding columns to total up with the single row i post.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't find the results that you wanted to see, so this is just a guess, but I'm reasonably sure that you want to group by the first column, and Sum() the numeric columns.

    -PatP

  7. #7
    Join Date
    Jun 2004
    Posts
    20

    Question

    I tried that. It groups the items together, but still shows up on multiple lines.

    This is frustrating.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If all you want is the "galactic total", just leave off the GROUP BY.

    -PatP

  9. #9
    Join Date
    Jun 2004
    Posts
    20
    I did end up getting this to work. I had been trying that all day, Thanks for all your help.

    Jeff

Posting Permissions

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