Add percentage column to query

[arcaidius]

I have a query to sum total parts and total rejects. I need to add a column for the reject percent. I tried this formatted as percent:
Field: Reject %: [Rejected]/[Total Threaded]
Total: Sum

Its giving me these numbers:
Total threaded Total Reject Reject%
586 50 161.22% should be 8.53%

Here is the SQL:

SELECT [New Qry].Machine, Sum([New Qry].[Total Threaded]) AS [SumOfTotal Threaded], Sum([New Qry].Rejected) AS SumOfRejected, [New Qry].Shift, [New Qry].Connection, Sum([Rejected]/[Total Threaded]) AS [Reject %]
FROM [New Qry]
GROUP BY [New Qry].Machine, [New Qry].Shift, [New Qry].Connection;

What am I doing Wrong?

 

[arcaidius]

To format the output results better:

Total threaded	Total Reject	Reject Reject%
586	50	161.22%	should be 8.53% (reject / threaded)

 

[Micron]

Maybe?
Sum([SumOfRejected]/[SumOfTotal Threaded]) AS [Reject %]

You seem to be creating calculated fields then not using them. Find the values for [Rejected]/[Total Threaded] in the first record and divide them and you just might find it is using those instead (perhaps that's where you get 161.22 from. Or is there only one record, which doesn't seem likely given that you're summing.
BTW, I think you should be using DSum, not sum; e.g. Sum([New Qry].Rejected)

 

[arcaidius]

Wow I'm starting to hate access. tried Sum([SumOfRejected]/[SumOfTotal Threaded]) AS [Reject %] now when I run the query it asks for the sum of total and reject in parameter values.

 

[arcaidius]

Frustrating, but I solved it, only took me 3 queries to do it instead of just one..... is that right?
maybe between the first and second is redundant, but I made a 3rd query where the columns are SumofTotal and entered your Sum([SumOfRejected]/[SumOfTotal Threaded]) in a new column and now it works.

 

[Micron]

If it asks for parameters, it can't resolve whatever the name is. Usually due to spelling (all it takes is a single errant space). Sometimes using calculated fields in other expressions can cause a circular reference but apparently that's not an issue here. It could also have been that [SumOfRejected]/[SumOfTotal Threaded] AS [Reject %] would have been better. Very difficult to pinpoint when we can't see the data, but as noted, I'm not seeing the need for things like Sum([New Qry].[Total Threaded]). These expressions are evaluated on each record so why sum on one value in one record in one field? If you want a value that is the sum of all the values in a field, that is DSum as I noted, not Sum, otherwise I'd expect to see Sum(field1,field2) over each record.

 

[arcaidius]

I'm Going to buy a **** Access for dummies book. It is summing multiple records in field 1 and field 2, and grouping them together by a field 3, and then I'm adding a calculated field to get the percent. It is working by doing the Sum in one query and then the percent in a 2nd query because the field names are now "SumOfBlah"

I am not sure how to do the DSum, I am doing this all in Design view and Dsum is not one of the selectable options on the table box at the bottom. I am very new to access (self taught). I totally understand making the table in a way that will be good and what to relate for other tables and such, but that is about it. I'm in a QA position teaching myself IT **** lol.

From what you are saying I am doing a bunch of extra steps to get the same results, I'm sure this could all be done with just one query?

 

[Micron]

Dsum is not one of the selectable options on the table box at the bottom.

Not sure what you mean by table box. If you want to pursue a single query option grab some table data and post. Be sure to include all tables necessary for the query sum. You should get a table with field names in your post as a result. That I can work with if the desired result is clear (presumably would go by your first post).

 

[jackd]

Arcaidius,

I don't mean to dissuade you from buying a book. For things like recordsets, aggregate functions... you can often do quite well using DuckDuckGo/Bing/Google. There are several tutorials (of varying quality) on youtube also. Here is one on DSUM by Richard Rost that may help.

Reject Percent is (TotalRejects/TotalThreaded)*100.

Here are lots of Database articles in different formats and different subjects.