Summing two fields from one table and dividing them in a pivot tab

[Guest]

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks

 

[Guest]

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])

 

[Guest]

When I try that, there is a message: "The expression you have entered
contains invalid syntax. You may have entered an operand without an
operator."

To use Sum, I think you need to have two fields that are multiplied or one
field that is multiplied by a constant. It seems Access work easily in the
rows but it does not behave like Excel as for the summation of a column.

There should be another way, the information is in the qry.
--
GillesABelanger

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks

 

[John Spencer]

You are missing a parenthesis in the statement, after "Sum(Defects".

Results: Sum([Defects]) / Sum([Qty of items audited])

When I try that, there is a message: "The expression you have entered
contains invalid syntax. You may have entered an operand without an
operator."

To use Sum, I think you need to have two fields that are multiplied or one
field that is multiplied by a constant. It seems Access work easily in the
rows but it does not behave like Excel as for the summation of a column.

There should be another way, the information is in the qry.
--
GillesABelanger

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks

 

[Guest]

Right. When I have all parenthesis, then, there is the message "You tried to
execute a query that does not include "FGA_AuditNu" as part of an aggregate
function.

This FGA_AuditNu is the name assigned to the primary key.

Why is that?
--
GillesABelanger

You are missing a parenthesis in the statement, after "Sum(Defects".

Results: Sum([Defects]) / Sum([Qty of items audited])

When I try that, there is a message: "The expression you have entered
contains invalid syntax. You may have entered an operand without an
operator."

To use Sum, I think you need to have two fields that are multiplied or one
field that is multiplied by a constant. It seems Access work easily in the
rows but it does not behave like Excel as for the summation of a column.

There should be another way, the information is in the qry.
--
GillesABelanger

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])


:

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks

 

[John Spencer]

Well, since you didn't post your entire query (SQL text) I can only speculate
that you have the field FGA_AuditNu in the SELECT clause and don't have it in
the GROUP BY clause. Now the question that this leads to is - if FGA_AuditNU is
in your SELECT clause, then why bother to group at all since Primary Keys are
unique and will force one row for every primary key value.

Right. When I have all parenthesis, then, there is the message "You tried to
execute a query that does not include "FGA_AuditNu" as part of an aggregate
function.

This FGA_AuditNu is the name assigned to the primary key.

Why is that?
--
GillesABelanger

You are missing a parenthesis in the statement, after "Sum(Defects".

Results: Sum([Defects]) / Sum([Qty of items audited])

When I try that, there is a message: "The expression you have entered
contains invalid syntax. You may have entered an operand without an
operator."

To use Sum, I think you need to have two fields that are multiplied or one
field that is multiplied by a constant. It seems Access work easily in the
rows but it does not behave like Excel as for the summation of a column.

There should be another way, the information is in the qry.
--
GillesABelanger

:

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])


:

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks

 

[Guest]

John,

I guess one of my problem is I do not master SQL. I am trying to create a
calculated field to extract the information I need.

I will read about SQL and try again.

Thanks for your answers.
--
GillesABelanger

Well, since you didn't post your entire query (SQL text) I can only speculate
that you have the field FGA_AuditNu in the SELECT clause and don't have it in
the GROUP BY clause. Now the question that this leads to is - if FGA_AuditNU is
in your SELECT clause, then why bother to group at all since Primary Keys are
unique and will force one row for every primary key value.

Right. When I have all parenthesis, then, there is the message "You tried to
execute a query that does not include "FGA_AuditNu" as part of an aggregate
function.

This FGA_AuditNu is the name assigned to the primary key.

Why is that?
--
GillesABelanger

You are missing a parenthesis in the statement, after "Sum(Defects".

Results: Sum([Defects]) / Sum([Qty of items audited])


GillesABelanger wrote:

When I try that, there is a message: "The expression you have entered
contains invalid syntax. You may have entered an operand without an
operator."

To use Sum, I think you need to have two fields that are multiplied or one
field that is multiplied by a constant. It seems Access work easily in the
rows but it does not behave like Excel as for the summation of a column.

There should be another way, the information is in the qry.
--
GillesABelanger

:

Try adding a new column in your totals query like --
Results: Sum([Defects] / Sum([Qty of items audited])


:

I posted a message on that subject a few days ago. I guess my question was
not clear.

Take 2.

Using dap, Audits results are gathered in a database. I am typically
auditing five identical items and look for critical defect. The information
is stored as defects qty and qty of items audited.

Defects Qty of items audited
0 5
1 8
2 6
0 5
Those two fields are in the same table.

How can I divide the sum of "defect" by the sum of "Qty of Items audited"?

I can sum each item but I do not see how to divide one by the other.

I also want to create a Pivot table and get those numbers per selected period.

Is this possible?

Thanks