need subquery help

[Milothicus]

I need a subquery to do what this query does:

SELECT tblInvs.InvJob, Sum(tblInvs.InvAmt) AS SumOfInvAmt
FROM tblInvs
GROUP BY tblInvs.InvJob;

It groups invoice amounts by job and sums the invoices for each one. the value will be shown on a form, so i need this to be in the recordsource, and i need to be able to edit the other controls on the form.

I'm not sure how to modify this to fit into a subquery, and i'm not sure where to put it, since the value will not be stored in a table anywhere.

any suggestions?

 

[KenHigg]

Mil,

The term you are using subquery has me a bit confused. As best I can tell your db has jobs and those jobs have invoices. The problem I think you are having is getting a job form to show a total amount of the job's invoices. Is this correct?

kh

 

[Milothicus]

My form's recordsource is a query. the single form display shows one record (one job) at a time, and a subform displays invoice dates and amounts for that job. i want a text box to display the sum of those invoice amounts to date. tblinvs contains invoice dates, amounts, and the job they're associated with.

to get the sums of the invoices grouped by job, i wrote a query. that's the one in my first post. i then included the results of that query in the recordsource for the form. it displays perfectly, but the recordset is no longer editable because of the sum function in the query... so what i need is a way to put the sum function as a criterion for a field in the query. there is some discussion of 'subquery' on this forum, and that's what it means; instead of including query1 in the source of query2, you place query1's SQL code (modified, i assume) into query2's criteria row. the problem is that each column in the query has to be related to a field in a table, and my data (sum of invoice amounts) isn't in a table, and isn't worth storing because all the info is already in the database (individual amounts).

so.... i need to group invoices by job#, add up the amounts for each job#, and include those totals in my query2.

now, i'm not sure it's even possible to put this code into a criterion, so i've been reading and am attempting to use a temporary table. not sure how to trigger it being cleared and repopulated, though. i'm working on it.

 

[KenHigg]

I think you're making this more complicated than it needs to be. If you have your main form jobs and a subform invoices, you should be able to put a text box control in the subform's footer that will sum the displayed invoices using a something like sum(invoiceAmount) where invoiceAmount is the fld name in the subform. Am I missing something?

kh

 

[Milothicus]

hm...hadn't thought of that. i suppose i'm the one missing something. I'll give that a try.