Get field value from latest record where...

[Spikemannen]

Hi...

This is my code so far:

SELECT SUM(tblArrend.EstTime) AS SumForPeriod
WHERE tblArrend.CompanyID = tblInvoice.CompanyID
AND tblArrend.Finished<tblInvoice.Until

My table tblInvoice looks like this:

InvoiceID
InvoiceDate
CompanyId
BillTime
Until

In this form I choose a InvoiceDate and a Company.
Now in want my query to get the sum for tblArrend.EstTime where the tblArrend.Finished date is between tblInvoice.Until.value and the last record in the table where I have choosen the same CompanyID.

Can anyone help me, please...?

Best Regardz,

Spikemannen

 

[PHV]

the last record in the table
Which table (tblArrend or tblInvoice) ?
Which sort criteria for last ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Spikemannen]

the last record in tblInvoice.
The last record is the same companyid but the last invoicedate.

Best Regardz,

Spikemannen

 

[PHV]

I'm not sure to understand your issue ...
Perhaps something like this ?
SELECT Sum(A.EstTime) AS SumForPeriod
FROM tblArrend AS A
WHERE A.CompanyID = [Forms]![main form name]![CompanyID control]
AND A.Finished > (SELECT Max(InvoiceDate) FROM tblInvoice AS I WHERE I.CompanyID = A.CompanyID)
AND A.Finished < [Forms]![main form name]![Until control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Spikemannen]

I'm sorry but my "code" english is not that good :-/

I'll try to describe it better:

in tblArrend i put in arrends from different companys.
And every quarter(of the year) we are suppose to send an invoice to each company. But only for those arrends that has a been finished (tblArrend.Finished.Value wich is a date). But every quarter we should only send an invoice for the time that we have written down (in tblArrend.EstTime) since the last invoice. You see?
So in tblInvoice it should be as easy as possible to register a invoice for each company. The thought is that you should only have to choose a company and the "until-date" and get the amount of time that you should bill the company.

Was that a better description?

Best Regardz,

Spikemannen

 

[Spikemannen]

Hmm, your example didn't work. The field just came out blank.

Best Regardz,

Spikemannen