Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24

    Unanswered: Adding extra row to a query

    Hi,

    I know this is probably really simple, and I'm just being really stupid, but......

    how can I force an extra row to the results of a group query?

    I have a query that provides a list of companies that have been used in the main table. They are grouped so that they only appear once. I want to use the results of the query to populate a combo box for filtering in a form. I want to be able to add the row "<All>" to the list, so it can be selected in the combo, and with a bit of code, remove the filter.

    Does this make sense???

    Any help greatly appreciated!

    Thanks,

    Qpid.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT va.client_ID, va.client_name
    FROM viewA va
    UNION ALL
    SELECT NULL, 'ALL'
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't think you can do it directly in your GROUP but you can feed a new query from your GROUP along the lines of:

    SELECT idGroup, strGroup FROM qryGroup UNION SELECT 0 as Nill, "All" as Fake FROM qryGroup ORDER BY idGroup;

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24
    Many thanks guys - managed it with:

    SELECT Company
    FROM qryAllOrdersGrouped
    GROUP BY Company

    UNION SELECT ALL "<All>" as Company
    FROM qryAllOrdersGrouped
    GROUP BY Company;

    Phew!
    Qpid.

Posting Permissions

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