Query from a Query (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

(Edited by Mario on 10-Sep-02 16:27. Here is the example)

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Read through post 146797 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.

Reply | Quote