Return nth Record in query (referencing another query without row numbers)

[bjstyl2]

I can't seem to figure out how to resolve my issue despite looking at many sources. It appears Access does not inherently have the ability to do this, but I am hoping the experts may have a workaround. Any assistance is greatly appreciated.



Table A:

Location 1	Location 2	Item ID	Item Name
Shelf 1	Shelf 2	123	Widget 1
		456	Widget 2
Shelf 3		789	Widget 3

Table B:

Location	Item ID	Item Name	Qty
Shelf 1	123	Widget 1	5
Shelf 2	123	Widget 1	2
Shelf 3	789	Widget 3	10

[orange]

What exactly is your question -in plain English?

Table A is unnormalized (Location info is repeated-- it appears)


In Table B shelf 2 123 widget 1 ?? What happened to widget 2 and widget 3

[bjstyl2]

I'm sorry, think looking at all this stuff has made me crazy. I would like to return 2 potential locations for an item, if they exist.

Query A is actually my results query. I would like to see these results returned if the item exists in Query B.

Example: I want to locate Item 123 -> result is that it should be on Shelf 1 and shelf 2 (backup option)

Hope that makes sense. Appreciate the assistance and patience.


Results Query:

Location 1	Location 2	Item ID	Item Name
Shelf 1	Shelf 2	123	Widget 1
		456	Widget 2
Shelf 3		789	Widget 3

Query B:

Location	Item ID	Item Name	Qty
Shelf 1	123	Widget 1	5
Shelf 2	123	Widget 1	2
Shelf 3	789	Widget 3	10

[orange]

Still not sure I understand your set up.

If I had a number of locations, and a number of shelves, and a number of items, I might use a database structure such as

Location --->Shelf--->ShelfHasItem<---Item

which represents these business rules:

There are several Locations
There are several Items
A Location has 1 or Many Shelves
A Shelf may have 0,1, or Many Items
An Item may appear on 0,1 or Many Shelves.

This set up involves 4 tables.
Location which has info about the Locations Only
Item which has info about Items Only

Shelf which is info about a shelf and a foreign key to identify the Location in which this shelf exists

ShelfHasItem which has info about Shelf and Item. This known as a junction table. It contains a Foreign key to Shelf, and a foreign key to Item to show "this shelf has this item", or "This Item is on this shelf".

The ----> represents a 1 to Many relationship between the tables at either end of the --->.

I may not have understood/interpreted your needs correctly.
Good luck.

[bjstyl2]

Thanks for the help yesterday on this. I think I understand what you are saying, but still can't achieve my desired result. I have further outlined it below. Essentially I need to know what location to send the picker to go to and find the item. If the item does not exist there then i would like to have a backup on his list. (ultimately i would like 3 locations). I am able to accomplish this in excel using a countif function, but would like to automate this in access if possible.

Thanks again for any assistance.


Location Table

Item Description	Item Name	On Hand	Location
Product 1	123	1	SA-70
Product 1	123	5	SA-87
Product 2	ABC	1	SA-01
Product 2	ABC	10	SA-09

Order Table

Order Date	Item Name	Order Qty	Customer Info
2/25/2015	123	1	xxx
2/25/2015	123	1	xxx
2/25/2015	ABC	1	xxx
2/25/2015	ABC	1	xxx

Desired Result

Order Date	Customer Info	Item Name	Item Description	Location 1	Location 2 (backup)
2/25/2015	xxx	123	Product 1	SA-70	SA-87
2/25/2015	xxx	123	Product 1	SA-70	SA-87
2/25/2015	xxx	ABC	Product 2	SA-01	SA-09
2/25/2015	xxx	ABC	Product 2	SA-01	SA-09

[orange]

Seems like we're not communicating clearly. I was suggesting an alternate, normalized structure that should address your issue. However, you have added Customer and Order to the mix and I am not sure where that fits. A Customer typically is someone who buys/orders Products/Services from you --which is how I understand your post.
When you mention picker , which I interpret as someone who physically gathers Items/Products to fulfill an Order.
So your database structure changes based on these new entities.

Since your Items/Product are stored on Shelf(s), and (as mentioned in earlier post) there may be many Shelf(s) per Location, you need to include Shelf in your algorithm/logic.

As you have no doubt realized, Access and Excel are different animals. Getting your database structured to meet your requirements is key to a successful database application.

In my view (if I have understood your business processes) you need to restructure your tables and relationships as mentioned in post #4. Also, you would need to include

Customer --makes--> Order---contains--> OrderItems

These OrderItems would become the PickList.

Then, you would need a physical inventory that shows which Items(Products) are on which Shelf in which Location and the current quantities of each product.

From the picker's perspective, he would identify the Location and Shelf for each item on the Picklist, and "pick" those Items, and reduce the quantity of that Item on that Shelf at that Location.

Most developers would set up a transaction approach to the current quantity of Product at each Shelf and Location. You take a physical inventory and get current quantities as of some Date/Time.
Then each time the picker picks an Item(s) the number of Items picked is a negative transaction. When you replenish Items, from a Supplier/Manufacturer, then each item added becomes a positive transaction. For each Shelf, at each Location, you have an inventory or Products. Using your latest physical count, and the transactions since that count, you can determine the current quantity of Items by

Current quantity of X = Latest Count of X - negative trans for X + positive trans for X

In your situation you need this for each shelf at each location for each item.

This is something you can design and test on paper. You may need some refinements, but once you get a data model that you can test with some sample data, you can redesign the database with confidence--knowing the design meets your requirements.

You should review this Allen Browne article.

Good luck.

Also: I don't see how your post's title reflects your needs.