UNION rpt??

[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;

 

[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

 

[Pieter Wijnen]

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

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

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;

 

[Pieter Wijnen]

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

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

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;

--