What's wrong with UNION in this case? (1 Viewer)
- Thread starter Unixaix
- Start date
June7
AWF VIP
- Local time
- Yesterday, 16:00
- Joined
- Mar 9, 2014
- Messages
- 5,470
Nothing. ProductID shows because ProdCode in PurchaseDetail has a lookup alias. The actual value saved is ProductID even though ProdCode displays. UNION query pulls actual value, not the alias. If you want ProdCode will have to join each SELECT to the Product table or join the UNION query to Product.
Why do you want to UNION this data?
Why do you want to UNION this data?
Last edited:
The fact that it exists at all is what is wrong.
When you have two tables that are essentially the same structure (PurchaseDetail/SaleDetail, Puchase/Sale) you did it wrong. When you do that you are storing a value that should be in a field in the table name (Purchase/Sale). Purchase and sale data should be in the same table, with a new field for the type (purchase/sale) that the transaction is.
You do that, you eliminate the need for this UNION hack to bring your data back together. It also makes getting CurrentInventory trivial (whcih I think is what you are moving toward with this UNION). You simply sum the Qty field of that new table I described.
You can do something similar with your Customers/EMployees/Suppliers table. They essentially have the same structure so all that data should go together with a new field to designate what role the person has.
When you have two tables that are essentially the same structure (PurchaseDetail/SaleDetail, Puchase/Sale) you did it wrong. When you do that you are storing a value that should be in a field in the table name (Purchase/Sale). Purchase and sale data should be in the same table, with a new field for the type (purchase/sale) that the transaction is.
You do that, you eliminate the need for this UNION hack to bring your data back together. It also makes getting CurrentInventory trivial (whcih I think is what you are moving toward with this UNION). You simply sum the Qty field of that new table I described.
You can do something similar with your Customers/EMployees/Suppliers table. They essentially have the same structure so all that data should go together with a new field to designate what role the person has.
