Hi Karl!
One problem has arisen that I did not anticipate. When the queries run and
a tie occurs, it does not return anything of the tie. Example:
Mbrs Resort # of Reservations
Gold Spa1 345
Gold Spa3 258
Gold Spa89 125
Gold Spa23 89
Gold Spa9 65
Gold Spa87 65
If this situation it drops the last 2 - is there a way to show the tie
regardless of the ranking?
Here is the code.
1st query
SELECT [Top5-rpt tbl].Expr1 AS Gold, Sum([Top5-rpt tbl].Expr3) AS QTY,
[Top5-rpt tbl].Expr2 AS Proj, [Top5-rpt tbl].Resort
FROM [Top5-rpt tbl]
WHERE ((([Top5-rpt tbl].StartDate) Between [Forms]![PremierDriver]![Text7]
And [Forms]![PremierDriver]![Text10]))
GROUP BY [Top5-rpt tbl].Expr1, [Top5-rpt tbl].Expr2, [Top5-rpt tbl].Resort
ORDER BY [Top5-rpt tbl].Expr1, Sum([Top5-rpt tbl].Expr3) DESC;
2nd Query
SELECT Q.Gold AS Expr1, Q.Proj AS Expr2, Q.QTY AS Expr3, Resorts.Resort,
(SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank, [Ownership Table].[Alpha Ownership],
[Ownership Table].SortKey
FROM (PremierTDCountqry AS Q INNER JOIN [Ownership Table] ON Q.Gold =
[Ownership Table].[Ownership Letter]) INNER JOIN Resorts ON Q.Proj =
Resorts.Proj
WHERE ((((SELECT COUNT(*) FROM PremierTDCountqry Q1 WHERE Q1.[Gold] =
Q.[Gold] AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;
Bunky said:
Karl,
Thank you for all your assistance; it works fine now. I'm sorry I did not
notice your wording. Sleep deprived taking care of wife will do it everytime.
Thanks again.
KARL DEWEY said:
I'm sorry, did I not explain it well enough when I said "Try these two
queries"?
--
KARL DEWEY
Build a little - Test a little
:
When I try to execute I get a msg that states
"Characters after end of SQL statement."
Should I break these apart into 2 separate queries or are they to run
together?
:
Try these two queries -
tblReswithGold_Count ---
SELECT tblReswithGold.Gold, Count(tblReswithGold.[Res number]) AS QTY,
tblReswithGold.Proj
FROM tblReswithGold
GROUP BY tblReswithGold.Gold, tblReswithGold.Proj
ORDER BY tblReswithGold.Gold, Count(tblReswithGold.[Res number]) DESC;
SELECT Q.Gold, Q.Proj, Q.QTY, (SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]) AS Rank
FROM tblReswithGold_Count AS Q
WHERE ((((SELECT COUNT(*) FROM tblReswithGold_Count Q1
WHERE Q1.[Gold] = Q.[Gold]
AND Q1.[QTY] >= Q.[QTY]))<=5))
ORDER BY Q.Gold, Q.QTY DESC;
--
KARL DEWEY
Build a little - Test a little
:
Gold is the field name for Premier type
Proj is the field name for Resort
Res number is the field name for reservations but this is what I am counting.
Table name is tblReswithGold
I wish to see the top 5 Proj's that Gold are staying at broken down by the
different Gold levels. ie-L for Platinum level, Z for Bronze level, S for
Silver level and G for Gold level. This will be determined by counting the
res numbers for each proj.
OK?
:
Your orignal post read "Now I wish to see only the top 5 resorts these
premier owners visited grouped by the premier type."
I assume that Gold is the field for 'premier type', but what field is the
Resort? Is 'Proj' your 'premier owners'?
Re-state your requirement in terms of the fields names. What is the table
name.
--
KARL DEWEY
Build a little - Test a little
:
Table Structure
Res-Date Date/Time
Proj Number
Res Number Text
Gold Text
Sample Date
Res-Date Proj Res Number Gold
9/1/2008 63 QNP3W S
9/1/2008 22 QNP3Z S
9/3/2008 33 ZXZXZ S
9/3/2008 63 XZXZX S
9/5/2008 63 YTYTY S
9/5/2008 22 UYUYU S
So, I want to see that proj 63 is the top with 3 reservations for type S
followed by proj 22 with 2 reservations and then proj 33 with 1 reservation.
Then find the top 5 for the other Gold types as well.
Does this help?
:
UNTESTED - Try this --
SELECT Q.Type, Q.Resort, Count(Q.Resort) AS [Count of reservations], (SELECT
COUNT(*) FROM YourTable Q1
WHERE Q1.[Type] = Q.[Type]
AND Count(Q1.Resort) < Count(Q.Resort))+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.Type, Count(Q.Resort) DESC;
If this does not work then I need you to post your table structure showing
field names and datatype. Also post sample data.
--
KARL DEWEY
Build a little - Test a little
:
The Type refers to ownership type. We have Bronze, Silver, Gold, and
Platinum. Mgmt wants to know the top 5 rsts each group goes to. So the Gold
may go to Spa1, Spa3, Spa74, Spa89 and Spa70 while the Silver owners go to
Spa74, Spa66, Spa1, Spa 70, and Spa05. All of this is determined by the
number of reservations booked.
:
You have Spa15 as both type A and B. Business Spa15 could be a Casino and
Spa - is this how you intend it?
--
KARL DEWEY
Build a little - Test a little
:
You are close. But this is what I need
Type Resort Count of reservations
A Spa1 98
A Spa21 87
A Spa13 45
A Spa74 24
A Spa15 15
B Spa74 105
B Spa66 56
B Spa89 32
B Spa21 16
B Spa15 11
Thanks for your assistance.
:
Forget my earlier post.
the top 5 resorts these premier owners visited grouped by the premier type.
Is this an example of results you want --
Type Resort
A Spa1 15
A Spa2 9
B Ski1 6
C Casino1 5
B Ski2 3
Try this ---
SELECT TOP 5 TblBunky.type, TblBunky.resort, Count(TblBunky.resort) AS
CountOfresort
FROM TblBunky
GROUP BY TblBunky.type, TblBunky.resort
ORDER BY Count(TblBunky.resort) DESC;
--
KARL DEWEY
Build a little - Test a little
:
Karl,
I am not following the SQL code. Is Q.Group = Premier type? What is
Q.Points? Product Q1 I think is the table name but what is Q1.[Group]= to.
I was trying to put names to your example and failed miserably. My bad- too
many years since I really coded SQL!
:
Use a ranking query with rank set to 5 maximum --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1)<=5))
ORDER BY Q.Group, Q.Points;
--
KARL DEWEY
Build a little - Test a little
:
I have a query that pulls a total of reservations booked by premier owners
and I have then sorted by premier type and resort visited. Now I wish to see
only the top 5 resorts these premier owners visited grouped by the premier
type. I'm sure SQL can do it but just putting 5 in at the top of the query
will not get my results. Ideas??
Thank you for your assistance.
