Difference between Dates

[Qualinwraith]

I have a Transactoin Table that holds the following fields:

TransID (*Primary Key Numerical Value)
OrderID (Non Unique Number Value, One Order can have multiple Transactions attributed to it.
Status (Numerical Value ranging from 0 to 32, each is a different phase in the life of a transaction such as 0 means Transation Created, 1 through 31 are various actions with 32 beign the transaction closing)
Date (The date in which a particular phase of a particular transaction happened.)
Reason (The Description of a phase. This is the only text field whose contents are relative to the Status field in the same table.)

I need to find the amount of time passed from the first Transaction to the last transaction of each individual order, which will be displayed in a report. Unfortunately not every order goes through all 32 phases, in fact most dont. The individual orders have a random amount of phases that they go through but each have a starting one (which ranges from 0 onwards) and an ending one (once again it is not necessarily 32). The dates correspond to the numbers in an individual order which means that if an order has the following phases:

1
2
4
6
Then the dates of each phase will be one after the other like the Status Number as shown above. Anybody knows how to do this? I need to find how much time passed from the first transaction to the last for each individual order qand display it in a report.

 

[namliam]

Easy-ish...

select
OrderID
max(Status) lastStatus
min([date]) firstdate
max([Date]) lastDate
from [yourtable]
group by orderid

P.S. You should not call your fields anything that is allready a default thing within access or vba (Date for example)

 

[Qualinwraith]

select FK_PledgeID, max(intStatus) lastStatus, min([dtmStatusDate]) firstdate, max([dtmStatusDate]) lastDate from tblMoscow_Transactions group by FK_PledgeID

This is the sql statement optimized for my table yet I am getting a

'Syntax Error 'missing operator' in query expression max([intStatus]) lastStatus'

any clue why?

 

[namliam]

Ah ... access...

You need to put an 'as' inbetween the alias and the max() thing

Try posting query like this:

select FK_PledgeID
,      max(intStatus) as lastStatus
,      min([dtmStatusDate]) as firstdate
,      max([dtmStatusDate]) as lastDate 
from   tblMoscow_Transactions 
group by 
       FK_PledgeID

It is much more readable don't you think?? Just quote my post to see how its done....

Greetz

 

[Qualinwraith]

Ah ... access...

You need to put an 'as' inbetween the alias and the max() thing

Try posting query like this:

select FK_PledgeID
,      max(intStatus) as lastStatus
,      min([dtmStatusDate]) as firstdate
,      max([dtmStatusDate]) as lastDate 
from   tblMoscow_Transactions 
group by 
       FK_PledgeID

It is much more readable don't you think?? Just quote my post to see how its done....

Greetz

Thanks a lot Namliam, that cleaned the mess up. I kind of need to add an additional column to this thing, one that will allow me to calculate the tiem difference between the first date column and the last date column. Do you know how that is done?

 

[namliam]

Yes I do, but you do have a brain... Try using it...

Hint: You should use a minus sign (-) somewhere

 

[Qualinwraith]

Ok it worked but the value returned is a tad unreadable with all the exponential values and all. any way how to make it more readable? is there such thing as formatting?

 

[namliam]

How do you mean? It is probably showing the number of days between the 2 dates, possibly adding in some decimals to allow for Minutes, hours and seconds...

What is actually your output?