Full Joins And Null Fields

[flissworld]

OK Ill explain this as best as I can

I have two tables - I buy widgets in the hundreds but they are different sorts. I sell widgets of the same kind in different numbers and kinds.

I want a purchases and sales report all in one

i.e

purchaseName  PurchaseNo  PurchasePrice  SaleName  SaleNo  SalePrice
Widget1           100          100       Widget1     100      200
Widget2           100          200       Widget2      50      150
                                         Widget2      25      150
Widget3           100          300
Widget4           200          400       Widget4      50      100

You can see from the above how I want to format my report, i have managed to display all the data as above - the only issue being that if I sell part of the item multiple times it duplicates the purchase column - I really need the purcahse colums to be blank, here is what I have - i do hope someone can help

SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase INNER JOIN sales ON purchase.purchaseitem=sales.saleitem;
UNION ALL
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase LEFT JOIN sales ON purchase.purchaseitem=sales.saleitem
WHERE (((sales.saleitem) Is Null));
UNION ALL 
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase RIGHT JOIN sales ON purchase.purchaseitem = sales.saleitem
WHERE (((purchase.purchaseitem) Is Null));

[salimudheen]

Hey,

Better u create the report, with the groping.
ie, create group on PurchaseItem. In this header u set the lable and text control of the purchase item. And other controls labels only u set here and also u keep the other text controls in detail section.

Sorry for the confusion.

[Stewart Ross]

Hi. As Salimudheen says, you can use the grouping facilities on a report to group by the purchase details. If you do so you can either have a staggered layout for the report data by placing the purchase fields in the header of the group then using the detail section to show the sales fields, or do as you show in post 1 and list all fields in the detail section.

If you put all fields in the detail section you could use the Hide Duplicates property of the textboxes on the report to list only the first occurrence of each, but doing so sometimes does not achieve an effect that looks as good to the user if there are several textboxes involved. It is worth experimenting to find out.

-Stewart

[NeoPa]

I would say this request is too non-specific to have an answer.

Your implementation of an OUTER JOIN is innovative, though this could possibly have been more easily done with no JOIN but with a WHERE clause specifying only matching records be included.

Without a specified unique reference though there are many potential problems. You could avoid duplicates, but how could you tell if they were referring to the same actual item or not?

[NeoPa]

To drop duplicates, there is a DISTINCT (or DISTINCTROW) predicate for the SELECT clause. This maps to the UniqueValues and UniqueRecords properties in the QueryDef.

Again, be careful of this as it may be possible to lose duplicates that you really need to keep.