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

 

[alvechurchdata]

Do you have a specific reason for duplicating this information. Can't you retrieve it from tblSupport whenever necessary?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[hwkranger]

First of all, storing a calculation is almost never a good idea.

Use a query, this way your calculation is real and not a plug.





Randall Vollen
National City Bank Corp.

 

[hwkranger]

By the way, your answer is as follows:

Select
I.CompanyID,
Sum(EstimatedTime) as TotalTime
from
tblInvoice as I Inner Join tblSupport S
On I.CompanyID = S.CompanyID
Where
FinishDate > TimeFrom
Group by
I.CompanyID

Randall Vollen
National City Bank Corp.