Re: Displaying columns from 2 independant tables

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/10/04

  • Next message: Harry J Nathan: "Help with Select statement" 
    Date: Fri, 10 Dec 2004 20:48:32 +0100

    On Fri, 10 Dec 2004 09:55:41 -0800, Wes wrote:

    >I have a table as follows:
    (snip)
    >I would like to sum the quantity of each code and staus
    >and display in tabular format as follows
    (snip)

    Hi Wes,

    If you can do the formatting at the client side (where it ought to be
    done), use this:

    SELECT Code, Status, SUM (Quantity)
    FROM MyTable
    GROUP BY Code, Status

    If you must do the formatting at the server side:

    SELECT Code,
             SUM (CASE WHEN Status = 'Dispatched' THEN Quantity ELSE 0 END) AS
    "Qty Dispatched",
             SUM (CASE WHEN Status = 'Awaiting Dispatch' THEN Quantity ELSE 0
    END) AS "Qty Awaiting Dispatch"
    FROM MyTable
    GROUP BY Code

    (both queries untested)

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: Harry J Nathan: "Help with Select statement"

    Relevant Pages

    • Re: Conditional Formatting And Running Sums
      ... > I have a report in which I am using a running sum to display the total ... formatting applied to it in the detail section. ...
      (microsoft.public.access.reports)
    • Re: performing calculations in the table
      ... is executed on exit from the data entry fields. ... >Hi Tony ... in the formatting you ... >> calculate sum for each row. ...
      (microsoft.public.word.vba.general)
    • Re: Conditional Formatting And Running Sums
      ... Formatting -- that'll work fine in reports. ... > I have a report in which I am using a running sum to display the total ...
      (microsoft.public.access.reports)
    • RE: sum of total hours not correct
      ... formatting may need to be fixed. ... Entry cells seem to be ... > I made a spreadsheet calculating how long a user is on line ... > Because this can go overnight, I make the sum with an IF-statement. ...
      (microsoft.public.excel.setup)
    • Re: Formatting in Text Boxes
      ... Dim myStr As String ... Dim iCtr As Long ... then add it to the sum. ... before formatting and so this works perfectly when the userform opens.... ...
      (microsoft.public.excel.programming)