OK I GIVE UP!! Help Me!!!!

[DaniBoy]

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!!

Thanks you
DaniBoy

 

[David R]

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

 

[DaniBoy]

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

 

[David R]

Well, what happened when you tried it?

 

[DaniBoy]

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

 

[DaniBoy]

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

 

[David R]

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

 

[DaniBoy]

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

 

[RV]

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

 

[DaniBoy]

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

 

[DaniBoy]

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

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

 

[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

 

[RV]

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)