query records by a sum of a field

[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

 

[KARL DEWEY]

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)

 

[dvlander]

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

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)

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

 

[KARL DEWEY]

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.

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

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)

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