Results 1 to 5 of 5
  1. #1
    rodeogal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    5

    Combo Box at Table or Form Level - which is best?


    I am having a terrible time trying to set up queries to get the information I need for a report. I wonder if my DB is designed incorrectly. In the MainTable, there are 7 different fields that are combo boxes. Each field is tied to an appropriate table. I am trying to run queries that would count the instances of each of the combo box choices for each field.

    Example: Case type (choices are EEO, WC) need to know how many of each of these there were and send that number to a report
    Case win (choices are n/a, yes, no) need to know how many of each and send that number to the same report as above

    Would my queries be easier to build and data easier to sort if the combo boxes were on the MainForm instead of on the Table level? Does it matter? What is the best way?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Combo Box at Table or Form Level - which is best?
    Form level, never at table level, just because you can doesn't make it a good idea

    see this link http://access.mvps.org/access/lookupfields.htm

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I NEVER set lookups in table, especially if the lookup uses alias (saves ID while displaying description). If your lookups are aliased, then query will have to include the lookup table for expressions that rely on descriptive text.
    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
    rodeogal is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    5
    OK. If I put the combo boxes in the form, do I still use an auto number primary key in the choice table? Won't the database still use that number and I will still have to query off that number to make the queries work? Thank you.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    You have a table tblMainTable: MTID, SomeInfo, CaseID, Win, ..., where Win has values e.g. 0 and 1, which represent False/No and True/Yes;
    You have a table tblCases: CaseID, CaseText;
    On form fMainForm you have along with other possible fields combo boxes cbbCase with ControlSource = CaseID, RowSourceType = "Table/Query", RowSource = "SELECT * FROM tblChoices" , BoundColumn = 1, ColumnCount = 2 and ColumnWidths = "0;2.5" (you see CaseText, CaseID is selected), and cbbWin with ControlSource = Win, RowSourceType = "Value List", RowSource = "1;Yes;2;No", BoundColumn = 1, ColumnCount = 2 and ColumnWidths = "0;2.5".

    The query to count cases will be something like (on fly, you have to check the syntax yourself):
    Code:
    SELECT c.CaseText, COUNT(mt.CaseID) AS [CaseCnt] FROM tblMainTable mt LEFT JOIN tblCases c ON c.CaseID = mt.CaseID GROUP BY c.CaseText
    To count Win:
    Code:
    SELECT Iif(mt.Win=1,"Yes","No") AS [Win], COUNT(Nz(mt.Win,2)) AS [WinCnt] FROM tblMainTable mt GROUP BY Iif(mt.Win=1,"Yes","No")
    (when Win is not determined, then there is no win)

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

Similar Threads

  1. Grade level table and subjects table
    By Khalil Handal in forum Access
    Replies: 4
    Last Post: 12-10-2016, 05:20 AM
  2. Input Masks - table or form level
    By ibcormac in forum Access
    Replies: 2
    Last Post: 01-29-2016, 08:17 AM
  3. Replies: 5
    Last Post: 08-11-2015, 01:45 PM
  4. Replies: 1
    Last Post: 05-27-2015, 04:46 AM
  5. Linking Tables at the Table Level Vs. the Query Level
    By Tammy in forum Database Design
    Replies: 3
    Last Post: 12-03-2014, 01:34 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