query records by a sum of a field

  • Thread starter Thread starter dvlander
  • Start date Start date
D

dvlander

I have a simple query that has four important fields, inAssignmentID,
inActive (Yes/No), inInvoiceAmt and inForecastAmt. I want the query to show
any record where inActive is Yes OR any record where in inActive is No AND
the sum of inInvoiceAmt for that specific inAssignmentID is greater than zero.

I'm relatively inexperienced so I really appreciate the assistance.

Dale
 
the sum of inInvoiceAmt
You do not Sum a single record. Below is where the value of [inInvoiceAmt]
is compared to zero.

WHERE [inActive] = -1 OR ([inActive] = 0 AND [inInvoiceAmt] >0)
 
Hi Karl:

Thank you so much for your reply. I understand your response but I'm not
sure it accomplishes what I'm looking for.

Say I have a group of records for inAssignmentID = 1 with an inActive value
of No (or zero). Some records will have > zero values in inInvoiceAmt and
others will have > zero values in inForecastAmt. Your solution will cause
the records with > zero values in inInvoiceAmt to be included (correct) but
the ones with > zero values in inForecastAmt not to be included (incorrect).

For this example, I would like ALL records to show for inAssignmentID = 1
because there is a > zero value in inInvoiceAmt for ANY inAssignmentID = 1
record. That is why I referenced the Sum as potentially being useful in the
solution.

I hope I'm making some sense.

Sincerely,

Dale

KARL DEWEY said:
You do not Sum a single record. Below is where the value of [inInvoiceAmt]
is compared to zero.

WHERE [inActive] = -1 OR ([inActive] = 0 AND [inInvoiceAmt] >0)

dvlander said:
I have a simple query that has four important fields, inAssignmentID,
inActive (Yes/No), inInvoiceAmt and inForecastAmt. I want the query to show
any record where inActive is Yes OR any record where in inActive is No AND
the sum of inInvoiceAmt for that specific inAssignmentID is greater than zero.

I'm relatively inexperienced so I really appreciate the assistance.

Dale
Click to expand...
Click to expand...
 
As I said you will not get a sum for a single record. Do you have multiple
records for a inAssignmentID? Do all of the records for a inAssignmentID
have the same inActive status?

Post sample data.


dvlander said:
Hi Karl:

Thank you so much for your reply. I understand your response but I'm not
sure it accomplishes what I'm looking for.

Say I have a group of records for inAssignmentID = 1 with an inActive value
of No (or zero). Some records will have > zero values in inInvoiceAmt and
others will have > zero values in inForecastAmt. Your solution will cause
the records with > zero values in inInvoiceAmt to be included (correct) but
the ones with > zero values in inForecastAmt not to be included (incorrect).

For this example, I would like ALL records to show for inAssignmentID = 1
because there is a > zero value in inInvoiceAmt for ANY inAssignmentID = 1
record. That is why I referenced the Sum as potentially being useful in the
solution.

I hope I'm making some sense.

Sincerely,

Dale

KARL DEWEY said:
the sum of inInvoiceAmt
Click to expand...
You do not Sum a single record. Below is where the value of [inInvoiceAmt]
is compared to zero.

WHERE [inActive] = -1 OR ([inActive] = 0 AND [inInvoiceAmt] >0)

dvlander said:
I have a simple query that has four important fields, inAssignmentID,
inActive (Yes/No), inInvoiceAmt and inForecastAmt. I want the query to show
any record where inActive is Yes OR any record where in inActive is No AND
the sum of inInvoiceAmt for that specific inAssignmentID is greater than zero.

I'm relatively inexperienced so I really appreciate the assistance.

Dale
Click to expand...
Click to expand...
Click to expand...
 
Back
Top