Full OUter Join Help Needed

[walemly]

I have a trial balance master table (TB Master) with the following
fields:

Period (text, YYMM format)
CoNo
AcctNo
AcctName
Balance

This table contains trial balance records for multiple periods. As each
new period passes, I append that month's records to the master table.

I'm trying to create a query that will do the following:

1. Prompt the user (via a parameter) for the month to report on
2. Automatically calculate the previous calendar year end period using
the value provided in 1.
3. Match the records for the two different periods on CoNo and AcctNo
4. Display the balance for the prior year end, the period reported on,
and the difference between the two.

I've worked out the steps using the QBE to join TB Master to itself and
to calculate the prior year end period from the parameter period and
display and calculate the desired balances. My problem is that there
are some additional accounts during the prior year end that are no
longer present in the current period reported on in the parameter. I
know I have to use a full outer join in order to present all the sought
records but I've been unable to display anything other than the records
that are present in both periods in the full outer joins I've attempted
so far. I feel like what I want is doable, I just can't get the SQL
where I need it to be. Any help with tweaking my SQL code below or in
advising me how I need to tweak it will be greatly appreciated.

Alan

SELECT [TB Master].Period, [TB Master].CoNo, [TB Master].AcctNo, [TB
Master].AcctName, [TB Master_1]![Balance] AS PrevYEBal, [TB
Master]![Balance] AS CurMonBal, Nz([TB Master]![Balance])-Nz([TB
Master_1]![Balance]) AS BalDiff
FROM [TB Master] LEFT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master].Period)=[Enter month to report (YYMM):]) AND (([TB
Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12"))
UNION SELECT [TB Master].Period, [TB Master_1].CoNo, [TB
Master_1].AcctNo, [TB Master_1].AcctName, [TB Master_1]![Balance] AS
PrevYEBal, [TB Master]![Balance] AS CurMonBal, Nz([TB
Master]![Balance])-Nz([TB Master_1]![Balance]) AS BalDiff
FROM [TB Master] RIGHT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master].Period)=[Enter month to report (YYMM):]) AND (([TB
Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12"))
ORDER BY CoNo, AcctNo;

 

[TomHinkle]

I've worked with databases for 10 years... Never heard of a 'full' outer
join. you're going to have to have a couple layers of joins.

The best approach (IMHO) would be to get a list of the ID's you want to
search on.. (make a cartesian product, then add distinct, Cartesian product
would be the table from this year and last year, with no join between them).

From that distinct list, join to each table***

(* when I say table, I'm really meaning recordset, whatever you are using be
it table or query)

other than that, you might try a union query.
or have to 'make' a temp table and append recs from each period (** I don't
like doing that)

I have a trial balance master table (TB Master) with the following
fields:

Period (text, YYMM format)
CoNo
AcctNo
AcctName
Balance

This table contains trial balance records for multiple periods. As each
new period passes, I append that month's records to the master table.

I'm trying to create a query that will do the following:

1. Prompt the user (via a parameter) for the month to report on
2. Automatically calculate the previous calendar year end period using
the value provided in 1.
3. Match the records for the two different periods on CoNo and AcctNo
4. Display the balance for the prior year end, the period reported on,
and the difference between the two.

I've worked out the steps using the QBE to join TB Master to itself and
to calculate the prior year end period from the parameter period and
display and calculate the desired balances. My problem is that there
are some additional accounts during the prior year end that are no
longer present in the current period reported on in the parameter. I
know I have to use a full outer join in order to present all the sought
records but I've been unable to display anything other than the records
that are present in both periods in the full outer joins I've attempted
so far. I feel like what I want is doable, I just can't get the SQL
where I need it to be. Any help with tweaking my SQL code below or in
advising me how I need to tweak it will be greatly appreciated.

Alan

SELECT [TB Master].Period, [TB Master].CoNo, [TB Master].AcctNo, [TB
Master].AcctName, [TB Master_1]![Balance] AS PrevYEBal, [TB
Master]![Balance] AS CurMonBal, Nz([TB Master]![Balance])-Nz([TB
Master_1]![Balance]) AS BalDiff
FROM [TB Master] LEFT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master].Period)=[Enter month to report (YYMM):]) AND (([TB
Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12"))
UNION SELECT [TB Master].Period, [TB Master_1].CoNo, [TB
Master_1].AcctNo, [TB Master_1].AcctName, [TB Master_1]![Balance] AS
PrevYEBal, [TB Master]![Balance] AS CurMonBal, Nz([TB
Master]![Balance])-Nz([TB Master_1]![Balance]) AS BalDiff
FROM [TB Master] RIGHT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master].Period)=[Enter month to report (YYMM):]) AND (([TB
Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12"))
ORDER BY CoNo, AcctNo;

 

[walemly]

Tom, thanks for your reply. FYI, a full outer join is a join that
outputs all records from the left join table whether or not there are
matches in the right join table as well as all records from the right
join table whether or not there are matches in the left join table. MS
Access does not support full outer joins natively but one can do a
workaround with a union query to deliver the same results. My problem
is that I'm trying to do a full outer join when all my records are
housed in the same master table and the results are behaving like an
inner join instead of a full outer join (i.e., I'm only getting output
for records that match in both recordsets).

I suspect that what I'm attempting somehow violates database logic and
cannot be done but I'm hoping someone who works with full outer joins
in Access can either confirm this or tell me how to do what I'm
seeking.

Alan

 

[John Vinson]

I suspect that what I'm attempting somehow violates database logic and
cannot be done but I'm hoping someone who works with full outer joins
in Access can either confirm this or tell me how to do what I'm
seeking.

You're not doing anything wrong.

Access (JET to be exact) does not support full outer joins. It's
simply not a valid SQL expression in the JET dialect of SQL.

The getaround is to use a UNION query to combine a Left Outer Join
query with a Right Outer Join query.

John W. Vinson[MVP]

 

[walemly]

Thanks for your input, John. I'm aware that Access does not support
full outer joins and you have to use a union workaround. Unfortunately,
none of the workarounds I've attempted thus far (see SQL in my original
post) have worked. They have behaved like inner rather than full outer
joins. I thought that may be due to the fact that I'm working with one
master table and trying to fully outer join a recordset from that table
with the table itself. Can you provide a suggested solution using my
actual SQL as modifed? Thanks.

Alan

 

[John Vinson]

Thanks for your input, John. I'm aware that Access does not support
full outer joins and you have to use a union workaround. Unfortunately,
none of the workarounds I've attempted thus far (see SQL in my original
post) have worked. They have behaved like inner rather than full outer
joins. I thought that may be due to the fact that I'm working with one
master table and trying to fully outer join a recordset from that table
with the table itself. Can you provide a suggested solution using my
actual SQL as modifed? Thanks.

Alan

Please repost your SQL (it's fallen off my downloaded messages and I
really don't want to go Google for it... sorry!)

John W. Vinson[MVP]

 

[walemly]

Here it is. I've changed it slightly from the original post. Regardless
of what I do, I can't get all the records I need. Thanks for any
assistance you can offer.

Alan

SELECT [TB Master].Period, [TB Master].CoNo, [TB Master].AcctNo, [TB
Master].AcctName, [TB Master_1]![BalDrCr] AS PrevYEBal, [TB
Master]![BalDrCr] AS CurMonBal, Nz([TB Master]![BalDrCr])-Nz([TB
Master_1]![BalDrCr]) AS BalDiff
FROM [TB Master] LEFT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master].Period)=[Enter month to report (YYMM):]) AND (([TB
Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12")
UNION ALL SELECT [TB Master].Period, [TB Master_1].CoNo, [TB
Master_1].AcctNo, [TB Master_1].AcctName, [TB Master_1]![BalDrCr] AS
PrevYEBal, [TB Master]![BalDrCr] AS CurMonBal, Nz([TB
Master]![BalDrCr])-Nz([TB Master_1]![BalDrCr]) AS BalDiff
FROM [TB Master] RIGHT JOIN [TB Master] AS [TB Master_1] ON ([TB
Master].AcctNo = [TB Master_1].AcctNo) AND ([TB Master].CoNo = [TB
Master_1].CoNo)
WHERE (([TB Master_1].Period)=Format(Val(Left([Enter month to report
(YYMM):],2))-1,"00") & "12") AND ([TB Master].Period=[Enter month to
report (YYMM):]) AND [TB Master].CoNo is null
ORDER BY CoNo, AcctNo;