Results 1 to 4 of 4
  1. #1
    DBR138 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4

    List each item in new table the number of times in one field

    I am fairly new to Access and came in a little over confident based on my Excel abilities which aren't as easy to translate as I had imagined. In Excel I would use VBA but I'm not familiar with it here and there might be a better option. I would really appreciate it if someone could help guide me thru this.

    I have a table named 'tbl_2nd Floor Open Replen Picks' that is my source data. It looks like this:
    ITEM_NO WHSE_CODE ITEM_UM MIN_OH SumOfLOCT_ONHAND ORDQTY PICK_LOC Level STD_CONT_QTY # OF CONT Run Time
    2168-4993 S LBS


    39 5.08 2220 On Site Store B 553 4 1/9/2018 5:19:18 PM
    B582C92 F LBS 7176 251 4784 Y213 C&D 55 9 1/9/2018 5:19:18 PM


    I need to make query that will populate a different table ' tbl_2nd Floor Cont Picks' with all of the items in the source table but I need each item listed the number of times in # OF CONT. Based on the example above the line 2168-4993 would be listed 4 times and B582C92 would be listed 9 times. I can do this in Excel with VBA very easily but I am struggling to apply that to Access.

    Thank you

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Helo and welcome to the forum

    Your post raises lots of questions.

    In a database, you normally only store data in one place (table)
    Why do you want to duplicate it?
    Does the destination table have the same structure?
    Why do you need multiple rows?
    Would a query achieve the same result?

    Suggest you explain the purpose of doing this process

    Also strongly recommend you get rid of all spaces, underscores & special characters like # in table & field names as they will cause you problems as you proceed.
    Better to make changes at the start
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    DBR138 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    4
    Quote Originally Posted by ridders52 View Post
    Helo and welcome to the forum

    Your post raises lots of questions.

    In a database, you normally only store data in one place (table)
    Why do you want to duplicate it?
    Does the destination table have the same structure?
    Why do you need multiple rows?
    Would a query achieve the same result?

    Suggest you explain the purpose of doing this process

    Also strongly recommend you get rid of all spaces, underscores & special characters like # in table & field names as they will cause you problems as you proceed.
    Better to make changes at the start

    Thank you so much for taking the time! First let me say how refreshing your suggestion on the spacing and special character is. I shuddered when I saw that as well but sadly there is very little about that I can change. Here is the backstory on what I am doing here.

    The source table I am using is populated by a query that runs off multiple ERP system linked data bases and is used for other things. Essentially it is a list of items we need pulled from stock to replenish the work area stock. The new table does have the same structure. I plan to use the new table to generate a report that would print onto our pick labels and I need one label for each container which is why I am creating multiple lines.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just because the source data is badly named with spaces/underscores & # signs, , it doesn't mean your Access tables need to be.

    Your ERP data sources should be linked tables & the data should then be imported into one or more normalised (well structured & named) access table(s)

    The report can be run from the original table - it doesn't need to be duplicated.
    Or possibly from a query.
    Either way, the report can be setup to create one label for each item number & container

    You need to think about using Access to help you rather than forcing Access to do something completely unnecessary.

    I'm about to sign off as its very late here but perhaps someone else will offer their thoughts on this
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 8
    Last Post: 12-13-2017, 10:38 AM
  2. Replies: 15
    Last Post: 10-08-2015, 03:33 PM
  3. Replies: 2
    Last Post: 02-19-2013, 04:02 PM
  4. Replies: 2
    Last Post: 04-05-2012, 08:39 PM
  5. Replies: 3
    Last Post: 08-24-2010, 07:40 PM

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