Calculating time

[Spikemannen]

Hi!

I have five tables:

tblCompany
CompanyID
CompanyName

tblSubject
SubjectID
Subject

tblHandler
HandlerId
Handler

tblArrend
ArrendID
Company
Arrived
Subject
Arrend
Handler
Prio
Description
Status
Finished
Time

tblInvoice
InvoiceID
Company
InvoiceDate
Until
Time

For the function I'm after I will work in tblInvoice.
I choose a invoice date (presumably today), then I choose a date in the tblInvoice.Until, when I leave that field I want a questiothat calculates all tblArrend.Time for every arrend that has a finished date before tblInvoice.Until for the active post but after the last posts tblInvoice.Until value. AND the time will only be calculated for thoose arrends that has the same Company value as tblInvoice.Company.

How to do this? Please, help me...

Regardz,
Spikemannen

 

[Mile-O]

For a start. Change Company and Handler in tblArrend to CompanyID and HandlerID. These are the fields that should be related.

 

[Spikemannen]

Yes

I have already done that, just a mistake when I wrote it down here.

 

[The_Doc_Man]

for every arrend that has a finished date before tblInvoice.Until for the active post but after the last posts tblInvoice.Until value. AND the time will only be calculated for thoose arrends that has the same Company value as tblInvoice.Company.

The "before tblInvoice.Until" is easy...

The "same company value as tblInvoice.Company" is easy

The "after the last post's tblInvoice.Until" is less obvious.

Using "Time" as a field name in tblInvoice and in tblArrend is a very bad choice. "Time" is a reserved word and the name of a VBA function. "Until" (as appears in tblInvoice) is ALSO a reserved word and the name of a VBA action verb/condition. Watch your nomenclature 'cause Access will reach up and bite you on the butt every time you step into the "field name is a reserved word" arena.

Assuming you fix the nomenclature, the information you want can be based on a select having a specific record from tblInvoice

WHERE tblArrend.CompanyID = tblInvoice.CompanyID ... (or you could JOIN the tables?)

The time criterion is simple, too... tack on to the above the substring

AND tblArrend.Time < tblInvoice.Until

It is the "since LAST invoice" that is tricky. Specifying that entry is the thing that will eat your socks.

This COULD be a DLookup with complex selection criteria. Assuming the invoice ID to be non-random, you could select the record with the right Company ID and the highest Invoice ID that doesn't match the one that is your object of attention. Look up DMax on how to find the "latest record other than the current one" and from there, you can use DLookup for the date on that record.

 

[Spikemannen]

Thanks

Thanks, I will try this.

About naming the fields, I don't think it would be a problem beacuse the database will be in swedish not english, but thanks for the comments, that was new and good information.

Best regardz,
Spikemannen

 

[Spikemannen]

:-(

I did not get any more info about "latest record other than the current one".

My sql code looks like this:
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...?