pulling records from one table based of another table column Mary 12/24/2008 8:16:02 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: pulling records from one table based of another table column John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 12/24/2008 8:48:24 PM On Wed, 24 Dec 2008 12:16:02 -0800, Mary <Mary[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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. That should be very simple. Create a new Query. Add Table1 and Table2; if Access doesn't do so for you automatically, join the ID fields by dragging ID from Table1 to ID in Table2. Put a criterion on Audit of [Enter Audit number:] Select Ascending under the Amount field. Include whatever fields you want to see in the grid. Open the query by clicking the datasheet icon in the left end of the toolbar. What specific problems have you been having "for months" with this query? I wonder if there's some other problem that isn't obvious from your post because this would seem to be a very basic query! -- John W. Vinson [MVP]

pull records based of another table Mary 12/25/2008 8:22:01 AM Sorry for the double posting and thanks for our help. What do you mean by Put a criterion on Audit of [Enter Audit number:] ? if i enter the number on audit criterion it will only pull one result that is based on that number. I think you missed my questions. I want it to pull all of the records based on the number from table1, column Aduit. Your anser will only pull all the Audit will 4 if I enter that on the critierion field.

Re: pull records based of another table John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 12/25/2008 7:43:02 PM On Thu, 25 Dec 2008 00:22:01 -0800, Mary <Mary[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Sorry for the double posting and thanks for our help. What do you mean by >Put a criterion on Audit of [Enter Audit number:] ? > > if i enter the number on audit criterion it will only pull one result that >is based on that number. I think you missed my questions. I want it to pull >all of the records based on the number from table1, column Aduit. Your anser >will only pull all the Audit will 4 if I enter that on the critierion field. I guess I didn't understand the question then. If you don't want to put a criterion on Audit, simply leave off the criterion. If you Join the two tables, you will be able to see all of the records for all audit values. What *SPECIFIC* problem are you having? If you're not getting the results you want, please open your Query in SQL view and post the SQL text here; also post a few representative records of the results you're getting (based on the sample data you posted say) and indicate what it is that you're seeing that you don't want, or aren't seeing that you do want. -- John W. Vinson [MVP]

Re: pull records based of another table Mary 12/30/2008 1:28:02 AM "John W. Vinson" wrote: [Quoted Text] > On Thu, 25 Dec 2008 00:22:01 -0800, Mary <Mary[ at ]discussions.microsoft.com> > wrote: > > >Sorry for the double posting and thanks for our help. What do you mean by > >Put a criterion on Audit of [Enter Audit number:] ? > > > > if i enter the number on audit criterion it will only pull one result that > >is based on that number. I think you missed my questions. I want it to pull > >all of the records based on the number from table1, column Aduit. Your anser > >will only pull all the Audit will 4 if I enter that on the critierion field. > > I guess I didn't understand the question then. If you don't want to put a > criterion on Audit, simply leave off the criterion. If you Join the two > tables, you will be able to see all of the records for all audit values. > > What *SPECIFIC* problem are you having? If you're not getting the results you > want, please open your Query in SQL view and post the SQL text here; also post > a few representative records of the results you're getting (based on the > sample data you posted say) and indicate what it is that you're seeing that > you don't want, or aren't seeing that you do want. > -- > > John W. Vinson [MVP] > Thanks for your reply. I do not want to pull all result that is very easy to do. I want to pull the records based of table.audit. So if employee ID 234 has an audit of 2. I want to see 2 record from table2 . This records should be based of the top 2 amt. The result for this from the 2 tables above should ONLY be: ID AMT AMTTYPE Transtype 234 5.30 visa credit 234 1.25 TC paytogo and soon. Thanks gain

Re: pull records based of another table Mary 12/30/2008 10:07:10 PM Thanks for your reply. Yes, I will be running the query from a form. I am opened to any methods. I want to pull the records based of table.audit. So if employee ID 234 has an audit of 2. I want to see 2 record from table2 . This records should be based of the top 2 amt. The result for this from the 2 tables above should ONLY be: ID AMT AMTTYPE Transtype 234 5.30 visa credit 234 1.25 TC paytogo and soon. Thanks gain "Mary" wrote: [Quoted Text] > > > "John W. Vinson" wrote: > > > On Thu, 25 Dec 2008 00:22:01 -0800, Mary <Mary[ at ]discussions.microsoft.com> > > wrote: > > > > >Sorry for the double posting and thanks for our help. What do you mean by > > >Put a criterion on Audit of [Enter Audit number:] ? > > > > > > if i enter the number on audit criterion it will only pull one result that > > >is based on that number. I think you missed my questions. I want it to pull > > >all of the records based on the number from table1, column Aduit. Your anser > > >will only pull all the Audit will 4 if I enter that on the critierion field. > > > > I guess I didn't understand the question then. If you don't want to put a > > criterion on Audit, simply leave off the criterion. If you Join the two > > tables, you will be able to see all of the records for all audit values. > > > > What *SPECIFIC* problem are you having? If you're not getting the results you > > want, please open your Query in SQL view and post the SQL text here; also post > > a few representative records of the results you're getting (based on the > > sample data you posted say) and indicate what it is that you're seeing that > > you don't want, or aren't seeing that you do want. > > -- > > > > John W. Vinson [MVP] > > Thanks for your reply. I do not want to pull all result that is very easy to do. I want to pull the records based of table.audit. So if employee ID 234 has an audit of 2. I want to see 2 record from table2 . This records should be based of the top 2 amt. The result for this from the 2 tables above should ONLY be: > ID AMT AMTTYPE Transtype > 234 5.30 visa credit > 234 1.25 TC paytogo > > and soon. > Thanks gain