Someone please help me...

[Carrie]

I am doing a query in which I need to find the date of the last call logged. Unfortunately, I do not have a table with a field for this.

What it is currently doing is telling me the organization and the date of every call they logged.

Is there an expression that will tell me just the most recent date that organization logged?

_________________
Thanks,

Carrie
This message was edited by Carrie on 2003-01-07 15:54

 

[Bariloche]

Carrie,

I think this will work for you (leastwise it worked in the "test" database I created ).

Create a query that calculates how many days, hours, seconds (whatever is appropriate), have elapsed since "Now". This will be calculated using the DateDiff function, thusly:

TimeAgo: DateDiff("s",LogTime,Now())

(modify that function to change "LogTime" to whatever the correct field name is)

Add that as a field to a query that also contains whatever other descriptive fields you need. Save that query, then create a another query based on that one. This time you want to create a "Total" query, put in the fields you need including the "TimeAgo" field. Drop the list box on the "Total" row for the "TimeAgo" field and change it from "Group By" to "Min". When you run the query (provided you've chosen your fields correctly) you should now have the Call that was logged most recently (i.e., the call for which the difference between Now and when it was logged is a Minimum)!

See if you can follow all that. If not, post back.


have fun
This message was edited by Bariloche on 2003-01-07 21:24

 

[Bariloche]

Carrie,

I just played around with my "first" query (the one that calculates "TimeAgo") to see if I could get the same result using just one query and I found that I could (my first boneheaded attempt when I was putting this solution together failed so I went with the two query approach). To do it in just one query, just make the query that you calculate "TimeAgo" a "Total" query and select "Min" as the function.

When I tried it initially I tried TimeAgo: Min(DateDiff("s", LogTime, Now()). The SQL resolves to this syntax but it needs to be entered as a "Total" query. Ah well, I'll get the hang of this someday.

enjoy

 

[WJReid]

Hi Carrie,

I am very new to Access and I tried your suggestion, but my query still shows all of the company's invoices. Any ideas on what is wrong.

I have used TimeAgo: DateDiff("s",[Date],Now()). Date is the name of my field for the Invoice Date.

regards

WJReid

 

[dmckinney]

Create a query with the tables Call and Org joined on Call.Org_ID = Org.OrgID.

Choose the fields Org.OrgName and Call.CallDate (or whatever this field is called)

Select GroupBy from the query options. (The greek sigma symbol) and in the GroupBy line under the Call.CallDate field select Max.

SQL should look something like the following. (Hand written so may contain errors.)

Select
Max(Call.CallDate),
Org.OrgName
from
Call inner join Org
on
Call.Org_ID = Org.OrgID
GROUP BY Org.OrgName

regards dave.

 

[WJReid]

Thank you very much Dave.

Bill

 

[Carrie]

Yes, Thank you, Your my hero.