Allocating Totals from another query, no join

[wageman3]

I am trying to allocate a total I calculated using the sum function in
another query with all the records in another query. When I try to bring
this single value into the query and calcuate off of it I get ambiguous join
errors. I do not have a common value between the two queries to create a
join. I just want to use this total as a plug number to allocate up.

Here is sort of what I had in mind.

Revenue Calculate Rev/TotalRev Other Query Amount Allocate Revenue
$100 .20 $540
($540*.20)
$200 .40 $540
($540*.40)
$200 .40 $540
($540*.40)

My basic problem is that I have a long list of departments that have an
estimated revenue number. In order to match the report with the financial
statements I need to allocate the actual revenue amount.

Thanks,

Greg

 

[John W. Vinson]

I am trying to allocate a total I calculated using the sum function in
another query with all the records in another query. When I try to bring
this single value into the query and calcuate off of it I get ambiguous join
errors. I do not have a common value between the two queries to create a
join. I just want to use this total as a plug number to allocate up.

Here is sort of what I had in mind.

Revenue Calculate Rev/TotalRev Other Query Amount Allocate Revenue
$100 .20 $540
($540*.20)
$200 .40 $540
($540*.40)
$200 .40 $540
($540*.40)

My basic problem is that I have a long list of departments that have an
estimated revenue number. In order to match the report with the financial
statements I need to allocate the actual revenue amount.

Thanks,

Greg

If the "other query" returns only one record, you should be able to do a
Cartesian join - just include the "other query" in the query window with NO
join line. It will be paired with every record in your main table.

If that's not working, please post the SQL view of the query.

John W. Vinson [MVP]

 

[wageman3]

SELECT [FRX Codes].[GL Exp Code], [FRX Codes].Deptcode, [DFR by
Dept].MappedCA, [FRX Codes].Suffix, [FRX Codes].[Exp Desc], [FRX
Codes].CMonth, [FRX Codes].CMonthBud, [FRX Codes].MVariance, [FRX
Codes].CYear, [FRX Codes].CYearBud, [FRX Codes].YVariance, [FRX
Codes].PriorYrMth, [FRX Codes].PriorYrYTD, [CYear]*[MappedCA] AS CYearDFR,
[CYear]+[CYearDFR] AS CYearNet, deptlist.[Dept Description], [DFR by
Dept].Indrevratio, [DFR by Dept].[ws6c CCR] AS totalccr, [DFR by
Dept].Indirect, [FRX Codes].SumOfCYear, [SUM Total CA].YearTotalCA
FROM [SUM Total CA], [FRX Codes] LEFT JOIN (deptlist RIGHT JOIN [DFR by
Dept] ON deptlist.Department = [DFR by Dept].MappedDepts) ON [FRX
Codes].Deptcode = [DFR by Dept].[DFR by GL Rev Dept_Deptcode]
WHERE ((([FRX Codes].Suffix)<="3399"));

[SUM Total CA].YearTotal CA is the single field I am trying to add to the
query for every record. I am getting the ambiguous outer join error.

Thanks,

Greg

 

[John W. Vinson]

SELECT [FRX Codes].[GL Exp Code], [FRX Codes].Deptcode, [DFR by
Dept].MappedCA, [FRX Codes].Suffix, [FRX Codes].[Exp Desc], [FRX
Codes].CMonth, [FRX Codes].CMonthBud, [FRX Codes].MVariance, [FRX
Codes].CYear, [FRX Codes].CYearBud, [FRX Codes].YVariance, [FRX
Codes].PriorYrMth, [FRX Codes].PriorYrYTD, [CYear]*[MappedCA] AS CYearDFR,
[CYear]+[CYearDFR] AS CYearNet, deptlist.[Dept Description], [DFR by
Dept].Indrevratio, [DFR by Dept].[ws6c CCR] AS totalccr, [DFR by
Dept].Indirect, [FRX Codes].SumOfCYear, [SUM Total CA].YearTotalCA
FROM [SUM Total CA], [FRX Codes] LEFT JOIN (deptlist RIGHT JOIN [DFR by
Dept] ON deptlist.Department = [DFR by Dept].MappedDepts) ON [FRX
Codes].Deptcode = [DFR by Dept].[DFR by GL Rev Dept_Deptcode]
WHERE ((([FRX Codes].Suffix)<="3399"));

[SUM Total CA].YearTotal CA is the single field I am trying to add to the
query for every record. I am getting the ambiguous outer join error.

Try creating and storing a Cartesian query on FRX Codes and [Sum Total CA] and
outer joining this query to the other tables; or, instead of including Sum
Total CA in the query at all, use

DLookUp("[YearTotalCA]", "[Sum Total CA]")

as a calculated field in the query. DLookUp is not very efficient but for a
one-row query it shouldn't slow things down too much.

You might even be able to replace [Sum Total CA] by a DSum() function call.

John W. Vinson [MVP]