Can this be combined

[Dgavilanes]

Hi All,
Here I'm again, I have a query 1 that sums up each field to get a subtotal, the second query2 sums up query1 my question is can I get this two combined into one and get the same result, or is there a better way? what

Qry1

SELECT TempQry_Subtotal_Proj_HC.Type, TempQry_Subtotal_Proj_HC.[Investment Plan], Sum(TempQry_Subtotal_Proj_HC.[Original Budget]) AS [SumOfOriginal Budget], Sum(TempQry_Subtotal_Proj_HC.Budget) AS SumOfBudget, Sum(TempQry_Subtotal_Proj_HC.Commitments) AS SumOfCommitments, Sum(TempQry_Subtotal_Proj_HC.Obligations) AS SumOfObligations, Sum(TempQry_Subtotal_Proj_HC.Expenditures) AS SumOfExpenditures, Sum(TempQry_Subtotal_Proj_HC.[Funds Available]) AS [SumOfFunds Available], Sum(TempQry_Subtotal_Proj_HC.[Original vs Projected]) AS [SumOfOriginal vs Projected], Sum(TempQry_Subtotal_Proj_HC.[Actual vs Projections]) AS [SumOfActual vs Projections], Sum(TempQry_Subtotal_Proj_HC.[% Actual Vs Proj]) AS [SumOf% Actual Vs Proj], Sum(TempQry_Subtotal_Proj_HC.STOPLIGHT) AS SumOfSTOPLIGHT, Sum(TempQry_Subtotal_Proj_HC.July) AS SumOfJuly, Sum(TempQry_Subtotal_Proj_HC.August) AS SumOfAugust, Sum(TempQry_Subtotal_Proj_HC.September) AS SumOfSeptember, Sum(TempQry_Subtotal_Proj_HC.[1st Quarter (Actuals)]) AS [SumOf1st Quarter (Actuals)], Sum(TempQry_Subtotal_Proj_HC.[1st Quarter (Projected)]) AS [SumOf1st Quarter (Projected)], Sum(TempQry_Subtotal_Proj_HC.July1) AS SumOfJuly1, Sum(TempQry_Subtotal_Proj_HC.August1) AS SumOfAugust1, Sum(TempQry_Subtotal_Proj_HC.September1) AS SumOfSeptember1, Sum(TempQry_Subtotal_Proj_HC.October1) AS SumOfOctober1, Sum(TempQry_Subtotal_Proj_HC.November1) AS SumOfNovember1, Sum(TempQry_Subtotal_Proj_HC.December1) AS SumOfDecember1, Sum(TempQry_Subtotal_Proj_HC.January1) AS SumOfJanuary1, Sum(TempQry_Subtotal_Proj_HC.February1) AS SumOfFebruary1, Sum(TempQry_Subtotal_Proj_HC.March1) AS SumOfMarch1, Sum(TempQry_Subtotal_Proj_HC.April1) AS SumOfApril1, Sum(TempQry_Subtotal_Proj_HC.May1) AS SumOfMay1, Sum(TempQry_Subtotal_Proj_HC.June1) AS SumOfJune1, Sum(TempQry_Subtotal_Proj_HC.TOTAL) AS SumOfTOTAL
FROM TempQry_Subtotal_Proj_HC
GROUP BY TempQry_Subtotal_Proj_HC.Type, TempQry_Subtotal_Proj_HC.[Investment Plan]
ORDER BY TempQry_Subtotal_Proj_HC.Type, TempQry_Subtotal_Proj_HC.[Investment Plan];

Qry2
SELECT Query1.Type, Sum(Query1.[SumOfOriginal Budget]) AS [SumOfSumOfOriginal Budget], Sum(Query1.SumOfBudget) AS SumOfSumOfBudget, Sum(Query1.SumOfCommitments) AS SumOfSumOfCommitments, Sum(Query1.SumOfObligations) AS SumOfSumOfObligations, Sum(Query1.SumOfExpenditures) AS SumOfSumOfExpenditures, Sum(Query1.[SumOfFunds Available]) AS [SumOfSumOfFunds Available], Sum(Query1.[SumOfOriginal vs Projected]) AS [SumOfSumOfOriginal vs Projected], Sum(Query1.[SumOfActual vs Projections]) AS [SumOfSumOfActual vs Projections], Sum(Query1.[SumOf% Actual Vs Proj]) AS [SumOfSumOf% Actual Vs Proj], Sum(Query1.SumOfSTOPLIGHT) AS SumOfSumOfSTOPLIGHT, Sum(Query1.SumOfJuly) AS SumOfSumOfJuly, Sum(Query1.SumOfAugust) AS SumOfSumOfAugust, Sum(Query1.SumOfSeptember) AS SumOfSumOfSeptember, Sum(Query1.[SumOf1st Quarter (Actuals)]) AS [SumOfSumOf1st Quarter (Actuals)], Sum(Query1.[SumOf1st Quarter (Projected)]) AS [SumOfSumOf1st Quarter (Projected)], Sum(Query1.SumOfJuly1) AS SumOfSumOfJuly1, Sum(Query1.SumOfAugust1) AS SumOfSumOfAugust1, Sum(Query1.SumOfSeptember1) AS SumOfSumOfSeptember1, Sum(Query1.SumOfOctober1) AS SumOfSumOfOctober1, Sum(Query1.SumOfNovember1) AS SumOfSumOfNovember1, Sum(Query1.SumOfDecember1) AS SumOfSumOfDecember1, Sum(Query1.SumOfJanuary1) AS SumOfSumOfJanuary1, Sum(Query1.SumOfFebruary1) AS SumOfSumOfFebruary1, Sum(Query1.SumOfMarch1) AS SumOfSumOfMarch1, Sum(Query1.SumOfApril1) AS SumOfSumOfApril1, Sum(Query1.SumOfMay1) AS SumOfSumOfMay1, Sum(Query1.SumOfJune1) AS SumOfSumOfJune1, Sum(Query1.SumOfTOTAL) AS SumOfSumOfTOTAL
FROM Query1
GROUP BY Query1.Type;

Any suggestions would be breatly appreciated

Thanks

Dennis

 

[Galaxiom]

That is a scary looking query but it is a bit hard to advise without knowing more about your data and your goals.

Such complex queries often suggest problems with the underlying data structure.

All those Month fields suggests potential normalization issues in the data structure. If the orignal table has a set of fields by Month it is definitely denormalized.

If not then it would appear that you have Crosstabbed the data too early in the game (in the TempQry) where you probably should have been using GROUP BY on the months.

 

[Dgavilanes]

Hi,,
And thanks for responding, yes is a very scary,
The months you are refering are two separate fields altogether, the first group are projected expenses and the second set are actual expenses.

I was given the task of duplicating an excel budget analysis within the database, if this works now,then my next task , I will build it from scratch the right way. For now is good enough.

The excel file has a complex report were the first thing the Analyst do is to group it by two main fields and from there it gets subtotaled and the subtotal gets subtotaled again etc, etc. Its a very scary looking file

So In access I have three Qrys, a group Subtotal, then a total and now a grand total.

So my q? was is there a way to combined qry1 and 2 and ge the same result?

If it helps I can submitt the file so you can reviewt and make any suggestions? if possible

Thanks and have a nice day

Dennis

 

[boblarson]

So my q? was is there a way to combined qry1 and 2 and ge the same result?

Given the complexity of it, I would say - don't even try. Use the TWO queries.

 

[Dgavilanes]

Thank you

 

[boblarson]

Oh, and the other reason to just use the two is - spend as little amount of time possible to get this working and then use the time saved to work on a correctly normalized version.

 

[Galaxiom]

I exactly agree with Bob. Putting effort into the existing structure would be flogging a dead horse.