Multicalculated Query Help

[Fuhgawz]

I have a table that has several rows of data.

I want to be able to take column 5 and sum them into one number. Then take column 6 and sum them into a number. i then want to take the two sums and divides c6 int0 c5 and receive an overall percentage.



Then I want to the overall percentage and divide it by the sum of c6 and store this result temporarily.

Then i want to take the next whole number percentage and get the difference between the current overall percentage.

Then i want to take this value and divide it out by the temporary value stored earlier and round this value up to the next whole number

Example: SUMC6 / SumC5 = Overall percentage . This i can calculate and get 52.9 From here is where I have issues.

I take 52.9 / 12359(SumC6) = .0042802 Then I I take 52.9 - 53 = .1 Then take .1 / .0042802 = 23.363394

How can I get this data to return back in a query? Im sure I havent provided enough information let me know how else I can help get this solved.

Thank you in advance.

[June7]

This would likely involve several queries.

Possibly a report would provide the output you want. Use report Sorting & Grouping features with aggregate calcs in footer sections.

[Fuhgawz]

Would having these queries update the results into a new table; then creating a macro to run these queries to run them work? Then running a query from the new table to retrieve the data work as well?

[June7]

Why would you want to save results into a table? Doing that will require DELETE and INSERT SQL actions and/or code (macro or VBA) to automate.

[Fuhgawz]

ok then this would go back to my original question if this is going to take multiple queries then how would I reference back to other results needed to return the information that I am looking to retireve?

[June7]

Queries, like tables, can be used in queries.

Queries can be nested (subqueries). Review: http://allenbrowne.com/subquery-01.html

[ssanfu]

What are you going to do with the result? Just display it so you can see the result?

Using queries, it would take 2 queries: the first query, a totals query, based on the table.
the second query, based on the totals query, to do the calculations.

Another option is to write a UDF (VBA). This would involve a record set (a totals query) to get the sums of the two fields, then do the calculations.
I prefer the UDF because I can control what happens a lot easier.

[Fuhgawz]

I have zipped up my table and added to the original post.

What i am looking to do is to add onto the first query or create a new query and calculate by Console what my current completion rate is and how achievements i need to earn (by console) to increase my completion percentage. As i mentioned in the OP the math posted there but as far as applying it to a SQL statement to retireve it/update it as I continue to update my table i am at a loss.

This is just a personal project for me as I wanted to track my own gaming data for my consoles.

[ssanfu]

The two fields you want to use in the calculations are:TtlAch & AchEarned ??

You have their field type as Text. They should be Number/Single, if (since) you are doing math on them.

[ssanfu]

You described how to do the math using C5 & C6.
Please explain again, but using actual field names.

[June7]

You already have a report started based on aggregate query. Remove the GroupBy on OverallComplete field.

Could probably base report on the raw table data and do the calcs you describe on the report. Set up grouping on the report and do aggregate calcs in group and report footer sections.

[Fuhgawz]

You described how to do the math using C5 & C6.
Please explain again, but using actual field names.

c5= TtlAch , c6, AchEarned

I correct both fields to numbers now.

[Fuhgawz]

You already have a report started based on aggregate query. Remove the GroupBy on OverallComplete field.

Could probably base report on the raw table data and do the calcs you describe on the report. Set up grouping on the report and do aggregate calcs in group and report footer sections.

The report I have was just me tinkering around. I have little knowledge about access honestly.

[ssanfu]

Basically, this is what I did:
Step 1: OverallPercentage =SumOfAchEarned / SumOfTtlAch

Step 2: Result1 =OverallPercentage / SumOfAchEarned

Step 3 NextInt = Int(OverallPercentage +1)

Step 4: Result2 = OverallPercentage - NextInt (and change sign)

Step 5 GameCompletionPct = Result2 / Result1

In the query, there is rounding performed to match your numbers.


See if this is what you are looking for:

[Fuhgawz]

Basically, this is what I did:
Step 1: OverallPercentage =SumOfAchEarned / SumOfTtlAch

Step 2: Result1 =OverallPercentage / SumOfAchEarned

Step 3 NextInt = Int(OverallPercentage +1)

Step 4: Result2 = OverallPercentage - NextInt (and change sign)

Step 5 GameCompletionPct = Result2 / Result1

In the query, there is rounding performed to match your numbers.


See if this is what you are looking for:

Thanks this was nearly what I was looking for. I made some slight modifications to capture exactly what I was looking for. However in Step 2 I have one issue that I cannot figure out how to resolve. If the NextInt > 100 then it should result in 100. everything else it should return the actual value. I'm sure this is an IF statement but I cannot get it to work correctly.

Also the reports were working fine when I first check them but now appear to not be working probably where I made changes to the Steps.

I attached the file below again if you or someone else can give a hand with the last issue I am having.

GameCompletion.zip