Report/SELECT DISTINCTROW

[Dennis]

I have a report that uses 4 Tables that make-up a SELECT
DISTINCTROW query for this report. This budget report uses
3 of the 4 tables to supply the related budget data and
one imported table that supplies the budget's expended
data. Each table is linked. The problem I am having is
that only the records that match the budget line item
number from the expended table(which is all of them) show
up on the report. That means all revenue is not reported
and some budgeted line items where 0 is expended do not
show up on the report. The question I have is how can I
have all of the budget line items that have dollars
allocated show up in the report and all expenditures also
show up in the report. I can get one or the other but not
both. Thanks Dennis

 

[Michel Walsh]

Hi,

You would need a full outer join, not available under Jet. A possible work
around is to make a virtual table out of the budgetItems and Expenditures:


SELECT BudgetItemID AS f1 FROM bugetItems
UNION
SELECT ExpenditureID FROM expenditures


save it as q1 (or make it a table, if so, index the field).


Next, make the two outer joins:

FROM ( q1 LEFT JOIN budgetAmounts As b ON q1.f1=b=budgetItemId)
LEFT JOIN ExpendituresAmounts AS e ON q1.f1=e.expenditureId


You do not select anything from q1 (ie, q1 does not appear in the SELECT
clause), q1 just help you to make the full outer join.


Hoping it may help,
Vanderghast, Access MVP