Very rusty at queries...

[andy_dyer]

Hi,

I am trying to form a join of two queries, one showing budget and one showing actual...

the two queries themselves work fine when i join them no matter how i change the join settings I cannot get them to show correctly i.e. for a single cost centre and cost heading have one line showing budget and actual next to each other...

The SQL of my current query is:

SELECT qryBudget.[Budget Owner], qryBudget.Site, qryBudget.[Cost Center Number], qryBudget.[Cost Center Name], qryActual.[Cost Heading], qryBudget.[Budget Month 1], qryActual.[Actual Month 1], qryBudget.[Budget Month 2], qryActual.[Actual Month 2], qryBudget.[Budget Month 3], qryActual.[Actual Month 3], qryBudget.[Budget Month 4], qryActual.[Actual Month 4], qryBudget.[Budget Month 5], qryActual.[Actual Month 5], qryBudget.[Budget Month 6], qryActual.[Actual Month 6], qryBudget.[Budget Month 7], qryActual.[Actual Month 7], qryBudget.[Budget Month 8], qryActual.[Actual Month 8], qryBudget.[Budget Month 9], qryActual.[Actual Month 9], qryBudget.[Budget Month 10], qryActual.[Actual Month 10], qryBudget.[Budget Month 11], qryActual.[Actual Month 11], qryBudget.[Budget Month 12], qryActual.[Actual Month 12], qryBudget.[Total Budget], qryActual.[Total Actual]
FROM qryActual LEFT JOIN qryBudget ON qryActual.[Cost Heading] = qryBudget.[Cost Heading]
GROUP BY qryBudget.[Budget Owner], qryBudget.Site, qryBudget.[Cost Center Number], qryBudget.[Cost Center Name], qryActual.[Cost Heading], qryBudget.[Budget Month 1], qryActual.[Actual Month 1], qryBudget.[Budget Month 2], qryActual.[Actual Month 2], qryBudget.[Budget Month 3], qryActual.[Actual Month 3], qryBudget.[Budget Month 4], qryActual.[Actual Month 4], qryBudget.[Budget Month 5], qryActual.[Actual Month 5], qryBudget.[Budget Month 6], qryActual.[Actual Month 6], qryBudget.[Budget Month 7], qryActual.[Actual Month 7], qryBudget.[Budget Month 8], qryActual.[Actual Month 8], qryBudget.[Budget Month 9], qryActual.[Actual Month 9], qryBudget.[Budget Month 10], qryActual.[Actual Month 10], qryBudget.[Budget Month 11], qryActual.[Actual Month 11], qryBudget.[Budget Month 12], qryActual.[Actual Month 12], qryBudget.[Total Budget], qryActual.[Total Actual];

Any assistance in what i am missing would be great!

 

[andy_dyer]

Hi - just figured it out needed to add more joins in and not just change the type of join!


SELECT qryBudget.[Budget Owner], qryBudget.Site, qryBudget.[Cost Center Number], qryBudget.[Cost Center Name], qryActual.[Cost Heading], qryBudget.[Budget Month 1], qryActual.[Actual Month 1], [Budget Month 1]-[Actual Month 1] AS [Month 1 Variance], qryBudget.[Budget Month 2], qryActual.[Actual Month 2], [Budget Month 2]-[Actual Month 2] AS [Month 2 Variance], qryBudget.[Budget Month 3], qryActual.[Actual Month 3], [Budget Month 3]-[Actual Month 3] AS [Month 3 Variance], qryBudget.[Budget Month 4], qryActual.[Actual Month 4], [Budget Month 4]-[Actual Month 4] AS [Month 4 Variance], qryBudget.[Budget Month 5], qryActual.[Actual Month 5], [Budget Month 5]-[Actual Month 5] AS [Month 5 Variance], qryBudget.[Budget Month 6], qryActual.[Actual Month 6], [Budget Month 6]-[Actual Month 6] AS [Month 6 Variance], qryBudget.[Budget Month 7], qryActual.[Actual Month 7], [Budget Month 7]-[Actual Month 7] AS [Month 7 Variance], qryBudget.[Budget Month 8], qryActual.[Actual Month 8], [Budget Month 8]-[Actual Month 8] AS [Month 8 Variance], qryBudget.[Budget Month 9], qryActual.[Actual Month 9], [Budget Month 9]-[Actual Month 9] AS [Month 9 Variance], qryBudget.[Budget Month 10], qryActual.[Actual Month 10], [Budget Month 10]-[Actual Month 10] AS [Month 10 Variance], qryBudget.[Budget Month 11], qryActual.[Actual Month 11], [Budget Month 11]-[Actual Month 11] AS [Month 11 Variance], qryBudget.[Budget Month 12], qryActual.[Actual Month 12], [Budget Month 12]-[Actual Month 12] AS [Month 12 Variance], qryBudget.[Total Budget], qryActual.[Total Actual], [Total Budget]-[Total Actual] AS [Total Variance]
FROM qryActual LEFT JOIN qryBudget ON (qryActual.[Cost Center Number] = qryBudget.[Cost Center Number]) AND (qryActual.[Cost Center Name] = qryBudget.[Cost Center Name]) AND (qryActual.[Cost Heading] = qryBudget.[Cost Heading])
GROUP BY qryBudget.[Budget Owner], qryBudget.Site, qryBudget.[Cost Center Number], qryBudget.[Cost Center Name], qryActual.[Cost Heading], qryBudget.[Budget Month 1], qryActual.[Actual Month 1], qryBudget.[Budget Month 2], qryActual.[Actual Month 2], qryBudget.[Budget Month 3], qryActual.[Actual Month 3], qryBudget.[Budget Month 4], qryActual.[Actual Month 4], qryBudget.[Budget Month 5], qryActual.[Actual Month 5], qryBudget.[Budget Month 6], qryActual.[Actual Month 6], qryBudget.[Budget Month 7], qryActual.[Actual Month 7], qryBudget.[Budget Month 8], qryActual.[Actual Month 8], qryBudget.[Budget Month 9], qryActual.[Actual Month 9], qryBudget.[Budget Month 10], qryActual.[Actual Month 10], qryBudget.[Budget Month 11], qryActual.[Actual Month 11], qryBudget.[Budget Month 12], qryActual.[Actual Month 12], qryBudget.[Total Budget], qryActual.[Total Actual];