Results 1 to 6 of 6

entering multiple values into the same field and separating later.

  1. #1
    chriswrcg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    25

    entering multiple values into the same field and separating later.

    In excel it is possible to enter multiple values into one cell separated by commas and then on another worksheet pull that information apart and place each individual value in i'ts own cell.



    I wanted to do it another way in access bu just giving each value it's own field from the start but I quickly ran out of space as I am getting you have exceeded the size of the database error messages. I solved this by creating multiple tables to hold the data but I got the same thing when I tried using the form wizard to combine the multiple tables into one data entry form. the form will not hold all the fields I need it to to work. The amount of field entries is just to high.

    Either I have to create two data entry forms (which I do not want to do for complexity issues) or combine a lot of the fields into one an break them up later in a report.

    What can I do or what should I do? Is it even possible for access to separate one field into many?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,736
    In general a field should hold a single piece of data. It sounds like your data isn't normalized. What are all these values? They may belong in a related table as records.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    chriswrcg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    25
    I track associate performance in a warehouse. I need to enter the individual task they performed during the day which can be one or up to 15 (15 is the most I have seen someone do so I capped it at 15) How many trips it took them to complete the task and what exceptions they faced while performing the task. An exception in my world is something the associates has to stop "production" to handle. This could be lunch, break, bin issue, found damage product and so on. I can not limit the number of exceptions they can use but I found that 12 was a good cap for them .

    this means I need fields for each (task code, task start time, task stop time, task trips) X 15 and then 12 exception fields for each of the 15 task spaces. That is a grand total of 240 fields just for the task and exceptions that does not include the associate information of name, department, shift, clock in clock out and so on and so on.

    Access will not let me put all that information on one table let alone one form. so I ether combine or separate and separate adds relationships ( which I barley understand) complexity.

    I would upload the Database I am building but even without data in it yet it exceeds the file size limit of the forum.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,406
    Your problem is that you are approaching this as one might with a spreadsheet. If you normalized your design, you could have unlimited exceptions or tasks for a person for any given day. You've indicated an issue about understanding relationships, but maybe a bit of reading would help you before we try to accurately grasp the nature of the business at hand.

    As long as I'm at it, I might as well throw in the kitchen sink so you can avoid common pitfalls as you go rather than have to fix things later.
    Normalization is paramount. Diagramming maybe not so much for some people.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  5. #5
    chriswrcg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2018
    Posts
    25
    Yea your right, I am very versed in Excel Spreadsheets and spreadsheet design. I am actually trying to build a database to replace a spreadsheet I have built for this purpose since people tell me using a data base is better. I am using excel logic in access and its not working. Time to start reading.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,406
    A database is better for storing and relating/mixing/mashing data. It's not really the tool for complex calculations or charts, or for some of the complex functions that Excel provides for working with numbers. Spreadsheet data is arranged horizontally; database data should be thought of as row based. As you will find when you read, a particular row (record) is related to other records in other tables by using common (related) data between them. I'd suggest you ensure you grasp normalization and the relational concept as it is pretty much the foundation upon which you will build. If those links don't do it for you, find some others. A key concept to grasp is that a table is an entity; the fields are attributes. Your table should only contain fields that are attributes of the entity, plus whatever fields are required to relate its data to another table. For example, tblOrders would likely have OrderDate and QuoteNumber but not QuoteDate. The latter is an attribute of the quote, not the order. QuoteNumber would be there only to relate the order to the quote record.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-02-2018, 08:30 AM
  2. Replies: 1
    Last Post: 08-01-2017, 11:14 AM
  3. Replies: 19
    Last Post: 09-09-2014, 12:36 AM
  4. Replies: 3
    Last Post: 07-15-2014, 02:28 PM
  5. Separating VALUES in FIELDS/COLUMNS
    By taimysho0 in forum Queries
    Replies: 11
    Last Post: 11-30-2011, 04:32 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
  •  
Tech Forums: Microsoft Office Forums