Calculating time

[Spikemannen]

Hi.

I have three tables.
tblCompany
CompanyID (count)
CompanyName (txt)

tblInvoice
ID (count)
CompanyID (related to tblCompany)
InvoiceDate (date)
InvoiceNo (txt)
TimeFrom (date)
Time (number)

tblSupport
ArrendID (count)
CompanyID (related to tblCompany)
Arrive (date)
Area (txt)
Description (pm)
Finished (yes/no)
FinishDate (date)
EstimatedTime (number)

Now I want tblInvoice.Time.Value to show the sum for all tblSupport.EstimatedTime.Value where tblSupport.CompanyID.Value is the same as in tblInvoice.CompanyID.Value and when the finished date is after tblInvoice.TimeFrom.Value.

Is there anyone out there that has an idea how to solve this?

Best Regardz,

Spikemannen

 

[PHV]

Something like this ?
SELECT I.ID, I.CompanyID, I.InvoiceDate, I.InvoiceNo, I.TimeFrom, Sum(S.EstimatedTime) AS TotalTime
FROM tblInvoice AS I INNER JOIN tblSupport AS S ON I.CompanyID = S.CompanyID
WHERE S.FinishDate > I.TimeFrom
GROUP BY I.ID, I.CompanyID, I.InvoiceDate, I.InvoiceNo, I.TimeFrom;

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

 

[hwkranger]

PHV,

I had almost the same solution. LOL - I posted on his other post. Funny that we used the same alias'.

(I broke it into company billing, you broke it down to individual bill)

Randall Vollen
National City Bank Corp.