|
|
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.
|
|
On Fri, 26 Dec 2008 06:09:00 -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 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?
--
John W. Vinson [MVP]
|
|
On Dec 26, 2:09 pm, Mary <M...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > 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
|
|
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:
[Quoted Text] > On Dec 26, 2:09 pm, Mary <M...[ at ]discussions.microsoft.com> 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
>
|
|
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" wrote:
[Quoted Text] >
> 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...[ at ]discussions.microsoft.com> 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
> >
|
|
|