non updateable ... again

[Milothicus]

I have a listbox, and it's underlying query includes a group by / sum function pairing. because of this, it's recordset is non-updateable.

I need to select an updateable recordset for which that calculated field is in a certain range (say between 0 and 1).

what i'm hoping for is to use the non-updateable query to create a list of record IDs who's calculated value is between 0 and 1, and then open an updateable recordset including only those records.

is there a way to do this?

 

[Mile-O]

You know, the actual query is more helpful if you post it rather than just talk about it.

 

[Milothicus]

I know, but i'm trying to simplify it. it's a big ugly query that i don't want to make people try to decipher. i'll try to narrow it down a bit..... give me 5 minutes.

 

[Milothicus]

Here are the non-updateable queries:

this calculates the outstanding value by taking the original vallue, adding the value of all revisions, and subtracting the value of all the invoices for a job:
SELECT Input.Enquiry_Index, nz([Input]![Value])+nz([qryValRev]![SumOfRevValue])-nz([qryInvAmt]![SumOfInvAmt]) AS Total
FROM qryInvAmt RIGHT JOIN (qryValRev RIGHT JOIN [Input] ON qryValRev.JobNoFK = Input.Enquiry_Index) ON qryInvAmt.InvJob = Input.Enquiry_Index;

This calculates the revisions to the original value:
qryValRev:
SELECT tblValRev.JobNoFK, Sum(tblValRev.RevValue) AS SumOfRevValue
FROM [Input] LEFT JOIN tblValRev ON Input.Enquiry_Index = tblValRev.JobNoFK
GROUP BY tblValRev.JobNoFK;

This calculates the amount that's been invoiced
qryInvAmt:
SELECT tblInvs.InvJob, Sum(tblInvs.InvAmt) AS SumOfInvAmt
FROM tblInvs
GROUP BY tblInvs.InvJob;

here's the updateable query (severely chopped to the relevant bits):

SELECT Input.Enquiry_Index, qryTotalValue.Total AS Outstanding
FROM [Input] LEFT JOIN qryTotalValue ON Input.Enquiry_Index = qryTotalValue.Enquiry_Index
WHERE (((qryTotalValue.Total)>0 Or (qryTotalValue.Total)=IIf([Forms]![frmJbBk]![chkCmpltJB],([qryTotalValue].[Total]),([qryTotalValue].[Total])<=0)));

This is part of a search, and i have a check box for "include completed jobs" (chCmpltJB).

Make any more sense now?

 

[Milothicus]

guess it just confused things more...