Last Non-Null Field for Each Item

[springa]

Good morning,

I am having a bit of an issue with a MS Access Database that I have created.

I have a table with the following fields: ID (Primary Key), Date, Entered By, Item ID,Transaction Detail, Warehouse Location, Units Received, Units Dispatched, Units Disposed Of. To track inventory movements.

Warehouse location is only entered for Units Received which could be of Transaction Detail: "Opening Stock" or "Transferred To Warehouse". When stocks are entered as either of these types, a Warehouse Location is entered.

I would like to run a query to give me the last known Warehouse Location for each item (i.e. no null values). This doesn't seem to be a simple "is not null" query and I have been battling with it for a few days now. I can either get only items who's last transaction includes a warehouse value or I get multiple warehouse locations per item.

Please help, this is driving me mad!

 

[namliam]

Use a group by query to fetch the last value...

failing that try a two step approach.
1) Get the highest 'is not null' ID (presumed to be autonumber field)
Select Max(ID) , [Item ID]
from YourTable
Group By [Item ID]

2) Use above query to fetch the results from your table.

Good luck!

P.S. Welcome to (one of ) the best access forums on the Internet.

 

[springa]

Hi NiamLiam,

Thanks for your help, but still doesn't solve my issue? I have a table that looks like this (picture attached).

What I need to do is return the last know Warehouse Location, i.e. for EL Display Stand, I need to return value A17 and for Table I need to return value A29. This will assist us with placing the same item back in the correct location when it is returned from store as well as giving us the location of items currently in warehouse?

I really appreciate your help, thanks very much.

 

[namliam]

Hi NiamLiam

Hard to get a name right??
Would it have hurt you to format your table so it is actually readable?

Did you even try my solution?? I do notice I forget to add "Where [location] is not null" to the first query.
This combination of 2 queries should give you the last known location.

 

[springa]

Hi Namliam.

Sorry about that, this is my first time posting on a forum. I have tried your solution. Have included a picture of the table in my last post.

Thanks again.

 

[springa]

Also, Item ID is also unique and has been linked to another table with various item details, i.e. item category, item description, hyperlink

 

[namliam]

My solution still stands??!!

You have tried my solution, did you also add in the "is not null" part??
This should work!

 

[springa]

Hi NamLiam,

Still can't seem to get it to work. I'm probably missing something. I've attached a pic of my queries in design view and their results.

 

[springa]

Correction to picture: I have grouped warehouse location and comes out with same IDs with multiple warehouse locations, not last location.

 

[namliam]

I see your problem... Let me spell it out a little more clearly...

Query1
This is a group by query....
Select Max(ID) , [Item ID]
from YourTable
Where [location] is not null
Group By [Item ID]

Query2
This joins Query1 with your original table based on ID to join to your Max(ID)

 

[springa]

Sorry, I'm sure you must be losing your patience with this, but I've got query 1 up and running smoothly, this gives me the correct IDs for the warehouse locations I want per Item ID. I am battling with the second query to link the two? Thank you so much for helping me with this!

 

[namliam]

Patients is in large supply with me, dont worry. (particularly today)

Just add both Query1 and YourTable to your query design.
Now click and drag the MaxID from Query1 over the ID in YourTable.
This is called "Joining".
Now add the needed columns from YourTable to the query like you normaly do (to show them in the result)

This now shows only the full result from the IDs as they were retrieved by query1.

 

[springa]

Thanks so much! Up and running, it was just changing the relationship that had been messing up my results, working perfectly now. Thanks again!