Query help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK here is the problem. I need to create a query that will return market
shares. A chart will be based off of this query. The query works perfectly
and the graph works. However, there are too many companies that appear on
the graph. So i would like to modify the query so that only companies that
produce over a certain share to be shown, and all companies beneath that
share to be grouped together and called other (all shares will be summed
together). Is this possible? I have included my query for it.

SELECT qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier, qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;
 
Hi JK,

The quick and easy way to do it is create two graphs, one of them is
powered by the SQL:

SELECT TOP 10 PERCENT qryFSModels.Region,
qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON
qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;

Sorry I can't find a way to display the lower 90%.

Hope this helps.

Nick
 
Thank you for the help. I should have clarified that I only want one graph.
Is it possible to create a query that will pull out the lowest few entries
and group them together and then combine the two new queries together and
then try to make the graph?

Nick 'The database Guy' said:
Hi JK,

The quick and easy way to do it is create two graphs, one of them is
powered by the SQL:

SELECT TOP 10 PERCENT qryFSModels.Region,
qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON
qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;

Sorry I can't find a way to display the lower 90%.

Hope this helps.

Nick
OK here is the problem. I need to create a query that will return market
shares. A chart will be based off of this query. The query works perfectly
and the graph works. However, there are too many companies that appear on
the graph. So i would like to modify the query so that only companies that
produce over a certain share to be shown, and all companies beneath that
share to be grouped together and called other (all shares will be summed
together). Is this possible? I have included my query for it.

SELECT qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier, qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;
Click to expand...
Click to expand...
 
OK, so i figured out how to return the bottom results that i wanted: i used
the top 5 method in the select statement, and then used an ascending order.
Now i need to figure out a way to add all of those values together and give
it the label "others" for its company name.

Nick 'The database Guy' said:
Hi JK,

The quick and easy way to do it is create two graphs, one of them is
powered by the SQL:

SELECT TOP 10 PERCENT qryFSModels.Region,
qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON
qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;

Sorry I can't find a way to display the lower 90%.

Hope this helps.

Nick
OK here is the problem. I need to create a query that will return market
shares. A chart will be based off of this query. The query works perfectly
and the graph works. However, there are too many companies that appear on
the graph. So i would like to modify the query so that only companies that
produce over a certain share to be shown, and all companies beneath that
share to be grouped together and called other (all shares will be summed
together). Is this possible? I have included my query for it.

SELECT qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier, qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;
Click to expand...
Click to expand...
 
Hi JK,

Well done for working that one out, I think that you could get it all
in one query by using the two separate queries, one for the top, say
25% all group on company, the other query showing the bottom 75% SUMed
and not grouped by anything. Then create a union query, by going in to
the sql view of the second query then copying the sql in to the sql
window of the first query. These two SELECT statements should be
separated by a UNION ALL statement. The SELECT statement that form the
union query must return the same number of rows, to give the lower 75%
the label others, you must enter the following in the 'Field:' box on
query 2: Company: "Others" this should be done before any cutting and
pasting done. If you follow this route you can also group the second
query by company name as they will all be the same!

Hope I have given you enough info this time.

Good luck,

Nick
OK, so i figured out how to return the bottom results that i wanted: i used
the top 5 method in the select statement, and then used an ascending order.
Now i need to figure out a way to add all of those values together and give
it the label "others" for its company name.

Nick 'The database Guy' said:
Hi JK,

The quick and easy way to do it is create two graphs, one of them is
powered by the SQL:

SELECT TOP 10 PERCENT qryFSModels.Region,
qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON
qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;

Sorry I can't find a way to display the lower 90%.

Hope this helps.

Nick
OK here is the problem. I need to create a query that will return market
shares. A chart will be based off of this query. The query works perfectly
and the graph works. However, there are too many companies that appear on
the graph. So i would like to modify the query so that only companies that
produce over a certain share to be shown, and all companies beneath that
share to be grouped together and called other (all shares will be summed
together). Is this possible? I have included my query for it.

SELECT qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier, qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much for all of your help. It works perfectly now.

Nick 'The database Guy' said:
Hi JK,

Well done for working that one out, I think that you could get it all
in one query by using the two separate queries, one for the top, say
25% all group on company, the other query showing the bottom 75% SUMed
and not grouped by anything. Then create a union query, by going in to
the sql view of the second query then copying the sql in to the sql
window of the first query. These two SELECT statements should be
separated by a UNION ALL statement. The SELECT statement that form the
union query must return the same number of rows, to give the lower 75%
the label others, you must enter the following in the 'Field:' box on
query 2: Company: "Others" this should be done before any cutting and
pasting done. If you follow this route you can also group the second
query by company name as they will all be the same!

Hope I have given you enough info this time.

Good luck,

Nick
OK, so i figured out how to return the bottom results that i wanted: i used
the top 5 method in the select statement, and then used an ascending order.
Now i need to figure out a way to add all of those values together and give
it the label "others" for its company name.

Nick 'The database Guy' said:
Hi JK,

The quick and easy way to do it is create two graphs, one of them is
powered by the SQL:

SELECT TOP 10 PERCENT qryFSModels.Region,
qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON
qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;

Sorry I can't find a way to display the lower 90%.

Hope this helps.

Nick

JKarchner wrote:
OK here is the problem. I need to create a query that will return market
shares. A chart will be based off of this query. The query works perfectly
and the graph works. However, there are too many companies that appear on
the graph. So i would like to modify the query so that only companies that
produce over a certain share to be shown, and all companies beneath that
share to be grouped together and called other (all shares will be summed
together). Is this possible? I have included my query for it.

SELECT qryFSModels.Region, qryFSFuelTankSuppliers.Supplier,
Sum([Production]*[SupplyPercent]) AS [FT Supplier Supply]
FROM qryFSModels LEFT JOIN qryFSFuelTankSuppliers ON qryFSModels.Model_ID =
qryFSFuelTankSuppliers.Model_ID
GROUP BY qryFSModels.Region, qryFSFuelTankSuppliers.Supplier, qryFSModels.Year
HAVING (((qryFSModels.Region)="eur") AND
((Sum([Production]*[SupplyPercent]))>0) AND ((qryFSModels.Year)=2006))
ORDER BY qryFSModels.Region, Sum([Production]*[SupplyPercent]) DESC;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help combining queries 1
Query Help Needed 8
Combining 3 queries 2
Max function query 3
Top 25 brokers query help 1
Need Help With SQL Query 2
Access Sum in Query 11
Help to merge 2 access queries 1

Back
Top