Union All Query?

[LMagee]

I haven't used acces in a while, but I remember being able
to use a statement to return information from two tables
where some fields may be blank. I think it was a Union
ALL statement, but I'm not sure, nor am I sure of the
syntax. I'm trying to return actual amounts from one
table and budgeted amount from anouther table. Currently
if the actual is null the budget amount doesn't show
either and vice-versa. I want all amounts to show unless
both budget and actual are null.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try LEFT JOINing the 2 tables. In the SELECT clause put the "left"
table column that always has the value. E.g.:

SELECT L.AccountNo, L.Amount, B.Budget
FROM AccountDetail As L LEFT JOIN Budget As B
ON L.AccountNo = B.AccountNo

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJke3IechKqOuFEgEQKvDwCfZQ3lpLBtsbaQwEkin4HGgMrZgokAoKOV
jcZ054uOeBMUN2DvalcN6SSN
=OsM+
-----END PGP SIGNATURE-----

 

[lmagee]

There isn'ta table that always a table with a value.
Example

Table 1-actual
Salaries 500
Bonus 200
Vacation 100
Cell Phone null
Postage 25

Table 2 - budget

Salaries 525
Bonus null
Vacation 100
Cell Phone 50
Postage null

Resulting Table Desired:
Expense, Actual, budget, variance
Salaries,500,525,(25)
Bonus, 200, null,200
Vacation, 100,100,0
Cell Phone null, 50,50
Postage, 25, null,(25)

Sometimes we budget itms that have no expense, and
sometimes we expense items when there as no budget. But
for variance analysis we need both items

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT A.Expense,
Sum(A.Actual) As ActualSum,
Sum(B.Budget) As BudgetSum,
Sum(B.Budget) - Sum(A.Actual) As Variance
FROM Actual As A LEFT JOIN Budget As B
ON A.Expense = B.Expense
WHERE <criteria>
GROUP BY A.Expense

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJlDX4echKqOuFEgEQL7DACdGjmOYxMec/cwNBRL+h6bMklH8HQAn1r6
+Fknoh/mffju+XOLaxfeoxiR
=LGkw
-----END PGP SIGNATURE-----

 

[Guest]

I have the same question as below and I have been trying to use this example in my data, without success. I have already summed my actuals and budgets and put them into their own tables. I have been able to get Actuals without Budgets but I still have Budgets without Actuals that I am not getting.

Here is the query I'm working with. This particular dept and Org have a Budget without Actuals and Actuals without a Budget in some GL Accts.

SELECT [Oracle Actuals by Month].Org, [Oracle Actuals by Month].Dept, [Oracle Actuals by Month].[GL Acct], [Oracle budget by Month].[Jun-04] AS Budget, [Oracle Actuals by Month].JunTotal AS Actual
FROM [Oracle Actuals by Month] LEFT JOIN [Oracle budget by Month] ON ([Oracle Actuals by Month].Org = [Oracle budget by Month].ORG) AND ([Oracle Actuals by Month].Dept = [Oracle budget by Month].DEP) AND ([Oracle Actuals by Month].[GL Acct] = [Oracle budget by Month].ACCT)
WHERE ((([Oracle Actuals by Month].Org)=30) AND (([Oracle Actuals by Month].Dept)=130));

I Appreciate any help you can give.

Cheers,
Rogers

 

[Gary Walter]

Hi KKerig,

It sounds like you want to UNION (no "ALL")
the query below with second like query
where tables are reversed on the LEFT JOIN.

Plus you will have issues with filtering
on an outer join.

One method....

Create a query that filters for specific
records from [Oracle Actuals by Month] :
(say it is called "qryActuals")

SELECT
A.Org,
A.Dept,
A.[GL Acct],
A.JunTotal AS Actual
FROM [Oracle Actuals by Month] AS A
WHERE
(((A.Org)=30)
AND
((A.Dept)=130));

Then create second query that filters for
specific records from [Oracle budget by Month]
(say it is called "qryBudget")

SELECT
B.Org,
B.Dept,
B.[GL Acct],
B.[Jun-04] AS Budget
FROM [Oracle budget by Month] As B
WHERE
(((B.ORG)=30)
AND
((B.DEP)=130));


Then use this query in a "frustrated outer join" UNION query.

SELECT
A1.Org,
A1.Dept,
A1.[GL Acct],
A1.Actual
B1.Budget,
FROM
qryActuals AS A1
LEFT JOIN
qryBudget As B1
ON
(A1.Org = B1.ORG)
AND
(A1.Dept = B1.DEP)
AND
(A1.[GL Acct] = B1.ACCT)
UNION
SELECT
A2.Org,
A2.Dept,
A2.[GL Acct],
A2.Actual
B2. Budget,
FROM
qryBudget As B2
LEFT JOIN
qryActuals AS A2
ON
( B2.ORG = A2.Org )
AND
( B2.DEP = A2.Dept )
AND
( B2.ACCT = A2.[GL Acct] );

Of course...I could be wrong...

Good luck,

Gary Walter

I have the same question as below and I have been trying to use this example in my

data, without success. I have already summed my actuals and budgets and put them
into their own tables. I have been able to get Actuals without Budgets but I still
have Budgets without Actuals that I am not getting.

Here is the query I'm working with. This particular dept and Org have a Budget

without Actuals and Actuals without a Budget in some GL Accts.

SELECT [Oracle Actuals by Month].Org, [Oracle Actuals by Month].Dept, [Oracle

Actuals by Month].[GL Acct], [Oracle budget by Month].[Jun-04] AS Budget, [Oracle
Actuals by Month].JunTotal AS Actual

FROM [Oracle Actuals by Month] LEFT JOIN [Oracle budget by Month] ON ([Oracle

Actuals by Month].Org = [Oracle budget by Month].ORG) AND ([Oracle Actuals by
Month].Dept = [Oracle budget by Month].DEP) AND ([Oracle Actuals by Month].[GL Acct]
= [Oracle budget by Month].ACCT)

WHERE ((([Oracle Actuals by Month].Org)=30) AND (([Oracle Actuals by Month].Dept)=130));

I Appreciate any help you can give.

Cheers,
Rogers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT A.Expense,
Sum(A.Actual) As ActualSum,
Sum(B.Budget) As BudgetSum,
Sum(B.Budget) - Sum(A.Actual) As Variance
FROM Actual As A LEFT JOIN Budget As B
ON A.Expense = B.Expense
WHERE <criteria>
GROUP BY A.Expense

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJlDX4echKqOuFEgEQL7DACdGjmOYxMec/cwNBRL+h6bMklH8HQAn1r6
+Fknoh/mffju+XOLaxfeoxiR
=LGkw
-----END PGP SIGNATURE-----