Hello Guys,
Well I have looked in all the Q&A and cant seem to find what I need!!! Its simple but I cant get it to work. I have a query that searches for the same IDnumber in two tables and only list the ones that match. I also want it to give me only the IDnumber with the latest date. I did the first, last, the max and min, they work fine, only that it shows all the records, I only need the Last record entered. Help me see the LIGHT!! http://www.access-programmers.co.uk/ubb/smile.gif

Thanks you
DaniBoy

Possibly a silly question, but why are you looking in two tables for the same sort of data?
You can use a Union Query to get everything into one place, then try the Max function on your date field. Group by ID# or sift it out in criteria to narrow it down.

HTH,
David R

The aggregate functions need to be able to aggregate data in order to function properly. If you include all the detail fields along with the aggregate function, they prevent the function from performing as you expect. You need to either use a sub-query or break your present query into two pieces. The first query would include just the IdNumber and the Max(someDate) fields. This allows you to find the max date for a given IdNumber. Then the second query takes the first one and joins it back to the original table to pick up the details. If there are multiple entries for the same date for the same Id, you will still get more than a single row per Id. If that happens, you'll need to use either a First() or Last() function to choose which row to return.

Thanks guys,
But I think I confused you guys. I am only using one table and I want to find the latest date on the "DateCompleted" field. This means that when I run the query I should get only the last record entered.
DaniBoy

Well, what happened when you tried it?

sorry!! this is what am doing. I have a work order database where the tblWorkOrders has a WO# field DateCompleted field and the addressID, all the rest of the fields I realy dont use on the query. I want to run a query where the results are going to be the lastest completaion date for an address on the workorder table. Meaning that if 1825 SW 40th Ter has 4 Workorders and all WO are completed, I will get the latest workorder acording to the closest completion date to current date.
When i try to do it I get all the workorders for that address, and not in order from max to min, or first to last. I just get a simple list. I only need the lastest complition date workorder for that address.

Am I explaining what I need ok?

I could send someone the table and the query.
thanks

ok, i just found out that no matter what I group the DateCompleted field by, I always get the same answer. What is up with this thing!!! Am gonna go run around the block!!!!!!!
DaniBoy

Well, after you get done with your shower, try this:
Open a new query.
Show the table you want.
Drag down the [DateCompleted] field.
Click the Sigma button (sideways M) on the toolbar.
Change "Group By" to "Max".
Run the query. I just tested this and it gives the last date.

Now save that query. Join that query to your regular query and you can get the record(s) back for that last date. As Pat said, if more than one show up and that's unacceptable, you'll have to take additional steps with First, Last, Max again, or something.

Good luck,
David R

Nothing seems to be working!!!!!! I dont know what else to do!! The access bible does not show anything on this!! I tried what you guys told me and nothing. I cant believe this is kicking my but!!!!! It should be a simple query!! Man am MAD!!!!! I did the what you told me David, but I gat a date that is not even close to the last completion date. Can I send this to someone!!! or can someone send me one that is working to see how to do it? !!!
Thanks,
Going running again!!! and putting ice on my tub!!! to cool off!!!! jajaja
DaniBoy

Or use just one query:

SELECT tblWorkOrders.AdressID, tblWorkOrders.DateCompleted
FROM tblWorkOrders
WHERE tblWorkOrders.DateCompleted=
(SELECT Max(tblWorkOrders1.DateCompleted)
FROM tblWorkOrders AS tblWorkOrders1
WHERE tblWorkOrders.AdressID = tblWorkOrders1.AdressID);

Suc6,

RV

Hey RV it works great!!!! no more running I hope!! jajaja, Now when I select another field from the same table and drag it down to the query it gives me this Msg. "You tried to execute a query that does not include the specify expression 'AddressID'as part of an aggregate function.
What does that mean? How can I see more fields?
Thanks

Hey RV I think I got it !!! I have to put Group By = Expression!!!!

YYYEEEEEEEHHHHHAAAAAA!!!!
Rum and Coke baby!!!!
DaniBoy

Hey guys!!!
Everything is working fine with the query, only that now I wanted to make a criteria on a field to be true, when I put true and the field it gives MSG "HAVING clause (WorkOrders.NewCheckBox=-1)without grouping or aggregation." I am putting grouped by Expression and still get the msg.
What can that Be?
Thanks DaniBoy

Just change your HAVING clause to a WHERE clause:

WHERE WorkOrders.NewCheckBox=-1

Greetings,

RV (no need to thank, that's what forums are all about, helping each other, anyway, thanks)