Union different table fields 2

[BasicBoy]

I have two tables - one for purchases and one for stores. They have mostly similar fields, but not completely.

I wish to list all transactions from the purchase and the stores tables in one grid. The item numbers and dates they have, refer to similar fields.

I have done the following query - which of course does not work :

UserGridData.Query = "Select maintrans.maintransno as mainno, maintrans.itemno as mainitemno, maintrans.date as maindate, maintrans.quantityordered as mainquantityordered, maintrans.quantitydelivered as mainquantitydelivered, maintrans.unitpriceexcl as mainrate, maintrans.centreno as maincentreno, maintrans.supplier as mainsupplier from maintrans UNION select storestrans.storetransno, storetrans.itemno, storestrans.date, storestrans.quantity, storestrans.itemsleft, storestrans.unitpriceexcl, storestrans.centreno, storestrans.supplier from storestrans order by maintrans.date where maintrans.itemno=" & CLng(fgAll.TextMatrix(fgRow, 0))

I think it is mainly my .. where .. and .. order by .. sections that are incorrect. How do I refer to where and order by in the two different tables to see that it only extracts the same item number from both tables and order by the dates given in both tables ?

Thanks

 

[PHV]

What about this ?
UserGridData.Query = "Select maintransno as mainno, itemno as mainitemno, [date] as maindate, quantityordered as mainquantityordered
, quantitydelivered as mainquantitydelivered, unitpriceexcl as mainrate, centreno as maincentreno, supplier as mainsupplier from mai
ntrans where itemno=" & CLng(fgAll.TextMatrix(fgRow, 0)) & " UNION select storetransno, itemno, [date], quantity, itemsleft, unitpri
ceexcl, centreno, supplier from storestrans where itemno=" & CLng(fgAll.TextMatrix(fgRow, 0)) & " order by 3"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jaxtell]

You want only rows that have the same item number in both tables? Can you return it as one row? I'm not really clear on exactly what you need. To me it sounds like a join makes more sense than a union. If you do need to use a union, these are some things to consider:

Column names should be identical in each section of the union (eg. SELECT maintrans.maintransno as transno .... UNION ... torestrans.sotrestrans as transno ...)

The WHERE will have to come after each individual section, or in a select statement wrapping the UNIONS

select * from (select ... from maintrans UNION select ... from storestrans) where ...

If you need to order each section of the union separately, you'll have to use the nested select statements.



-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[BasicBoy]

Thank you both for very valuable replies.

I cannot make a join as the transaction numbers (ID) may be the same in the two tables (referring to different item numbers) whilst the item numbers they refer to (another field in both tables) must be grouped on.
I do not know which field I must refer to when sorting - do I sort on the alias ?

Thanks

 

[jaxtell]

I think PHV has the right answer. Refer to the sorting column by number. ORDER by 3 in this case meaning sort by date.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[PHV]

Column names should be identical in each section of the union
Absolutly FALSE !
In fact, any column alias after the first UNION keyword is simply IGNORED.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BasicBoy]

Can you please take another look at this - .. too few parameters. Expected 1

"Select maintransno as transno, itemno as myitemno, [date] as mydate, quantityordered as quantity1, quantitydelivered as quantity2, unitpriceexcl as rate, centreno as costcentreno, supplier as supplierno from maintrans where itemno=" & ThisItemNo & " UNION select storetransno as transno, itemno as myitemno, [date] as mydate, quantity as quantity1, itemsleft as quantity2, unitpriceexcl as rate, centreno as costcentreno, supplier as supplierno from storestrans where itemno=" & ThisItemNo & " order by 2"

Thanks

 

[BasicBoy]

I have tried the one without the alias's in the second part as well - same problem.

 

[BasicBoy]

It was a spelling mistake - sorry - thank you very much for the help.