Results 1 to 15 of 15
  1. #1
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7

    Limiting Rows for Multiple Querys

    Hello, I am pretty new to Access but I would like to start using it instead of Excel to save time and mostly automate my daily routine.

    I create 'pick routes' in my warehouse for the pullers to go and grab parts for orders. In the past I have been downloading inventory locations for each part (which are text files that Excel and Access can easily understand), and I have been picking out (up to) five different locations for each part number to choose from. Then I paste them all into one spreadsheet and sort out the locations alphabetically (while keeping the same part numbers grouped together). This makes an easy to follow 'route' throughout the warehouse to pull all the parts I need for the particular pick.

    Anyways, I want to do this in Access because downloading each part number data and cutting, pasting, organizing, etc. takes a lot of time. Couple things to note here:

    -The warehouse is laid out alphabetically by location


    -Some parts may have only one location or several hundred, depending on what's in stock
    -Part numbers that are the same must stay grouped together in rows
    -The main data table containing all part numbers and locations has to be updated daily since Access is not linked to the live online database we use

    So far I have learned how to create a database and run a query. I can select all of the part numbers I want, but I can only have it display every single location for that part which is not only unnecessary but wastes paper and makes it harder for the puller to keep track of where they are on the route or how many parts they are supposed to pull.

    My question is: Is there any way I can filter out the list so it only displays up to 5 locations for each part number? Using the "Top Values" function in the query properties only displays the first five parts on the list, not five of each part number that I requested in the query.

    And one more question: After making the query somehow display up to five locations of each part number, can I still sort the resulting locations alphabetically and still keep the part numbers grouped together?

    Thanks in advance.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you give us an example of a "pick route" for a particular Part that may be in several locations? Simple terms, no jargon. I'm not really following your request.
    Good luck.

  3. #3
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Click image for larger version. 

Name:	picklist.png 
Views:	29 
Size:	60.9 KB 
ID:	42164

    I've attached a sample pick route. This came from what I normally make in Excel, but would like Access to handle all the processing for me (so all I have to do is run a query to make a similar list and paste into Excel or generate a report to print). You can see the PartNo field has each of the same part number grouped together, and all locations are sorted alphabetically. This isn't entirely necessary if I can just provide up to five locations per part number. It's more so to keep the rows from being blank but also to not mix up what parts are in what locations and to keep the puller from having to make several trips around the warehouse.

    With our inventory system, I am able to download locations for individual parts (which is what I do when making these lists in Excel) or a complete list of warehouse inventory, which is what I import into Access.

    The reason I would like to have five locations for each part is to provide the puller with options to pick the best looking part in inventory, since appearance is a very high requirement with our shipping partner. I keep them grouped together so that the puller marks which location they pull the part from before returning the list to me for inventory corrections.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is output you want? Does it also represent raw data source structure? What is decision process for selecting parts - how would you make selection in your head?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am surprised that each part can be stored in 5 or more places. Are all parts with same part number not the same/identical?
    You may also want to review this article on Inventory by Allen Browne.

  6. #6
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by June7 View Post
    That is output you want? Does it also represent raw data source structure? What is decision process for selecting parts - how would you make selection in your head?
    The source data is organized in the same fashion with identical fields. I just choose the part numbers I need and up to five different locations. When selecting/sorting them I look at the entire location list for that one part number and choose the locations that best fit alphabetically with the other parts on the pick list. Generally I have to cut and paste other part numbers out of the way and stick a new one in between for everything to remain alphabetical.

  7. #7
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by orange View Post
    I am surprised that each part can be stored in 5 or more places. Are all parts with same part number not the same/identical?
    You may also want to review this article on Inventory by Allen Browne.
    We have thousands and thousands of locations (bins or shelves), each with several parts inside of them. Parts can be put anywhere when they are entered as data so long as it has a location attached so we know where exactly it is. Because we have more than one part in stock (of a given part number) it can have several locations depending on where it was entered.

    To give you an idea of how much stock I am dealing with, the complete inventory list has over 2 million records. Each row contains the brand, part number, location, who entered it into that location, the quantity of that part in that location, when it was entered, etc.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Spec that stops me cold is "choose the locations that best fit alphabetically with the other parts on the pick list". Criteria that relies on data in other records of same table is never simple.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Very interesting - I don't think I've seen another post with parts in inventory stored in multiple locations and alphabetically. Nor multiple different parts in the same Bin. But then again I haven't worked in inventory nor fulfillment.
    Perhaps you could tell us more about this approach and the significance of the "5 locations". Are these closer to the "picker" in some manner?

  10. #10
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by June7 View Post
    Spec that stops me cold is "choose the locations that best fit alphabetically with the other parts on the pick list". Criteria that relies on data in other records of same table is never simple.

    At this point I think Access probably isn't the best tool to use to do this, since I already have databases built in other software. It seems like to get anywhere close to what I need to do I have to build the entire database again in Access.

    Quote Originally Posted by orange View Post
    Very interesting - I don't think I've seen another post with parts in inventory stored in multiple locations and alphabetically. Nor multiple different parts in the same Bin. But then again I haven't worked in inventory nor fulfillment.
    Perhaps you could tell us more about this approach and the significance of the "5 locations". Are these closer to the "picker" in some manner?
    Up to 5 locations for each part is just to provide the picker with options when it comes to picking out a specific part. The picker does not know the locations unless I give them out, and I chose to display up to 5 just to cut down on the amount of paper I print out. Usually there is a part suitable for shipment in one or more of those five locations, as we cannot send out damaged or old products.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you show us a picture/graphic of your tables and relationships (database design)?
    When you say display up to 5 locations, does that mean you have multiple locations(more than 5) for each Part?
    You " currently have a database in other software" can you tell us a little about the software and its effectiveness?
    It's not that to work with Access you'd have to start from scratch, readers are trying to understand the "business and its rules" in order to offer advice/recommendations.

  12. #12
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by orange View Post
    Can you show us a picture/graphic of your tables and relationships (database design)?
    When you say display up to 5 locations, does that mean you have multiple locations(more than 5) for each Part?
    You " currently have a database in other software" can you tell us a little about the software and its effectiveness?
    It's not that to work with Access you'd have to start from scratch, readers are trying to understand the "business and its rules" in order to offer advice/recommendations.
    For some background, our warehouse ships automotive parts for certain online parts retailers that shall remain nameless (so I don't get into trouble). For our "main" shipping partner, we ship out thousands of orders a day and use an online inventory management along with Java apps for picking out parts and adding/deleting parts from inventory.

    It goes like this:
    -A customer orders a part or a set of parts.
    -The order is transmitted to our online order report.
    -A pick is generated (a pick in this case contains anywhere from 5-20 orders, depending on how many parts are on the orders) and sent to one of many pullers in the warehouse to the laptop on their forklift.
    -The laptop has a 'pick program' which is a Java application linked to our inventory. It tells the puller which location to go to, what part to pull and how many of that part to pull.
    -The puller marks the part as pulled in the Java app and it is automatically deleted from inventory. If for some reason the part is not there, the part is not suitable for sale (damaged, missing parts, etc.) then the puller marks the location as zero and it will provide another location to pick from.
    -After the pick is completed, all the associated packing lists for those orders are printed to one of many printers around the warehouse and the orders are then sorted out into totes with their packing list and given to packers for the rest of the order processing.

    That is how shipping orders works. For receiving new parts:

    -Parts come in.
    -Any new parts that do not have existing information in inventory (with their part number, description, etc.) are created in the database and added to the online catalog the customer uses to purchase parts.
    -Parts are separated into pallets and distributed to several forklift drivers to be placed into inventory.
    -They find somewhere to put the part, and using another Java app enter the location they put the part in and enter the part number and quantity that was placed into the location.
    -These parts are now in inventory and can be pulled for new orders.

    That is how shipping/receiving works with our main shipping partner. I am managing orders for our newest shipping partner, which uses a completely different shipping platform that is not linked to our inventory. We have someone that enters the parts into the online catalog and these orders are then transmitted to the shipping platform I use. From there I manually generate the pick list for these orders, and because I do not have a Java app to automatically give new locations if a part is not suitable for sale, I have to provide a few locations to pick from on the list I manually generate. The puller then marks which location on the list they pulled the part from, and then I delete these parts from inventory manually. What I'm trying to do is basically make Access work like the Java pick program, since Access can interpret the complete inventory lists I am able to download. Otherwise, I just have to look up each part number for each order, download the location list for each and just pick and choose locations to make a nicely laid out pick route in Excel.

    The reason I have to do all this manually is because this new shipping partner has much, much lower order volume and we have been shipping with this partner for a much shorter time. With our main shipping partner we ship out thousands and thousands of orders a day, but with this shipping partner we currently ship out less than 100 orders a day. That being said, the order volume is growing every day and it becomes increasingly more time consuming to manually generate these lists, to the point where I am not able to leave enough time in the day for the parts to actually be pulled and shipped in a timely manner.

    A couple more notes:

    -The warehouse is laid out alphabetically as follows, we'll use location BA45C16 for example. BA represents the aisle, and these aisles are placed alphabetically down the warehouse. There are several aisles, from AC, BA, BB, BC, CA, CB, CC, DA, FA, FB, GA, HA, etc. 45 represents the section in that aisle, where each section will have rows A-M. C represents, well, row C, and 16 is the bin in that row. So to get a part from BA45C16, the puller drives the forklift to aisle BA, goes down to section 45, goes up to row C and pulls out bin 16 to get their part.
    -The pick list I generate has to be laid out alphabetically by location so the puller does not make several trips around the warehouse, as I have found it in the past that wastes quite a large amount of time since these forklifts only drive at about 5 miles an hour in a 1,000,000+ sqft warehouse.
    -In Access all I have is one table containing all ~2.1 million records (every part number and location in the entire warehouse) and one query with the specific parts I need for a particular pick. I do not have any relationships.

    If anything, I think I would be able to get a couple new laptops in with a new Access database to use for this shipping partner's orders. That would negate the need to limit results as there is no paper to waste; though a drop down list for each part number would be beneficial, if Access can do that sort of thing.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks for the fuller description. Bins are alphabetic, that makes more sense. I had the impression you were describing part storage by 'alpha-partname'.


    But a word of caution! for consideration.
    Having 2 separate Inventory systems/applications (java-based, and an Access based smaller system) to be used by several pickers is prone to issues. If the 2 systems/databases don't get synchronize "auto-majicaly", then you're never quite sure of the contents(parts contained) in any Bin- again a source of issues. This could be considered a silo approach.
    A single table "database" for an Inventory is a non-starter --you really should start with a normalized structure and learn/understand/become familiar with database concepts.

    What exactly is the current status of this proposed Access database? Do you have it populated as a single table now?
    Are there plans to move this new "customer" into the java-based application?
    Is there a IT or database group involved - or - is this something you're just thinking about?

    In your own view, what would be the ideal situation given the customers you currently have and the expectations for the business for the future?

    NOTE: Here is a 26 minute video on a building a Stock Control system in Access. You will get some ideas of Access' capabilities and an appreciation of database design.


  14. #14
    achulz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    7
    Quote Originally Posted by orange View Post
    Thanks for the fuller description. Bins are alphabetic, that makes more sense. I had the impression you were describing part storage by 'alpha-partname'.


    But a word of caution! for consideration.
    Having 2 separate Inventory systems/applications (java-based, and an Access based smaller system) to be used by several pickers is prone to issues. If the 2 systems/databases don't get synchronize "auto-majicaly", then you're never quite sure of the contents(parts contained) in any Bin- again a source of issues. This could be considered a silo approach.
    A single table "database" for an Inventory is a non-starter --you really should start with a normalized structure and learn/understand/become familiar with database concepts.

    What exactly is the current status of this proposed Access database? Do you have it populated as a single table now?
    Are there plans to move this new "customer" into the java-based application?
    Is there a IT or database group involved - or - is this something you're just thinking about?

    In your own view, what would be the ideal situation given the customers you currently have and the expectations for the business for the future?

    NOTE: Here is a 26 minute video on a building a Stock Control system in Access. You will get some ideas of Access' capabilities and an appreciation of database design.

    Currently this 'project' of sorts is in the beginning phases with just a single table I grabbed, mostly because I'm not experienced with Access and I can tell it will take time for me to build said database. I do agree with your point though that it is essentially an information silo, which is not what I am aiming for. If I were to keep working at this, I would ideally like to easily update the database say, every day or two, just to keep it synchronized with our Java based inventory system, but that just introduces even more work and probably isn't the best approach.

    The plans with this shipping partner is much, much more growth into what would eventually be a separate department I oversee. I manage orders for both currently but my position will be filled and I will work full time fulfilling orders with the new shipping partner in question. The reason I am beginning this Access database is because the work flow is already getting to the point of being overwhelming, when combined with my other duties. We do have an IT guy that writes the Java apps we use, but how long until he writes an app for this new shipping partner, I have no idea. I'm just trying to do everything in my power to make it easier for the time being. But diving into Access especially when I'm unfamiliar with it is probably hurting more than it is helping.

    The ideal situation would be to write another Java based app that is integrated into our existing system but is solely for processing orders with the newer shipping partner. The shipping platform is different, the shipping carriers are different, but the inventory is all the same; so I think a simple, but separate Java app linked to our live inventory will work to automate what I am currently doing manually (and free up a lot more time).

    I can definitely see Access as being a useful tool, clearly it's very powerful and I'm only scraping the surface of what it can do, but I do not have the resources or time to build an entirely new infrastructure, especially one not synchronized with our current inventory system. I think the best thing to do in this situation is to keep my head down and spend the time in Excel until I can work with the other management staff and IT to come up with a better solution.

    Thanks for the help!

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If this new Customer is important to "the business", then getting that Customer into the operational environment would seem to be a priority for management. Since it's the same inventory but different shipping platform and carriers, perhaps there is a way to update the java to allow for various platforms and carriers. I don't know your environment, but it seems, as a business, you have a new customer, ordering the same parts from you existing inventory of 2+ million parts. So management can evaluate some options to get that Customer "into the fold" without developing new systems that are not compatible with what exists.

    There is more to database than buying software and a few laptops --and a lot of successful database is dependent on design and business rules and management support.

    It seems another system dealing with same parts, pickers and picking, but under different software and fraught with manual interoperability/synchronization/maintenance issues is not something management would support.

    But you will probably have to make a case for something if the workload is increasing and that business is important. You wouldn't be the first martyr to try and do it all on your own --get some management buy in for whatever you decide is appropriate.

    The video I suggested will give you an appreciation of Stock Control database and the design effort.

    Good luck.

    The Database Planning and Design link in my signature has many articles that may be useful in your situation.

    You will get a quick experience with database design if you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link. You have to work through the tutorial (about 45 -60 minutes) but you will learn and what you learn can be used with any database.
    Again good luck with your decisions and project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-01-2019, 06:01 AM
  2. Replies: 2
    Last Post: 08-01-2018, 03:58 PM
  3. Multiple Append querys
    By Homegrownandy in forum Queries
    Replies: 14
    Last Post: 10-27-2017, 02:26 AM
  4. LIMITING Query results from multiple tables
    By moneypennie21 in forum Queries
    Replies: 8
    Last Post: 07-18-2017, 06:36 AM
  5. Mail Merge multiple querys
    By Homegrownandy in forum Queries
    Replies: 1
    Last Post: 10-16-2015, 04:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums