Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query question (XP, 2000)

    I am having a problem doing a query. Below is a very simplistic representation of my table.
    <table border=1><td> ID </td><td> Region</td><td> State1 </td><td> County1a </td><td> County1b </td><td> State2 </td><td> County2a </td><td> County2b </td><td>1</td><td>West</td><td>CA</td><td>Kern</td><td>Lake</td><td>TX</td><td>King</td><td>Lee</td> <td>2</td><td>Mid</td><td>OK</td><td>Ellis</td><td>Kay</td><td>TX</td><td>MIlls</td><td>Rusk</td>
    <td>3</td><td>South</td><td>TX</td><td>Hill</td><td>Jack</td><td>LA</td><td>Iberia</td><td>Sabine</td>
    </table>
    Although my example shows records with fields for two states and two counties for each state, there are actually five states with each state having as many as eight counties. I need to be able to search this table based on a search form where the user selects states and counties from combo boxes containing all states and all counties for the state selected.

    In other words, I need to search the table for the existence of a region name OR a state OR a state & county OR a region name & state OR a region name & state & county.

    This is overwhelming for me and I am thinking that if I do a make table query and concatenate the county fields it would be faster and easier. I am considering this because the query design mode doesn

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query question (XP, 2000)

    How can TX be in regions West, Mid and South?

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query question (XP, 2000)

    These were just placed in there arbitrarily however they are intended to show that there can be duplicate states - even duplicate state/county combinations.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query question (XP, 2000)

    Your table structure is not handy for what you want to do. You should restructure it like this (using the same data you posted):

    <table border=1><td>ID</td><td>Region</td><td>State</td><td>County</td><td align=right>1</td><td>West</td><td>CA</td><td>Kern</td><td align=right>1</td><td>West</td><td>CA</td><td>Lake</td><td align=right>1</td><td>West</td><td>TX</td><td>King</td><td align=right>1</td><td>West</td><td>TX</td><td>Lee</td><td align=right>2</td><td>Mid</td><td>OK</td><td>Ellis</td><td align=right>2</td><td>Mid</td><td>OK</td><td>Kay</td><td align=right>2</td><td>Mid</td><td>TX</td><td>Mills</td><td align=right>2</td><td>Mid</td><td>TX</td><td>Rusk</td><td align=right>3</td><td>South</td><td>TX</td><td>Hill</td><td align=right>3</td><td>South</td><td>TX</td><td>Jack</td><td align=right>3</td><td>South</td><td>LA</td><td>Iberia</td><td align=right>3</td><td>South</td><td>LA</td><td>Sabine</td></table>
    This will make querying much easier. It could be normalized further, but this should do.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query question (XP, 2000)

    I understand that however there are other constraints that prevent modifying the original table. Given the original structure, how could I do a maketable query that would create the table you suggest.?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query question (XP, 2000)

    I strongly suggest that you reconsider your design. Doing what you want with the current design is a nightmare.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query question (XP, 2000)

    Yes, it is a nightmare. The constraint to which I alluded is the form design. As you can see in the attached screen shots, the user wants to be able to toggle the additional states & counties on & off with the checkbox. I agree that it would be far better to have the locations in a separate table but I was not able to figure out how to have the same toggle effect using a subform.

    Any ideas?
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: query question (XP, 2000)

    What the user wants does not always make sense. In a subform you can add as many or as few records as you like, so it is much more flexible than using a fixed number of fields.

    If you wanted to simulate the effect of your check box, you could change the height of the subform control to display only one record if the check box is clear, and to display a number of records (4 in the screenshot) if the check box is ticked.
    Attached Images Attached Images
    • File Type: png x.PNG (9.6 KB, 0 views)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •