top values

[Guest]

I was introduced to top values last week and am not trying to use it. I am
not sure if this is the best way to go. In my query is there a way to first
filter my selection and then do the top values?

 

[Van T. Dinh]

I am not sure I entirely understand your question but you certainly can use
the WHERE clause to filter records in a Query / SQL that uses the TOP
predicate. In general, the JET database engine will perform the filtering
first so, I guess, it will do what you asked.

 

[Guest]

So what I have going on is on a form I have a command button to open a
report. I do have a where clause. I then want the top 13 values. Somewhere
I am going wrong. In my query I have it sorted by what we call TA Number.
This is a main number this is what my where clause is based on. When I then
veiw my report I only want the Top 13 of that TA Number to show. Right now
it show the actual top 13. Here is my sql

SELECT TOP 13 Amount_Vendor_Date.Per_Diem_Amount, Amount_Vendor_Date.[Charge
ID], Amount_Vendor_Date.Service_Date, Amount_Vendor_Date.Vendor,
Amount_Vendor_Date.Cash_Amount, Amount_Vendor_Date.TA_Number,
Amount_Vendor_Date.Credit_Card_Amount, Amount_Vendor_Date.Hotel_Amount,
Amount_Vendor_Date.Notes, Amount_Vendor_Date.Departure,
Amount_Vendor_Date.Arrival, Amount_Vendor_Date.[Time Leave],
Amount_Vendor_Date.[Time Arrive]
FROM Amount_Vendor_Date
GROUP BY Amount_Vendor_Date.Per_Diem_Amount, Amount_Vendor_Date.[Charge ID],
Amount_Vendor_Date.Service_Date, Amount_Vendor_Date.Vendor,
Amount_Vendor_Date.Cash_Amount, Amount_Vendor_Date.TA_Number,
Amount_Vendor_Date.Credit_Card_Amount, Amount_Vendor_Date.Hotel_Amount,
Amount_Vendor_Date.Notes, Amount_Vendor_Date.Departure,
Amount_Vendor_Date.Arrival, Amount_Vendor_Date.[Time Leave],
Amount_Vendor_Date.[Time Arrive]
ORDER BY Amount_Vendor_Date.TA_Number;

Thanks
chey

 

[Van T. Dinh]

You have to include the WHERE clause in the SQL, not as the WhereCondition
in the OpenReport method. The WhereCondition filters *after* the Query,
AFAIK.

 

[Guest]

Oh that make sense. So where do I put the where clause in my sql? Are you
able to help me with that?
Chey

You have to include the WHERE clause in the SQL, not as the WhereCondition
in the OpenReport method. The WhereCondition filters *after* the Query,
AFAIK.

--
HTH
Van T. Dinh
MVP (Access)

So what I have going on is on a form I have a command button to open a
report. I do have a where clause. I then want the top 13 values.
Somewhere
I am going wrong. In my query I have it sorted by what we call TA Number.
This is a main number this is what my where clause is based on. When I
then
veiw my report I only want the Top 13 of that TA Number to show. Right
now
it show the actual top 13. Here is my sql

SELECT TOP 13 Amount_Vendor_Date.Per_Diem_Amount,
Amount_Vendor_Date.[Charge
ID], Amount_Vendor_Date.Service_Date, Amount_Vendor_Date.Vendor,
Amount_Vendor_Date.Cash_Amount, Amount_Vendor_Date.TA_Number,
Amount_Vendor_Date.Credit_Card_Amount, Amount_Vendor_Date.Hotel_Amount,
Amount_Vendor_Date.Notes, Amount_Vendor_Date.Departure,
Amount_Vendor_Date.Arrival, Amount_Vendor_Date.[Time Leave],
Amount_Vendor_Date.[Time Arrive]
FROM Amount_Vendor_Date
GROUP BY Amount_Vendor_Date.Per_Diem_Amount, Amount_Vendor_Date.[Charge
ID],
Amount_Vendor_Date.Service_Date, Amount_Vendor_Date.Vendor,
Amount_Vendor_Date.Cash_Amount, Amount_Vendor_Date.TA_Number,
Amount_Vendor_Date.Credit_Card_Amount, Amount_Vendor_Date.Hotel_Amount,
Amount_Vendor_Date.Notes, Amount_Vendor_Date.Departure,
Amount_Vendor_Date.Arrival, Amount_Vendor_Date.[Time Leave],
Amount_Vendor_Date.[Time Arrive]
ORDER BY Amount_Vendor_Date.TA_Number;

Thanks
chey

 

[Van T. Dinh]

Check Access Help on the "WHERE Clause" ...