Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Iterations (2k)

    Hi, I'm trying to automate another chart making process I have to do every week. This chart in particular requires that I do 3 VLOOKUPs on the same column in excel, but all of the information is contained in my DB already (which is exported to excel). What the column does right now is a lookup to one list, then it is filtered on #N/A's (which would be when no data was available), and then we try again from a different list to fill in more of those #N/A's, and this is done a 3rd time with a 3rd list. These 3 lists that we are getting data from can't be combined because the basic layouts of the tables are different, so I'm stuck with keeping them seperate. So, I'm wondering if I can do iterations within a query, or would I need 3 seperate queries to accomplish this?

    Also: The problem isnt searching through the #N/A's, because the columns that I am searching through are supposed to contain a specific string anyway, so I can just search for those that don't contain that string in them.

    If I haven't explained what I'm trying to do well enough, please let me know and I'll try to better explain the situation, thanks!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Iterations (2k)

    Essentially, a Vlookup is similar to joining tables in access. That is, you are returning a value in a named ranged by linking another value in that range to the same value in another range or in access, return the results from one table where there is a match in another table based on a common element. (I hope that was not too confusing.)

    If you have the data in access in different tables, have you thought about trying to join the data to do what you want? As long as there is a common field to join on, then the layout of the table may not matter.

    If you can't join the tables in access then you could try using queries to do what you want.

    Any chance you can provide more information on the tables, data, and what you are trying to accomplish.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Query Iterations (2k)

    In Access, you can base one query on another query (or on a combination of tables and/or queries). You could probably use the Find Unmatched Query Wizard to find missing values, but without knowing details, it's impossible to give exact advice.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Iterations (2k)

    aha! thats a good solution, I didn't think of that. Now my only question with that is does it have to be a table? or can i use a query to join these tables (into a query instead of another table)? It actually sounds familiar to an update query, but update based on certain criteria.

    well here is maybe a better example of what I'm trying to accomplish:

    #'s that need the information from these three tables (not autonumber, pretend they are unique numbers):
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    First table with data:

    1 | BTD
    3 | NC
    5 | BTD
    6 | BTD
    7 | NC

    2nd Table with data:

    2 | NC
    3 | BTD
    4 | NC
    8 | NC

    Third Table:
    7 | BTD
    9 | NC

    Resulting Table or query should be:
    1 | BTD
    2 | NC
    3 | BTD
    4 | NC
    5 | BTD
    6 | BTD
    7 | BTD
    8 | NC
    9 | NC
    10 |

    The resulting table will have all of the numbers from the first table, just not all will have information. And from there, the BTD info will be counted, it doesn't even matter that NC is there, they could also be blank, NC just means that theres another status, just not the one we want. I hope that explains a little bit better.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Iterations (2k)

    Hans, the unmatched query wizard, is that like an update query?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Query Iterations (2k)

    No, it is a method to help you create a special type of select query - one that selects records in one table or query that have no match in another table or query on a specified field. You start the wizard by clicking New in the Queries section of the database window, and selecting Find Unmatched Query Wizard. The wizard will ask a series of questions, and create a query based on your replies.

    You can use the resulting query as the basis for other queries.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Iterations (2k)

    Yes, you could use multiple queries to do what you want. However, you said

    The resulting table will have all of the numbers from the first table, just not all will have information.

    So the resultant data set is based on the first 10 unique numbers as needing results.
    - Where do you store these number so you know what results to get?

    All of the numbers from the first table are 1,3,5,6,7. So you would get results for these.

    You lost me with what you are trying to do with the second and third table. Are you trying to do the following.

    Based on a set of id's (your 1 - 10), update with all of the data from the first table.
    Then update this data by checking against the second table. If either result is BTD, update the data to BTD else leave as NC.
    Then update this data by checking against the next table. If either result is BTD, update the data to BTD else leave as NC.
    ... and so on for each table to be checked.

    More simply put. You could run multiple update queries to update the data associated with 1 - 10 to BTD where values = BTD. If the value is not BTD then leave as is or set to NC.

    Am I on the right track?
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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