Need query to look at previous record value

[anissw]

Hi- I have a table that will have ITEMID as unique field but location could have save values. I tried using DLOOKUP to look at the value for the previous record to see if I need that value is same as current, however, since my data query are text formats, I am unable to. I get a message of mismatch data type. Can anyone help me to apply my query to look at sorted table by Location and ITEMID and if previous location = location, then my area=0?

Thanks-
anissw

 

[pbaldy]

This should fix the DLookup():

http://www.mvps.org/access/general/gen0018.htm

But I'd probably use this:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[anissw]

Hi- Thanks for replying. I think the second link worked in identifying the Location. I'll let you know the status.

 

[anissw]

Hi - I was able to identify the unique locations and assign accordingly, however, is there a way that based on the first unique location, I can assign area field once so that I can calculate my area to charge one location for all item IDs that are in the same location? I have attached a sample test file to give you a better idea. I have multiple items that are stored in 1 location and have to calculate area_ft_2bill4, however, I cannot charge for each item if the location is the same. They can only be charged once

Thanks-

anissw

 

[pbaldy]

I'm not clear on what you're trying to achieve. What result would you expect from that table?

 

[anissw]

Hi- I apologize, I just realized my second question to you was redundant and the solution you provided worked perfectly. Sorry for the confusion. The SELECT TOP 1 link worked perfectly!!

Again thank you-

 

[pbaldy]

Happy to help!