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

[ItsMe]

Refer to post #9. You need a working query.

[rp123]

oh okay.

Is it possible to divide a field in a table by a field in a query using another query?

[ItsMe]

Yes but, the calculation is done in the second query first. Then, the main query will retrieve the calculated data, avoiding the ambiguous join error.

[rp123]

ah okay!

sorry i mistook what you meant by working query. I have a working query, this is it:

SELECT SUPPLIERS.[SUPPLIER NAME], Sum(ORDERS.QUANTITY) AS [DEFECTIVE PARTS]FROM (ORDERS INNER JOIN PARTS ON (PARTS.[PART NUMBER] = ORDERS.[PART NUMBER]) AND (ORDERS.[PART NUMBER] = PARTS.[PART NUMBER])) INNER JOIN SUPPLIERS ON PARTS.ID = SUPPLIERS.ID
WHERE (((ORDERS.[ORDER CODE])=2))
GROUP BY SUPPLIERS.[SUPPLIER NAME];

[ItsMe]

So you want to multiply the Sum(ORDERS.QUANTITY) by 100 and then divide that by ORDERS.QUANTITY ? What is the expression you tried using with this SQL? How does your question in post #1 realate to this SQL?

[rp123]

that is my query used to calculate how many defective parts were sold by each supplier.

I want to divide that by the total sold by each supplier (which is the quantity in the orders table) and then multiple the answer by 100

[ItsMe]

If the SQL in post #19 is provideing good results then build another query to manage the rest of the calcualtion. In the second query add the necessary tables and also add the first query. When you create your alias in your new second query, you will need to include the query name that created the alias DEFECTIVE PARTS.

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

This is assuming quantity is a field in a table and said table is on your new query. If you are still having troubles, uploaded your last atempt here by removing personal data, compact and repair, and Zip Down the file.

[rp123]

my file is 544kb after clicking on compact and repair

[rp123]

i tried it but it keeps showing up with the sql error

[rp123]

can i send a bigger file through private message on the forum?

[ItsMe]

What if you "Send To" and "Zipped" using the mouse's Right Click?

[rp123]

ah okay i done itAttachment 15862

[rp123]

....zip

there

[ItsMe]

Unfortunately, your tables are not "Normalized". You should have relationships between your tables based on Primary Key fields and Foreign Key fields.

For instance, the relationship between table Parts and table Suppliers will not work. You can not JOIN two tables together on their Primary Key Fields. Your parts table has a field named, "Supplier". This field is text. It does not make sense to store the literal text of the Supplier in this field. It would make more sense to Use the "Supplier" field as a Foreign Key to the Primary Key in the Supplier table. The Supplier field would then be a Long Integer data type to accommodate the Autonumber field that is the Primary Key in table Suppliers.

You store the Primary Key value in the corresponding Foreign Key field. The data types need to match. AutoNumber fields fit into Long Integer data types. You would base your relationship on these PK/FK fields. You use these relationships to create JOINS in your queries.
You should study up on the term, Relational Data Base Management System (RDBMS). RDBMS rely on a Normalized table structure, or Relations. Without this you will not be able to build a query. Without queries you will not be able to build a public interface or be able to Manage your data. This needs to come first.
Use Autonumber fields where possible to generate PK’s

Use Foreign Key fields in relative tables to store the PK value in the FK field, thus allowing a relationship.

Do not create relationships based on text. Use whole number data types like long integers.

Do not use special characters, spaces, or reserved words in the names of your objects ie, tables, reports, forms, etc.

[rp123]

oh okay. thank you very much for your help! should I start a new database or edit the relationships in the current one?