calculating turntime over multiple records

[kev747]

Hi all,
Not sure if this is possible, so I'm after some advice.

I have a field in my form that records the turntime taken for items being returned by a vendor. There will be multiple records in the db that are linked by a common serial number, but the different items will be despatched and returned at different times, hence more than one record is needed. The turntime is calculated from the last item despatched date, to the last item received date.

Is it possible to -

1) Filter all records with a particular serial number.
2) Select the latest despatched date from the filtered records.
3) Select the latest date received from the filtered records.
4) Calculate the turntime based on these two dates and enter the value into a field on all of the filtered records.

I think I may have to create an outer form with the serial number on it, and then a subform for all the items despatched that are linked to that serial number.

Any ideas would be greatly appreciated.

Kev.

 

[GingerR]

sure. you can make it all in one field, but i'll separate it out so you can see what's happening.

in this i am assuming you are on a form. i will calculate the max DISPATCHED date and max RECEIVED dates. then i will subtract those two into a text box on the form.

you'll have to put in your own field/table names.

make three text boxes on the form called:
MaxDispatched
MaxReceived
TurnTime

with the following as control sources:

MaxDispatched: =dmax("Dispatched","SubTable","SerialNo = " & SerialNo)

MaxReceived: =dmax("Received","SubTable","SerialNo = " & [SerialNo] & " and  Received > #" & [MaxDispatched] & "#")

{i put in that the received date must be greater than the dispatched date, cause i'm wondering if some would be 'dispatched' but not yet 'received'? i mean i dont want to get a 'prior' received date-i only want the received date if it's greater than the max Dispatched date, right?}

then TurnTime text box = subtract the two.

is this close? if not please provide your table structure and i'll figure it out more. maybe just using the dmax functions you can figure out what you need. look them up in help.

you also mention "and enter the value into a field on all of the filtered records" but i'm not sure what you mean - you want to enter that value into a field in a table? it's not a good idea to put a calculated value into a table cause it can change (a diff max received date for example). please explain more if this is what you mean.

thanks-g

 

[kev747]

Thanks for the help G,

In my form, I have three fields, date despatched, date received, and turnaround time. As each item is despatched, the date is recorded in the appropriate field, and the same when its returned.

However, multiple items will be linked by an assembly serial number (also on the form), so there may be 5 or 6 records for the one serial number. The turnaround time field is applicable to the assembly serial number, not the individual items.

What I need to do is out of the 5 or so records, find the latest date despatched, and subtract it from the latest date received. This value (say 20 days) then needs to be entered into the turnaround time field for each of the 5 or so records.

Currently all fields exist on a single form, but I think a subform may be required for the assembly serial number and turnaround time fields, thus only one turnaround time field will be updated, instead of multiple ones.

I was kind of hoping to avoid this, only because I've already built the form, and there are a couple of hundred records already. Just trying to save myself some extra work.

Again, thanks for your help.

Kev.

 

[GingerR]

how big is your db? can you compact it and put it in a zip file and send it to me to look at? otherwise i think i'll be asking questions for days....datachick10@hotmail.com

g