Re: query to pull records based of subquery column
- From: Mary <Mary@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 1 Jan 2009 09:54:07 -0800
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" wrote:
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.
"Ken Sheridan" wrote:
On Dec 26, 2:09 pm, Mary <M...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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
- Prev by Date: Re: pull records based of another table
- Next by Date: Re: Union Query with a Crosstab Query
- Previous by thread: Query not bringing up all records
- Next by thread: Re: query to pull records based of subquery column
- Index(es):
Relevant Pages
|
