Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

UNION rpt??

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

Tags:
  1. lmv

    lmv
    Expand Collapse
    Guest

    I have 2 reports (code included) I can get them both to work seperately but I
    need a report that will combine the info. and then return a difference in a
    calculated unbound column any suggestions. I don't know how to put 2 reports
    together. When I try it should return 189 records 1 for each category with
    the budget amt the cost (amt used even if it is 0 in either the budget or
    cost) PLEASE HELP I have been working on this for a week!

    SELECT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    qryCategoryLookup.MainCatName
    FROM qryCategoryLookup INNER JOIN [EST_S-84a] ON
    qryCategoryLookup.CategoryID = [EST_S-84a].Category
    ORDER BY [EST_S-84a].Category;
    ----------

    SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    Sum(qryPurchaseOrderRpt.ExtendedPrice) AS SumOfExtendedPrice
    FROM qryPurchaseOrderRpt
    GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID;
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh Union Make Special Visit To Houses Of Parliament To Showcase Their Community Work - Breaking News Jul 30, 2016
    Heritage India's Union Sports Ministry To Promote Gatka History of Sikhism Oct 23, 2013
    Hi-Tech Unites the World's Sikhs in Daily Communion Sikh Sikhi Sikhism Jun 29, 2013
    The Holy Union of Anand Karaj New to Sikhism Mar 16, 2013
    General Union with God (BoKSD 6) Hard Talk Dec 4, 2012

  3. Linc

    Linc
    Expand Collapse
    Guest

    You need to use UNION ALL instead of INNER JOIN

    Example:
    SELECT * FROM TableA UNION ALL SELECT * FROM TableB

    Basically, you are joining two SELECT statements using UNION ALL. Any
    additional info, such as ORDER BY, should be on the first SELECT statement.
    As far as I know,this will only work using the same number of fields of the
    same datatype.

    Hope this helps.
    Chris


    "lmv" wrote:

    > I have 2 reports (code included) I can get them both to work seperately but I
    > need a report that will combine the info. and then return a difference in a
    > calculated unbound column any suggestions. I don't know how to put 2 reports
    > together. When I try it should return 189 records 1 for each category with
    > the budget amt the cost (amt used even if it is 0 in either the budget or
    > cost) PLEASE HELP I have been working on this for a week!
    >
    > SELECT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    > qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    > qryCategoryLookup.MainCatName
    > FROM qryCategoryLookup INNER JOIN [EST_S-84a] ON
    > qryCategoryLookup.CategoryID = [EST_S-84a].Category
    > ORDER BY [EST_S-84a].Category;
    > ----------
    >
    > SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    > Sum(qryPurchaseOrderRpt.ExtendedPrice) AS SumOfExtendedPrice
    > FROM qryPurchaseOrderRpt
    > GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID;
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You need the same number of fields & compatible types

    ie
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will work
    Also
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BNumField,BNumField FROM BTABLE Will work

    But
    SELECT ANumField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will NOT work

    Also you can get the final result sorted by using
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will work
    ORDER BY 1 (You can use the Column number instead of name in an order by
    clause)



    HTH

    Pieter

    "Linc" <Linc@discussions.microsoft.com> wrote in message
    news:D148A47B-6F72-47E3-B9E8-6590F49F7F60@microsoft.com...
    > You need to use UNION ALL instead of INNER JOIN
    >
    > Example:
    > SELECT * FROM TableA UNION ALL SELECT * FROM TableB
    >
    > Basically, you are joining two SELECT statements using UNION ALL. Any
    > additional info, such as ORDER BY, should be on the first SELECT
    > statement.
    > As far as I know,this will only work using the same number of fields of
    > the
    > same datatype.
    >
    > Hope this helps.
    > Chris
    >
    >
    > "lmv" wrote:
    >
    >> I have 2 reports (code included) I can get them both to work seperately
    >> but I
    >> need a report that will combine the info. and then return a difference in
    >> a
    >> calculated unbound column any suggestions. I don't know how to put 2
    >> reports
    >> together. When I try it should return 189 records 1 for each category
    >> with
    >> the budget amt the cost (amt used even if it is 0 in either the budget or
    >> cost) PLEASE HELP I have been working on this for a week!
    >>
    >> SELECT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    >> qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    >> qryCategoryLookup.MainCatName
    >> FROM qryCategoryLookup INNER JOIN [EST_S-84a] ON
    >> qryCategoryLookup.CategoryID = [EST_S-84a].Category
    >> ORDER BY [EST_S-84a].Category;
    >> ----------
    >>
    >> SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    >> Sum(qryPurchaseOrderRpt.ExtendedPrice) AS SumOfExtendedPrice
    >> FROM qryPurchaseOrderRpt
    >> GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID;
    >>
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You need the same number of fields & compatible types

    ie
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will work
    Also
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BNumField,BNumField FROM BTABLE Will work

    But
    SELECT ANumField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will NOT work

    Also you can get the final result sorted by using
    SELECT ATextField, ANumField FROM ATABLE
    UNION ALL
    SELECT BTextField,BNumField FROM BTABLE Will work
    ORDER BY 1 (You can use the Column number instead of name in an order by
    clause)



    HTH

    Pieter

    "Linc" <Linc@discussions.microsoft.com> wrote in message
    news:D148A47B-6F72-47E3-B9E8-6590F49F7F60@microsoft.com...
    > You need to use UNION ALL instead of INNER JOIN
    >
    > Example:
    > SELECT * FROM TableA UNION ALL SELECT * FROM TableB
    >
    > Basically, you are joining two SELECT statements using UNION ALL. Any
    > additional info, such as ORDER BY, should be on the first SELECT
    > statement.
    > As far as I know,this will only work using the same number of fields of
    > the
    > same datatype.
    >
    > Hope this helps.
    > Chris
    >
    >
    > "lmv" wrote:
    >
    >> I have 2 reports (code included) I can get them both to work seperately
    >> but I
    >> need a report that will combine the info. and then return a difference in
    >> a
    >> calculated unbound column any suggestions. I don't know how to put 2
    >> reports
    >> together. When I try it should return 189 records 1 for each category
    >> with
    >> the budget amt the cost (amt used even if it is 0 in either the budget or
    >> cost) PLEASE HELP I have been working on this for a week!
    >>
    >> SELECT [EST_S-84a].Category, [EST_S-84a].LEBAEst_Cost,
    >> qryCategoryLookup.CategoryName, qryCategoryLookup.MainCatID,
    >> qryCategoryLookup.MainCatName
    >> FROM qryCategoryLookup INNER JOIN [EST_S-84a] ON
    >> qryCategoryLookup.CategoryID = [EST_S-84a].Category
    >> ORDER BY [EST_S-84a].Category;
    >> ----------
    >>
    >> SELECT qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID,
    >> Sum(qryPurchaseOrderRpt.ExtendedPrice) AS SumOfExtendedPrice
    >> FROM qryPurchaseOrderRpt
    >> GROUP BY qryPurchaseOrderRpt.ProjectID, qryPurchaseOrderRpt.CategoryID;
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page