Results 1 to 9 of 9
  1. #1
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34

    Table/Form Design


    I'm new with Access, so I need a little help with designing my access database.
    I’d like to create a form where the user will have a dropdown for 2 fields (Size ID & Size Brkdn ID), another field will calculate Total Pcs based on the code selected on Size Brkdn ID.
    The Total Pcs will be used as part of another calculation.

    When user chooses the Size ID = B and the corresponding SizeBrkdn ID = A, it should display on the form:

    Size ID
    B
    S
    M
    L
    XL
    Total Pcs
    Size Brkdn ID
    A
    1
    2
    2
    1
    6

    Contents of each table
    Size ID TABLE
    Size ID
    A S M L
    B S M L XL
    C S M L XL 2XL
    D S M L XL 2XL
    E S M L XL 2XL
    F XS S M L XL
    Size Brkdn ID TABLE
    Size Brkdn ID
    A 1 2 2 1
    B 2 2 2 2
    C 2 3 1
    D 3 3 3 3
    E 4 6 2
    F 4 4 4 4
    G 2 2 2 2 2



  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are a number of free data models at
    http://www.databaseanswers.org

    You may find something to help you with design.

    Here is a great article on the principles of Database Design.
    http://forums.aspfree.com/attachment...2&d=1201055452

    Since you are new to database you may find this free video tutorials a good example of concepts.
    https://www.youtube.com/watch?v=xNJZ...eature=related

    Since you're new to Access this set of videos will definitely be helpful.
    https://www.youtube.com/playlist?lis...A7CD9866BA79BE

    Good luck.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    One method would be a multi-column combobox. Review http://datapigtechnologies.com/flash...combobox3.html

    Then calculation in a textbox could sum the combobox columns. Combobox Column(index) starts with 0 so column 2 is Column(1).

    =[comboboxname].Column(1) + [comboboxname].Column(2) + [comboboxname].Column(3) + [comboboxname].Column(4)
    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
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    I used the combobox. It does display correctly on the drop down list, but once I click on the one I want, it only displays the bound column. How do I make it display all columns? I would also need it to display all columns on the report.

    Thanks!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    To display all the columns would mean constructing a column with concatenation, like:

    SELECT SizeID, Field1 & " | " & Field2 & " | " & Field3 & " | " & Field4 As Sizes, Field1, Field2, Field3, Field4 FROM tablename;

    Or have individual textboxes reference each column: = [combobox].[Column](1)
    Last edited by June7; 11-09-2012 at 05:33 PM.
    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.

  6. #6
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    Hi June,
    I'm not sure if I understood you correctly.
    Created a combobox that displays all the size codes and once chosen updates the text boxes that references each column. But it is not saving the contents on the text boxes.
    I have attached my test db. Can you quickly see what is wrong if you don't mind?

    Thanks!
    Attached Files Attached Files

  7. #7
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    Ok, I think I figured out question earlier. Instead of creating an after update code to update the txtfield.
    I was supposed to reference the columns within the control source of the txtfield.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Reference the combobox columns in the ControlSource of textbox.

    Set uneditable (because of expression) control TabStop property to No.
    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
    andy-29 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Culver City
    Posts
    34
    Cool, thank you!

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

Similar Threads

  1. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  2. Replies: 8
    Last Post: 03-24-2012, 11:03 AM
  3. please help to design a table
    By oas in forum Database Design
    Replies: 3
    Last Post: 02-11-2012, 08:54 AM
  4. Table design
    By eacollie in forum Database Design
    Replies: 5
    Last Post: 06-21-2011, 03:33 PM
  5. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 AM

Tags for this Thread

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