Results 1 to 8 of 8
  1. #1
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23

    Best way to store work region - Yes/No field or junction table

    First I'll show the form, to give an idea of what functionality I'm trying to provide... basically the ability to filter based on several criteria, and then search as well. Click image for larger version. 
<br /><script async src=
    Name: screenshot_1.jpg  Views: 4  Size: 117.8 KB  ID: 9827" class="thumbnail" style="float:CONFIG" />


    Most of this functionality is already built. Filter by division, filter by subdivision and the search feature work by using nesting queries (built with query builder). First query filters on division, the second query uses the results of the first query to additionally filter on subdivision, then the final query applies the search criteria and filters on DISTINCT values. All that is working great. But I need to build the feature to filter by region. (I'd like to keep as much built with query builder as opposed to VBA).

    Here's the current relationship diagram.Click image for larger version. 

Name:	screenshot_2.jpg 
Views:	3 
Size:	61.4 KB 
ID:	9828

    Each region is just a Yes/No field with a 1-to-1 relationship with the company.ID field. When a new company record is created, the default value of each region value is Yes. Meaning that our business process assumes any new company does business in all regions until we find out otherwise and check No on the appropriate form to indicate.

    One question is. Should I stick with this DB design for tracking work regions? Or go with something like this: A junction table to store the company.ID against the workregion.ID :Click image for larger version. 

Name:	screenshot_3.jpg 
Views:	2 
Size:	61.8 KB 
ID:	9829

    There are about 5500 company records, so this would result in a huge junction table (5500 * 9 regions). This would also require adding code to populate 9 new entries to the junction table with the creation of each company record.

    My main reason for considering the junction table instead of Yes/No fields is to make it simpler to use the nested subqueries I already have built to apply the filter by region. AFAIK, there's no way with query builder to build the WHERE clause with mulitiple "OR" statements based on the value of the 9 checkboxes on the form. I've already written the VBA needed to process the form checkboxes to build the WHERE clause. I just don't know how to use that variable containing the WHERE clause in the pre-built query in Access. Also, since my final nested query involves a DISTINCT value filter, I don't think I can use the results of THAT query to make a new one with.

    Second question is: Is there a way to perform the (filter by region) Query I already have built with VBA on the form and then save the results of THAT query as a query that could be referenced by one of my already-written saved queries in Access???

    thanks in advance for advice.

    baulrich

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Consider this approach to filter form http://datapigtechnologies.com/flash...tomfilter.html

    Note that the example does not show Date or Yes/No fields. They are a bit trickier than text.

    Since text is easy to apply criteria to, consider:

    Create a field that returns a text "T"/"F" if any checkbox is true then apply criteria to that field.

    IIf(check1=-1 Or check2=-1 Or check3=-1 Or check4=-1 Or check5=-1 Or check6=-1 Or check7=-1 Or check8=-1 Or check9=-1,"T","F")
    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.

  3. #3
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    I'm not following..

    Create a field that returns a text "T"/"F"
    You mean a new field in the query, right?

    But if I have just ONE new field, it seems by the logic suggested, I'd get a "T" if ANY of the regions on the form are checked... and get an "F" otherwise.

    I'd then have the option of adding criteria to that new field of the query to filter by T or F. But how does that help?? It still gives me just one field by which to filter.

    Here's the logic that I need to apply... coded in VBA
    Code:
    Dim strWHERE_CLAUSE As String
    Dim strSQL As String
    
    strWHERE_CLAUSE = ""
    
    If Me.chk_KY_Paducah Then
        strWHERE_CLAUSE = "KY_Paducah=true"
    End If
       
    If Me.chk_KY_Hopkinsville Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "KY_Hopkinsville=true"
    End If
    
    If Me.chk_KY_Owensboro Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "KY_Owensboro=true"
    End If
    
    If Me.chk_KY_Bowling_Green Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "KY_Bowling Green=true"
    End If
    
    If Me.chk_KY_Madisonville Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "KY_Madisonville=true"
    End If
    
    If Me.chk_KY_Lexington Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "KY_Lexington=true"
    End If
    
    If Me.chk_TN_Clarksville Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "TN_Clarksville=true"
    End If
    
    If Me.chk_TN_Nashville Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "TN_Nashville=true"
    End If
    
    If Me.chk_IL_All Then
        If Len(strWHERE_CLAUSE) > 0 Then strWHERE_CLAUSE = strWHERE_CLAUSE & " OR "
        strWHERE_CLAUSE = strWHERE_CLAUSE & "IL_All=true"
    End If
    
    
    strSQL = "SELECT [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].* FROM [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist]"
    If Len(strWHERE_CLAUSE) > 0 Then
        strWHERE_CLAUSE = " WHERE " & strWHERE_CLAUSE
        strSQL = strSQL & strWHERE_CLAUSE
    End If
    
    strSQL = strSQL & ";"
    That results in NO WHERE clause if none of the the region checkboxes are selected, but if any are.... it only includes those in the WHERE clause.

    Hopefully it can be done the way you suggest and you can fill me in on what I'm overlooking.

    thanks,

    baulrich

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, I meant create a field in query with expression. I thought you wanted record if ANY of the Yes/No fields is yes. If not, then create a calculated field for each checkbox that returns a text value then apply the wildcard criteria for text value as shown in the tutorial.

    If you want to use VBA to build a string for any combination of criteria, that does get tricky and will be an expansion of the code you show. Review http://www.allenbrowne.com/ser-62.html
    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.

  5. #5
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    I'm still not grasping the logic. Can you be more specific about how to create the calculated field and where to use the wildcard criteria?


    To be clear about what I'm trying to accomplish: Each company record has 9 different Yes/No fields; one for each of the Regions that a company may do business in.

    There are 9 checkboxes on the company-filter form. If only the Region1 checkbox is TRUE, then only the company records with Region1=Yes should be shown. If Region1 and Region4 checkboxes are TRUE, then a company record should be returned IF Region1=Yes OR Region4=Yes.


    I've experimented extensively with the wildcard criteria, but I'm not sure I can make it work when when multiple Yes/No fields need to be evaluated with OR instead of AND. I'm doubtful, but I'm still hopeful someone can explain in detail a way to do it with Query Builder.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Here is what I found out. The query with parameters isn't going to work with the Yes/No fields after all. I think what I was remembering from earlier efforts was that only one check field was allowed to be selected for each record (in which case the poster was advised to change to a single text field).

    Take this example of table with 3 Yes/No fields:
    Type T1 T2 T3
    A No No Yes
    B Yes Yes No
    A Yes No No
    C No Yes No

    Out of the 4 records, only the third returns with the following query:
    SELECT * FROM Table1 WHERE Type="A" And (T1=-1 OR T2=-1);

    To use the DataPigTech tutorial example, the query would be like:
    SELECT *
    FROM Tabl1
    WHERE Type Like [input type] & "*" AND (IIf([T1]=-1,"T","F") Like [1input T or F] & "*" Or IIf([T2]=-1,"T","F") Like [2input T or F] & "*" OR IIf([T3]=-1,"T","F") Like [3input T or F] & "*");

    Response to each prompt: A, T, T, nothing
    Unfortunately that returns first and third records.

    Note that the query parameter method requires that every one of the records have a non-Null value in the fields used for filter. If Null is allowed will have to handle that in the query by calculating a field to use for filter. I use Nz function to return an empty string if Null encountered.

    Your options appear to be:
    1. VBA to build the desired filter string
    2. reconfigure the data to more normalized structure (the child table for associated Locations)
    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.

  7. #7
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    Well, I think I finally found a way to do this with Access query builder. Here's the screenshot:Click image for larger version. 

Name:	screenshot_4.jpg 
Views:	4 
Size:	125.3 KB 
ID:	9843


    And the resulting SQL is:
    Code:
    SELECT [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].ID, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].is_inactive, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].is_subcontractor, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].company_name, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].city, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].state, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].phone_main, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].fax_main, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].email_address, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Paducah, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Hopkinsville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Owensboro, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].[KY_Bowling Green], [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Madisonville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Lexington, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Clarksville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Nashville, [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].IL_All
    FROM [qry_frm_company_filter-companies_filtered_by_div+subdiv+dist]
    WHERE ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Paducah)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Paducah])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Hopkinsville)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Hopkinsville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Owensboro)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Owensboro])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].[KY_Bowling Green])=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Bowling Green])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Madisonville)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Madisonville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].KY_Lexington)=Yes) AND (([Forms]![frm_company_Filter]![chk_KY_Lexington])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Clarksville)=Yes) AND (([Forms]![frm_company_Filter]![chk_TN_Clarksville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].TN_Nashville)=Yes) AND (([Forms]![frm_company_Filter]![chk_TN_Nashville])=Yes)) OR ((([qry_frm_company_filter-companies_filtered_by_div+subdiv+dist].IL_All)=Yes) AND (([Forms]![frm_company_Filter]![chk_IL_All])=Yes));

    I'm sure this is far from efficient, but it does get the job done. For each of the 9 regions, an additional field is added to the query, based on the value of the checkbox for that region on the form. So for each particular region the region field must = Yes AND the checkbox on the form for that region must equal Yes.

    I'm glad to have found a way to do this with builder, but it will be more efficient to use VBA to check the status of each of the 9 checkboxes on the form and use them to construct a WHERE clause with ORs... that only includes the regions the user is interested in. I just discovered QueryDefs and I think these will be key because its important for me to use the nested queries that are already saved in Access and also be able to save the results of this particular query as a QueryDef that can be accessed by other Access objects (without VBA).

    I am interested in hearing what most experts would say is the best way to store 9+ fields such as these. Yes/No fields in the company table (actually mine are in separate table with 1-1 relationship) or in a junction table shared by company table and region table.

    thanks,

    baulrich

  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
    The normalized structure would be a junction table. It is a balancing act between normalization and ease of data entry/output.
    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.

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

Similar Threads

  1. Junction Table
    By snowboarder234 in forum Access
    Replies: 11
    Last Post: 04-18-2012, 09:31 AM
  2. How to use Junction Table to populate DB
    By Sorbz62 in forum Forms
    Replies: 1
    Last Post: 10-02-2011, 05:45 PM
  3. Updating a Junction Table
    By oleBucky in forum Forms
    Replies: 4
    Last Post: 04-05-2011, 04:59 PM
  4. Junction Table
    By mae3n in forum Database Design
    Replies: 2
    Last Post: 01-15-2011, 10:23 PM
  5. Replies: 0
    Last Post: 09-25-2006, 03:42 AM

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