Results 1 to 4 of 4
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    Multiple drop-down in a single cell

    I have an access table named "Import" where I have a column "Import_ID". It comprises 4 parts which help me identify each import consignment uniquely. For example "2018-19 Akh Cig 1" is a name of a single import and no other import has the same name. Seeing this I know which import is for which year, which station, what product and it's sequence. I insert data directly through table, without using any form. So, these 4 parts goes into a single cell ("Import_ID") as Short text format and with "Indexed: Yes (no duplicates)" constraint. My question is - Is there a way so that when I enter value to this cell I get 4 drop-down for the 4 parts but when entered completely they create a single cell value? They can come from a value list or I could have a table with 4 columns which feeds the 4 parts of "Import_ID" column but then converts them as a single value string. This would help me avoid typo for entering the field.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you had each part as a separate field, you could concatenate the parts for display.
    Relational database principles suggest you use atomic values for fields ---1 fact 1 field in a table.
    Experience says you should have all user interaction through form(s), never direct access to tables.

    Here are a few basic database "rules"
    1. Every row in a table must be unique. This is a requirement because if the relational calculus
    is to be reliable, we must be able to identify a specific row, for example, to be deleted. We
    cannot rely on the sequence of the rows; indeed, relational database theory specifies that there
    is no defined order of data in a table. This usually requires that each table has a primary key
    field or combination of fields that can never be duplicated within that table. The database
    engine itself normally guarantees that this state will be maintained; for example, if you try to
    enter a row of data with a primary key value that duplicates an existing value, the database
    engine will present an error message, and will not add the new data.

    2. Data should be "atomic". That means that data should be stored in its most basic, indivisible
    form; for example, avoid storing an address as a single data element, with street number,
    street name, apartment number, city, state and zip code. Each of these are separate pieces of
    data and should be stored in separate columns.

    3. Data should be single valued. That means that you should not have a single field that
    contains "children's names," for example. If you need to join children's names to their
    parents' records, do it with relationships between tables, not by entering multiple names into
    one field in the parents’ record.....


    You may wish to research Normalization.

    Good luck with your project.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Agree with orange, ID parts should be in separate fields.

    But to answer your question, yes, can have 4 UNBOUND comboboxes and then code (macro or VBA) can combine selections to store into one field.
    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.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    these 4 parts goes into a single cell ("Import_ID") as Short text format and with "Indexed: Yes (no duplicates)"
    agree with Orange, better to keep fields separate. You can use a multi field index with a unique constraint to prevent duplicates - and perhaps some of those fields already exist in your imported data and perhaps the others can be calculated.

    I insert data directly through table, without using any form
    And to add to June's suggestion, you will need to use a form

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

Similar Threads

  1. Expand entire row not just single cell in a Report
    By CityOfKalamazoo in forum Reports
    Replies: 2
    Last Post: 07-28-2016, 09:22 AM
  2. msgbox if cell changes from drop list
    By justlearning123 in forum Programming
    Replies: 2
    Last Post: 06-28-2015, 07:43 AM
  3. Replies: 4
    Last Post: 09-19-2012, 11:49 AM
  4. importing a single cell from excel onto access
    By virus100 in forum Import/Export Data
    Replies: 0
    Last Post: 03-20-2010, 11:57 AM
  5. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 02:16 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