Query having trouble pulling last record

[robsworld78]

Hello, I seem to have a complex problem and can't sort it out.



I'm trying to use a query to pull info from the last record in a table but its acting very glitchy and for the life of me I can't even find a pattern to what's causing it, I have a theory though.

Ok the db is quite complex and is for inventory control. I have a table called "Orders" where all orders get placed and the order details go to a table called "Inventory" (bad choice of name) which are linked together and works flawlessly.

In the "orders" table I'm trying to pull the last record for each vendor showing the "bin" they last used. In the query I have just the "orders" table in the query using the fields "VendorID" and "Bin". In the query I've used the criteria "DLast("Bin","Orders","VendorID=" & [VendorID])" and using grouping I've used Last and both give the same result.

I add records to the table via 2 methods, a regular form and on that form I add records using SQL insert. Depending what a user does on the form it might add a single record using just the form or it may trigger 2 SQL insert statements which adds 2 extra records to the table, so when the user is done on the form 3 new records were created and that is also working flawlessly.

It appears that if the last 2 records have the same VendorID and one was inserted via "SQL insert" and the other via the form it chooses the SQL insert record even though it comes before the record that was inserted via the form. (I hope that makes sense)

Its seems if all records go in via the form the query always has the right result and if all records go in via SQL insert the query has the right results but when records are added via both methods it seems to have glitches, however that could be all wrong because its done this from the very beginning I think before I used SQL insert except in less frequency. I got help on the forum once and thought I had it sorted out but the problem still exists.

The worst is I don't even know if this is whats causing it but its the best pattern I can come up with.

If I add just the field "OrderID" from the table "orders" in the query it still doesn't pull the last orderID, it acts the same and gets stuck on the same record for.

The table "inventory" which has all the order details also has the same problem. For the heck of it I thought I would make a query to use just the "inventory" table and only the "inventoryID" field and pull the last record but its getting stuck at ID# 721. There's a gap for ID's it starts again at 870 - 972 however the query doesn't see any of those records when using DLast, if its a normal query with no grouping it shows all records.

I thought it was the gap between ID's at first because it was stuck on a gap in the orders table as well but when I change vendorID in that table it affects the query and continues reading after the gap.

Its a very bizarre problem and its killing me, I've got some crazy queries going on that work no problem and in my mind this is the simplest query and it doesn't work.

Please help me.

[hertfordkc]

nulls.
When there are fields which generally contain values, I find I often have to use a calculated field:
Fld:NZ(FLD,0) ' or ""

While it may be grasping at straws, your comments offer some support for either nulls or superfluous blanks being the culprit. You comments suggested that records inserted by the two different methods weren't necessarily returned in the expected order.

HTH

[orange]

How are your VendorId and OrderId valued? Where do the values for these fields come from?

What do you mean exactly by the Last record?
I don't have acc2010. I have acc2003, but if you make an mdb version without any confidential data, I'd look at it.

[robsworld78]

Hi, thanks for the replies. I don't think its because of nulls because of the way it acts. I'm going to make a short video later and post to youtube so you can see exactly whats happening. I can try and explain but visual is much better.

Orange, what do you mean by how and and where for the ID's? The ID's are all autonumbers and when I do the sql inserts its pulling the values from the main form and subform on the main form. I tried making a 2003 copy before but it didn't work, maybe the video will show you more.

[orange]

Ok, autonumbers. I wasn't sure if you were assigning numbers, or got them from another source.

Not sure why you can't create an mdb.

[robsworld78]

Autonumbers are good right?

When I hit save as mdb it says "can't save because it uses features that require the current file format."

[orange]

Yes autonumbers for IDs is good.
You have some 2010 feature, so can't create mdb.
I wonder what the feature is?
Anyway, I'll wait for the video.

[robsworld78]

Glad to hear the ID's should be good. Yeah good question, I have no clue, maybe the cool looking buttons that come with access. Video will be late tonight, anything you might want to see?

[orange]

Not at this time.

[robsworld78]

Hi, well I still have this crazy problem, I tried a few other things but it still acts funny. I had one field that wasn't being populated in all the records and was linked to other tables so I've made sure that gets populated now but it doesn't make a difference.

I attached a video showing whats happening, remember this problem existed before any of those transfer records were being done. I posted to the forum for help on this months ago and thought it was solved but I don't believe it ever was.

http://youtu.be/SG78seLk0Zk

Thanks

[orange]

I watched your video. What did you use to create it??

But, I found it confusing-- not the video per se - but could not really follow the problem because of things like Van 28? and the yellow dot moving around the screen. It seems there is a lot of extraneous info for someone not intimately familiar with your set up and processes.

I think you have to state the exact problem and show it. Then work through the series of actions/vba/forms etc that are involved. I'd like to help but don't know exactly what can be done.

[John_G]

Hi -

One thing you have to consider is MS Access' concept of "Last" - it does not usually have one. Access does not have record numbers, and has no way of identifying the "Last" record entered; "Last" depends entirely on how the records are ordered on the screen or report.

You need to find a way of identifying the last record by using the actual data. The "Last" record entered might be the one with the maximum ID value, though since you are using autonumbers that is not 100% reliable. You might want to consider using Order number as your PK, but making it an ordinary Long Integer instead of autonumber, so that you have control over what it contains.

HTH

John

[orange]

I agree with John G regarding Last - that's why I asked earlier in post #3.

Do you record a Date/Time field when you create the record/update? If so, then your last Bin id would be associated with the record with the Max Date/time value.

[robsworld78]

The db is confusing and doing a lot. In short its for inventory control and is tracking inventory for a trailer and multiple freezers, vans, bins and vendors. When I originally made it I had tracking for everything but vans because I couldn't get the queries to do another layer so I came up with what I think was a pretty good solution. Before I go on as I mentioned this problem existed before these additions so I know its not because of them.

Anyways the confusing part for the vans is because vans are vendors. What I did was managed to convert the vehicleID to a VendorID. There are many types of vehicles and all are located in a table with a field defining what type of vehicle it is. It the type is a Van then the system converts the ID to a VendorID. What I did was in the Vendors table I added "Temp Storage" as a last name and the van number as a first name, so van 128 in the vehicle table also has a record in the vendor table with a first name of "128". Neither table are linked or related in anyway.

Then on all my forms that show vendors I added Not "Temp Storage" to the queries so they are invisible. Then I was able to create a query that has the vendors and vehicles table on them and created a relationship between the first name and vehicle number and they became one. So now the inventory for the vans always sit related to the temp storage vendor for that van. When a van is taken the inventory is removed from the van vendor and given to the real vendor and when the vendor returns the van the inventory gets transferred back to the temp storage. If a person wasn't in a van the inventory stays attached to there name because they are likely to take the same product again but with vans that never happens so I had to come up with this transfer this other wise we would always have to manually transfer the inventory from vendor to vendor.

Thats the best I can explain it, it has nothing to do with the problem though. The problem is only that its not pulling the last record out of the orders table. If you noticed in the video on the temp query I made that shows only the orderID it doesn't show the last orderID, that's the problem.

I've played with ordering my records but that doesn't make a difference. I've tried in tables and queries. I don't understand how the OrderID can't work, the last record always has the highest number.

I'll see if I can get a better video. Oh I used Debut Video Capture, pretty cool software.

[robsworld78]

I don't think I can use the Date/Time field because we place pre-orders which will have a later date. If this query pulled the last records I would have preorders omitted from it so it didn't interfere.