query to pull records based of subquery column

[Mary]

Hello all..
I am really really in need of a help.

I have a table and a query, table1 and query1. Query1 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 data in column Audit
are not unique.

Here are the columns and rows for table 1. 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 query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.


The result for this query should be as shown 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. I think this is should
query1 should be joined as a subquery of table1.

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 query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.

AHA! Apologies for the prior responses, Mary, I'd missed that you want to
include the Audit value as the Top Values operand in the query.

Unfortunatly I don't think there's any way do do that without using VBA code
to construct the SQL string. What's the context? Are you running this query
from a form, or from the query window directly, or as the recordsource for a
Report?

 

[Ken Sheridan]

Hello all..
I am really really in need of a help.

I have a table and a query, table1 and query1. Query1 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 data in column Audit
are not unique.

Here are the columns and rows for table 1. 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 lookat
column Audit of query1 and pull that amount of records of table1.  The result
should be listed by the top Amt for the records based on query1.Audit.

The result for this query should be as shown 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. I think this is should
query1 should be joined as a subquery of table1.

Thanks a million in advance.

Try this:

SELECT T1.*
FROM Table1 As T1
INNER JOIN Query1
ON T1.ID = Query1.ID
WHERE T1.Amt >=
(SELECT MIN(Amt)
FROM Table1 As T2
WHERE T2.ID = T1.ID
AND
(SELECT COUNT(*)
FROM Table1 AS T3
WHERE T3.ID = T2.ID
AND T3.Amt >= T2.Amt) <= Query1.Audit);

Ken Sheridan
Stafford, England

 

[Mary]

KEN!!!!!!!!!!!!!!!! YOU ARE THE BEST. It works on the sample tomorrow I will
apply it to the real tables. WWWWWWWWWWWWWOOOOOOOOOWWWWWWW.


And the query is fast in doing the calculations.

 

[Mary]

KEN.

Update. This works great. But one strange thing is that I was testing the
same sample data. When I copy and paste new records into table2 and run the
query it does not reflect the total count in query1.audit. One record is
always missing. Now if I key a new record it correctly reflect the result
based of query1.audit.
I think this may have to do with access but I am not sure.

For example. Open table1 and copy and paste all 5 records for ID 234. This
should increase your total records on table1 for ID 234 to 10. Now go to
query1.audit and increase the audit for ID 234 to 4. Run the query. You will
see that the result change to from 3 to 2 instead of 4. But if you manual
increase by keying additional 5 records for 234. Then increase query1.audit
to 4. You will pull the correct amount of records (4 records).
This is not a major issue just want to know if this is a copy and paste
limitation with access.

Thanks a million for all of your help

 

[Mary]

UPDATE
This is what I am looking for 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?


Any additional help will be great

 

[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

UPDATE
This is what I am looking for 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?


Any additional help will be great

 

[Mary]

KEN SHERIDAN it appears that you are the only one that has a better
understanding of helping me resolve this problem. I have indexed most of the
tables; this improved the execution time of the query. Additionally, I have
modified the > to < and the MIN to MAX this gives me the top AMT.
I REALLY REALLY NEED YOU HELP. On production the result is able to correctly
pull about 85% of the result. For some reasons, some of the records are
missing. I AM WILLING TO PAY A REASONABLE FEE. The sample works great but
here is what is happening with the production database.
This is what I am looking for 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 think it has to do with the part of the Amt section of the query you gave
me.

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

UPDATE
This is what I am looking for 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?


Any additional help will be great

KEN!!!!!!!!!!!!!!!! YOU ARE THE BEST. It works on the sample tomorrow I will
apply it to the real tables. WWWWWWWWWWWWWOOOOOOOOOWWWWWWW.


And the query is fast in doing the calculations.


:

Hello all..
I am really really in need of a help.

I have a table and a query, table1 and query1. Query1 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 data in column Audit
are not unique.

Here are the columns and rows for table 1. 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 query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.

The result for this query should be as shown 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. I think this is should
query1 should be joined as a subquery of table1.

Thanks a million in advance.

Try this:

SELECT T1.*
FROM Table1 As T1
INNER JOIN Query1
ON T1.ID = Query1.ID
WHERE T1.Amt >=
(SELECT MIN(Amt)
FROM Table1 As T2
WHERE T2.ID = T1.ID
AND
(SELECT COUNT(*)
FROM Table1 AS T3
WHERE T3.ID = T2.ID
AND T3.Amt >= T2.Amt) <= Query1.Audit);

Ken Sheridan
Stafford, England

 

[Mary]

OK. I guess no one can help?

KEN SHERIDAN it appears that you are the only one that has a better
understanding of helping me resolve this problem. I have indexed most of the
tables; this improved the execution time of the query. Additionally, I have
modified the > to < and the MIN to MAX this gives me the top AMT.
I REALLY REALLY NEED YOU HELP. On production the result is able to correctly
pull about 85% of the result. For some reasons, some of the records are
missing. I AM WILLING TO PAY A REASONABLE FEE. The sample works great but
here is what is happening with the production database.
This is what I am looking for 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 think it has to do with the part of the Amt section of the query you gave
me.

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


:

UPDATE
This is what I am looking for 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?


Any additional help will be great

KEN!!!!!!!!!!!!!!!! YOU ARE THE BEST. It works on the sample tomorrow I will
apply it to the real tables. WWWWWWWWWWWWWOOOOOOOOOWWWWWWW.


And the query is fast in doing the calculations.


:

Hello all..
I am really really in need of a help.

I have a table and a query, table1 and query1. Query1 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 data in column Audit
are not unique.

Here are the columns and rows for table 1. 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 query1 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.

The result for this query should be as shown 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. I think this is should
query1 should be joined as a subquery of table1.

Thanks a million in advance.

Try this:

SELECT T1.*
FROM Table1 As T1
INNER JOIN Query1
ON T1.ID = Query1.ID
WHERE T1.Amt >=
(SELECT MIN(Amt)
FROM Table1 As T2
WHERE T2.ID = T1.ID
AND
(SELECT COUNT(*)
FROM Table1 AS T3
WHERE T3.ID = T2.ID
AND T3.Amt >= T2.Amt) <= Query1.Audit);

Ken Sheridan
Stafford, England