Help will pulling records based of another table

[Mary]

Hello all..
I am really really in need of a help. This query of my project has hold me
back now for months. The project is to develop a database.

I have two tables, table1 and table2. Table 1 has the following columns and
rows:
ID Audit
223 2
234 3
225 1
226 2
The column ID is unique, have one record per row and Audit is not unique.

Here are the columns and rows for table 2. Records in column ID are unique
but can have more than one record per rows. Amt Type is grouped by Trans
type. Example, cash and visa are credit Trans type; where as, all MC and TC
are paytogo.

ID Amt Amt type Trans Type
223 1.50 cash credit
223 2.03 visa credit
223 2.30 MC paytogo
234 .50 TC paytogo
234 1.75 cash credit
234 .90 visa credit
234 5.30 visa credit
234 1.25 TC paytogo
225 1.35 MC paytogo
225 2.24 visa credit
225 3.35 MC paytogo
226 2.17 visa credit
226 1.15 TC paytogo
226 1.50 TC paytogo
226 1.60 visa credit

I would like a sql statement or know how to create a query that will look at
column Audit of table1. For every Audit in table1 pull the corresponding
records in table 2 and sort in order by Amt.Table2. I really do not care
about the order. Just would like for it to pull the records from table2 based
of Audit.table1.


The result for this query should be. From the result below. There are 3
records for ID 234, 1 record for ID 225, 2 records for ID 223 and 2 records
for ID 226
ID Amt Amt type Trans Type
234 5.30 visa credit
234 1.25 TC paytogo
234 1.75 cash credit
225 3.35 MC paytogo
223 2.30 MC paytogo
223 2.03 visa credit
226 2.17 visa credit
226 1.60 visa credit

Any help will be greatly appreciated as this has been holding be back from
completing this project. I am opened to any direction.
Thanks a million in advance.

 

[Jeff Boyce]

Create a new query in design view.

Add the two tables. Join them on the shared field(s).

Select the fields you wish to see in the output.

Run the query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/