pulling records from one table based of another table column

[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.

 

[John W. Vinson]

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!

 

[Mary]

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.

 

[John W. Vinson]

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.

 

[Mary]

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.

ID AMT AMTTYPE Transtype
234 5.30 visa credit
234 1.25 TC paytogo

and soon.
Thanks gain

 

[Mary]

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

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.

ID AMT AMTTYPE Transtype
234 5.30 visa credit
234 1.25 TC paytogo

and soon.
Thanks gain

 

[John W. Vinson]

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

I see in another thread that you did get an answer?

 

[Mary]

Yes. It works great on the sample data but it is extremely slow on
production. Table1 usually average 35,000 records and table2 usually averages
537 records. It took over 1 hour to pull the results for these records.
This there a way to improve the timing? As this query result needs to show
up on the form for the end-user to use?

I can take additional help

 

[John W. Vinson]

Yes. It works great on the sample data but it is extremely slow on
production. Table1 usually average 35,000 records and table2 usually averages
537 records. It took over 1 hour to pull the results for these records.
This there a way to improve the timing? As this query result needs to show
up on the form for the end-user to use?

Are there Indexes on the relevant fields? That can make a huge difference.

Please post the SQL view of the query, it should be possible to speed it up.
Those tables are *not* that big.

 

[Mary]

Yes. I did after I have posted this by using access optimization. This
indexes them .

Additionally, the results records are missing records at random. For example
cases where table1.audit is 5 the result may produce 5 records and others may
produce 3 or 4 record. Another example is that cases where table1.audit is 1
some results may correctly pull one records and some will pull no records.
The production data for this example supposed to pull a total of 612 records
but only pulled 503.
This only happens when the records size increases (using the production size
I have mentioned below)

 

[Mary]

Do you think it will improve the result if I create separate queries? If yes,
can you help me rewrite the other one?
Basically, I can LEFT JOIN table1 to table2 get the result (TABLE3) and on
table you write for me the query that will pull the number of records based
of table3.audit. I am guessing that this will not be a subquery as a result
it will improve the results and speed.

This is the join query.
SELECT table1.*, table2.Audit1
FROM table1 LEFT JOIN table2 ON table1.[ID] = table2.[ID];

The query above will add the column audit. I will then call this new query
table3. Now if you can write the query that will pull the result based of the
top Amt by ID and count of result based of table3.audit . For example if ID
234 has 5 records and table3.audit is 3, I want to the result to pull the 3
top records. if ID 225 has10 records and table3.audit is1, I want to the
result to pull the 1 top records.

Thanks a million in advance

 

[Mary]

Do you think it will improve the result if I create separate queries? If yes,
can you help me rewrite the other one?
Basically, I can LEFT JOIN table1 to table2 get the result (TABLE3) and on
table you write for me the query that will pull the number of records based
of table3.audit. I am guessing that this will not be a subquery as a result
it will improve the results and speed.

This is the join query.
SELECT table1.*, table2.Audit
FROM table1 LEFT JOIN table2 ON table1.[ID] = table2.[ID];

The query above will add the column audit. I will then call this new query
table3. Now if you can write the query that will pull the result based of the
top Amt by ID and count of result based of table3.audit . For example if ID
234 has 5 records and table3.audit is 3, I want to the result to pull the 3
top records. if ID 225 has10 records and table3.audit is1, I want to the
result to pull the 1 top records.

Thanks a million in advance

Yes. I did after I have posted this by using access optimization. This
indexes them .

Additionally, the results records are missing records at random. For example
cases where table1.audit is 5 the result may produce 5 records and others may
produce 3 or 4 record. Another example is that cases where table1.audit is 1
some results may correctly pull one records and some will pull no records.
The production data for this example supposed to pull a total of 612 records
but only pulled 503.
This only happens when the records size increases (using the production size
I have mentioned below)