Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query from a Query (Access 2000)

    I have 2 Tables(An address and transaction table). I would like to run a query on one of the tables selecting all people from a certain town. Then I would like to query the second table with the results from the first table in order to have all transactions for a certain town.

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from a Query (Access 2000)

    just make sure that in the query for the people from the town, you add the linking field that links the two tables. Then build a query on that query and add the tranaction table, and make sure that in the QBE grid the table and first query are linked.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a Query (Access 2000)

    My problem is I don't know how to link the 2 tables since in the one I have the town seperate and in the other it forms part of a discription. Example in the one it is "Tulsa" and in the other one the discription reads Tulsa Garage, Tulsa Mart etc.

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from a Query (Access 2000)

    Unless you want to build a separate query for each town, you can't. Are you specifically trying to match a single town? Don't you already have a link of some sort between the tables? If so, just use that link and filter on the town in the address table because the description will be both unreliable and unweildly to use.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a Query (Access 2000)

    <P ID="edit" class=small>(Edited by Mario on 10-Sep-02 16:27. Here is the example)</P>I've done a similar exercise in excel on a differant dataset. I've autofiltered the first table, copied unique values to another spreadsheet (3rd table( and then autofiltering the second table by each record from the 3rd table and copying it to a new spreadsheet.

    Is it possible to run a query on a table and get 5 records that match criteria and then querying another table with these 5 records.

    Refer to example: I'm looking for all person age 30 and then from the second table the transactions for all the persons from the 1st query



    Thanks
    Attached Files Attached Files

  6. #6
    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 from a Query (Access 2000)

    As stated by others, you really need to set up your tables and then link them. I have attached a sample of your database with linked tables. The tables were modified slightly as well as your query. One design flaw in your setup is that the way you ran your query, you got all people named John at age 30 but missed Peter. This design flaw still exists in the attached db and should be modified.

    Good Luck
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  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 from a Query (Access 2000)

    One last note, you said you did a similar exercise in Excel. Excel is not a relational database as Access is. By setting up the design properly and linking tables, you will find it much easier to run queries against your dataset to get what you need in Access. (Much less manual effort)

    If you can't link the tables, then you could create a query to get the first set of results and then run a second query with a subquery using an IN statement linked to the first query to do what you are asking for. (That's easy for me to say....)

    It would be better to just learn how to use access as a relational database.

    HTH
    Regards,

    Gary
    (It's been a while!)

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a Query (Access 2000)

    Thanks for all the help. Another question. If I have the original text file's transaction field as Mike Johnson and Peter Johnson. How do I write a query to create a new record to show in the field name being Mike and Peter etc.(It should split the name on the space between the name and last name. I hope I'm clear.

    Thanks

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query from a Query (Access 2000)

    I see Charlotte beat me to the answer to this issue. About splitting the name, you can use the Mid function to do that. Look in the Help how to do it.

  10. #10
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query from a Query (Access 2000)

    I don't know how to use the mid function since the name could be of variable length. i.e. Mario, Zave etc. In Excel I would have used Data Text to Columns and delimited on space. Please help

  11. #11
    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 from a Query (Access 2000)

    Read through <post#=146797>post 146797</post#> for an example of a function that does something similar to what you need. You could then use the function in a query to get just the first name by searching for the space between the first and last name.
    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
  •