How to divide a sum of a column in a table by a value in a query, using a new query?

[rp123]

I have a database which contains a table called "orders" that has a field called "quantity" in it. I also have a query called "defective parts supplied" which has a field called "defective parts".



How would I use a new query called "% failure/defect rate per supplier" to calculate the following:
100 * [defective parts]/[quantity]


Thank you!

[ItsMe]

You should be able to create an alias in your query. You can use the query builder to help. In a new field, in the grid at the bottom of the Query Builder window you could put something like.

MyCalc: 100 * [TableName].[defective parts]/[TableName].[quantity]

[rp123]

Hi,

thank you for your help.

I tried that but it keeps saying:
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement"

[ItsMe]

Do you get that message without the expression I suggested? You may have to create another query object to do the calc and then join the new query to the original query.

If the message only occurs when the expression is added, you may be able to do something with parenthesis and or another alias to force the calc to be retrieved first, all within a single SQL.

[rp123]

yes it only comes up with the expression you suggested. ive been trying that same one for quite a while but it doesnt make sense to me as I am new to access!

what is the best thing for me to do? i want to divide a field in a query by a field in a table

[ItsMe]

I am going to guess that quantity and defective parts are coming from two different tables and doing the aggregate calc is causing confusion for Access. It does not know what to do first.

Can you post the SQL from the query that works. Use the forum tools to preserve the code you paste here. Click the "Go Advanced" Button and then the # hashtag button to add brackets that you can paste the SQL into. This will help preserve the formatting of your code and make it easier to read.

Also create a second code example, clicking # again, to paste the Expression you tried. The expression with the calc.

[rp123]

Ooh i tried around 10 different codes I searched online from and nothing worked. I dont have the codes because I had to delete them as they did not work!

[rp123]

nothing from that query is working. even when i delete the % failure field, the rest of the data does not work either. my other fields in that query that I need are Supplier name (from the suppliers table), defective parts (from the defective parts supplied query) and quantity (from the orders table)

[ItsMe]

EDIT: You will need a working query to start with.


You mentioned a working query. You can view the SQL by clicking the View SQL button on the upper left corner while in design view. Also fill in the blanks here for the table names, relative to the fields in your calc.

MyCalc: 100 * [TableName].[defective parts]/[TableName].[quantity]

[rp123]

select suppliers.[supplier name], [defective parts supplied].[defective parts], orders.quantity, 100*[defective parts supplied].[defective parts]/[orders].[quantity] as [& failure/defect rate per supplier]
from (orders left join parts on orders.[part number] = parts.[part number]) left join ([defective parts supplied] left join suppliers on [defective parts supplied].[supplier name] = suppliers.[supplier name]) on parts.id = suppliers.id

[ItsMe]

So post #10 is not an example of SQL that works????

[rp123]

it does not work anymore for some reason

all I did to get that was create a query and selected 3 field names from 3 different tables

[rp123]

now it is saying join expression not supported

[ItsMe]

This part indicates an expression
100*[defective parts supplied].[

Perahaps you can build another one.

[rp123]

build another what + how do i build another one?