Show only last record in query

[robsworld78]

Hi, I made a query and thought it was showing the last record only for a specific field but now I realize its showing the max of that field.



I thought using the "Last" option in the "totals" row on the query designer would give the last record but its doing the same as "Max" under totals.

The query shows a vendor name and a bin number, if no grouping it will show all the orders with a bin number for each vendor but I only want to see the bin number on the last order.

What would I put in criteria to do that?

[June7]

Try DLast function as criteria for that field.

DLast("fieldname","tablename")

Be careful with First and Last (as well as DFirst and DLast). If you want the last record of a sorted table, that won't work.

[robsworld78]

Hi, that's not what I want and not pulling the last record. That narrows my query down to only 1 record no matter what I do so that won't work and it's also not pulling the last order for the bin number, its still pulling the 1st orders bin number.

The query is like this.

It has the orders info table on it.

For fields I have the VendorID, OrderID and Bin

If I use that without "Totals" it gives me all the vendors that have an order in the system placed at some point of time, then the orderID and Bin for that order, if they have more then 1 order in the system it shows them all.

This is the list I want except it should only show the last order for each vendor not all the orders each vendor has in the system.

If I remove the field bin and under totals select "Max" it will show me exactly what I want. It pulls all vendors and shows only the last orderID for each vendor.

The problem is when I add bin to the query, because the numbers are different for each order it will then add each order on the query and if I select "Last" under the totals it then shows the order with the largest bin number not the last bin number. It does the same thing if I select "Max" like I did for orderID.

So I need something in criteria to only affect the "bin" field not all fields like the code you passed along.

Make sense?

[June7]

DLast("Bin","query","VendorID=" & [VendorID])

[robsworld78]

Be careful with First and Last (as well as DFirst and DLast). If you want the last record of a sorted table, that won't work.

None of my tables are sorted neither is the query. All my tables show up in the order the records were placed, newest records at the bottom. I tried sorting it so the records would flip hoping it would then choose the correct record but it always grabs the highest Bin.

[robsworld78]

ok figured it out but it still does´nt work. This is what I have now.

DLast("Bin","Orders","Vendor=" & [Vendor])

I changed the name of the query to the table but its still pulling the highest bin number.

[robsworld78]

I thought I would try it with another field on the orders table and its doing the same thing. I removed bins and added vehicles, its basically the same senerio, a different vehicle is taken everyday just like a bin. If I ask for the last vehicle used it gives the highest number not whats on the last order.

[robsworld78]

Its actually not pulling the highest bin or vehicle its pulling the first record and only the first. Even if I have just the OrderID and Vendor on the query and ask for the Last orderID it gives me the first record. I tried deleting the query and rebuilding but same thing.

Simplest query I have and it wont work.

[robsworld78]

Ok I actually figured it out, your code is fine of course.

I was certain when I originally made the query it was working so I started looking at the table to see what changes I made to it. At one point I added a new field to the table called SaveOK, just a standard text field. it was for trying to undo a record, decided not to go with it but left the field there. No where in the DB was the field referred to but once I deleted it the query worked, even without the code as it should.

Access is always playing games with me, no reason that field should have messed up the query. Now Im going to be scared to add fields to my tables. All day access has been playing stupid games with me, time to shut it down.

[June7]

Don't understand why another field affected the results. But glad you got it resolved.

Was the DMax needed or did Max with totals grouping serve?

[robsworld78]

I didn't need DLast in the end, using Last in totals works as it should.

I'm glad I didn't have to use the DLast, I think I read once that if your using the runtime for the DB then non of the D functions should be used, as in DLast, DMax, DLookup, etc.. Am I right when I say that?

[June7]

By runtime you mean an mde/accde file? Could be, never set one up.

[robsworld78]

Yeah that's the one, I said runtime but its the executable version I don't think works with them. I planned to make this DB executable so since I read that I've been trying to avoid them. Except now I have a problem which probably isn't solvable, when I try to compile to the accde it gives a funny error about to many tableID's or something, so I'll just leave it as a accdr when I'm done.

Do you know of any good threads or websites about locking up a db so no one can tamper with it? More security then just hiding the navigation pane and hiding objects.

[June7]

This thread might be a start.
http://forums.aspfree.com/microsoft-...7t-327136.html

[robsworld78]

Thanks, I'll check it out.