Help will pulling records based of another table Mary 12/24/2008 8:09:01 PM 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.

Re: Help will pulling records based of another table "Jeff Boyce" <JeffBoyce_IF[ at ]msn.com-DISCARD_HYPHEN_TO_END> 12/25/2008 2:26:18 PM 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Mary" <Mary[ at ]discussions.microsoft.com> wrote in message news:64DD5815-AB7E-44E1-8394-4FBBA700A8FE[ at ]microsoft.com... [Quoted Text] > 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. >