How to Collect two fields from two different places

[azhar2006]

Hello Guys


I have a form that contains two subform
In the subform No. 1 there is a currency field
In the subform No. 2 there is a currency field
The question is
I want a model that combines independent currency field in a subform 1 and 2
And I will show queries of the subforms

subform No. 1
SELECT tblGoodsPurchased.GoodsPurchesedID, tblGoodsPurchased.OrderDateID, tblGoodsPurchased.GoodsID, tblGoodsPurchased.Qty, tblGoodsPurchased.Price, tblGoodsPurchased.Calibre, tblGoodsPurchased.WagWork, tblGoodsPurchased.Other, [Price]*[Qty]+[WagWork] AS Expr1, tblGoodsPurchased.Paid, [Expr1]-[Paid] AS Expr2
FROM tblGoodsPurchased;
-------------------------------------------------------------------------
subform No. 2
SELECT tblGroups.GroID, tblGroups.OrderdateID, tblGroups.TypeofSilverID, tblGroups.Mykmea, tblGroups.Calibre, tblGroups.Ounce, tblGroups.WagWork, tblGroups.Other, tblGroups.rebate, ([Ounce]/31.1) AS IOunce, [IOunce]*[Calibre] AS Igram, [WagWork]*[Mykmea] AS Expr2, [WagWork]+[Igram] AS IgmAndwag, ([IgmAndwag]*[mykmea]) AS Total, tblGroups.Paid, [totalAmount]-[Mykmea] AS mtbke, [Total]-[Paid] AS Expr1
FROM NamSelvir RIGHT JOIN tblGroups ON NamSelvir.ID = tblGroups.TypeofSilverID;

[orange]

For multiple subforms see these tutorials.
http://www.datapigtechnologies.com/f.../subform1.html
http://www.datapigtechnologies.com/f.../subform2.html

Good luck

[azhar2006]

Thank you very much
But this is not what I want
What I want is
Collection of fields query 1 query with fields of 2
But show me a message


SELECT tblGoodsPurchased.GoodsPurchesedID, tblGoodsPurchased.OrderDateID, tblGoodsPurchased.GoodsID, tblGoodsPurchased.Qty, tblGoodsPurchased.Price, tblGoodsPurchased.Calibre, tblGoodsPurchased.WagWork, tblGoodsPurchased.Other, [Price]*[Qty]+[WagWork] AS Expr1, tblGoodsPurchased.Paid, [Expr1]-[Paid] AS Expr2
FROM tblGoodsPurchased;

UNION ALL SELECT tblGroups.GroID, tblGroups.OrderdateID, tblGroups.TypeofSilverID, tblGroups.Mykmea, tblGroups.Calibre, tblGroups.Ounce, tblGroups.WagWork, tblGroups.Other, tblGroups.rebate, ([Ounce]/31.1) AS IOunce, [IOunce]*[Calibre] AS Igram, [WagWork]*[Mykmea] AS Expr2, [WagWork]+[Igram] AS IgmAndwag, ([IgmAndwag]*[mykmea]) AS Total, tblGroups.Paid, [totalAmount]-[Mykmea] AS mtbke, [Total]-[Paid] AS Expr1
FROM NamSelvir RIGHT JOIN tblGroups ON NamSelvir.ID = tblGroups.TypeofSilverID;

[orange]

The message is clear - the number of columns in the the selected tables of the UNION query do not match.

The ALL will include any duplicates.

see http://www.techonthenet.com/sql/union.php

[June7]

I count 11 fields in the first SELECT and 17 in the second.

The first SELECT line defines the field names so the aliases are ignored and not needed in the following SELECT lines. Use alias in the first SELECT.

Use Null as placeholder where there is not a corresponding field in one of the SELECT lines. For instance, not seeing the equivalent of TypeofSilverID in the first SELECT so put Null in the same position with field alias:

, Null AS TypeOfSilverID,