CROSSTAB QUERIES OVER TIME TO TOP TEN

[netrusher]

Is there anyway to turn a crosstab query into a top ten query if the crosstab query is based on a date range.

See code below:

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum([Trend1-3TON-MONTH_1].[Mechanical Totals]) AS [SumOfMechanical Totals]
SELECT [Trend1-3TON-MONTH_1].SystemGroup
FROM [Trends-1-3TON-MONTH] AS [Trend1-3TON-MONTH_1]
GROUP BY [Trend1-3TON-MONTH_1].SystemGroup
PIVOT [Trend1-3TON-MONTH_1].YearMonth;

 

[dhookom]

It isn't clear what you want to apply the top 10 to...

Is there a good reason why you alias the table name with only one copy of the table in the query and the alias isn't much of a shortcut?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

I want to apply the top ten to the time frame that might be selected. If Jan. Feb. is the time frame then I would want the Top Ten to be for that Time Frame. The top ten System Group Faults. I am not sure what you mean by alias the table. This is a crosstab query of an existing query.

 

[lespaul]

The "alias" Duane is referring to:

FROM [Trends-1-3TON-MONTH] AS [Trend1-3TON-MONTH_1]

most of us will do something like:

FROM [Trends-1-3TON-MONTH] AS M

which would then make the rest of the query look like:

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum(M.[Mechanical Totals]) AS [SumOfMechanical Totals]
SELECT M.SystemGroup
FROM [Trends-1-3TON-MONTH] AS M
GROUP BY M.SystemGroup
PIVOT M.YearMonth;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

I don't think you can add a TOP property to a crosstab. I'm not sure what you mean by time frame since I don't see any WHERE clause in your crosstab and there is no column generated as a Row Heading that is a total.

What column from your crosstab do you want to use to determine TOP?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

The time frame is from the first line of code (I have changed my code some from the original post). The time frame is picked up from a form. The results below show three days. Of course there could be many more SystemGroups. What I would like is to create a top ten, or five or three from this crosstab query results by totaling the totals across the cross tab results.

This is how my crosstab appears:

SystemGroup 2006 11 21 2006 11 22 2006 11 23
Air Filter 3 5 7
Back up Alarm 4 8 10
Battery Box 5 3 9
Battery Cable 8 7 5
Brake Cable 10 6 11

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum([Trends-Mechanical-1-3TON-DAY].[Mechanical Totals]) AS [SumOfMechanical Totals]
SELECT [Trends-Mechanical-1-3TON-DAY].SystemGroup
FROM [Trends-Mechanical-1-3TON-DAY]
GROUP BY [Trends-Mechanical-1-3TON-DAY].SystemGroup
PIVOT [Trends-Mechanical-1-3TON-DAY].YearMonthDay;

 

[lespaul]

and how do you WANT it to be? Top ten Dates? Top Ten SystemGroups?

 

[netrusher]

Top Ten System Groups.

 

[netrusher]

Below is the code I have for a crosstab query. This shows me totals for System Groups by Month. The columns are months. This also gives me a grand total accross all of the months. What I would like to do is get a Top Ten based on the GrandTotal. Any suggestions on how to accomplish this? Thanks for all help.

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-MONTH].Totals) AS SumOfTotals
SELECT [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory, Sum([Trends-1-3TON-MONTH].Totals) AS GrandTotal
FROM [Trends-1-3TON-MONTH]
GROUP BY [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory
PIVOT [Trends-1-3TON-MONTH].YearMonth;

 

[dhookom]

Try a query with this sql view:

SELECT TOP 10 *
FROM qxtbYourUnnamedQuery
ORDER BY GrandTotal DESC;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

Forget my above post. I already get the same results from another query.