Make-Table Query Ranking Sum

[Guest]

I am trying to create a Make-Table Query using the design grid that will have
2 fields named BillProvName and AllowedAmt that come from a table named Gyn
Util by Report Group. The new table is named Top Provider by AllowedAmt. I
want to group by BillProvName and sum the AllowedAmt. The trick is I want
the table to consist of only the top 10 BillProvName based on the sum of the
AllowedAmt. I'd prefer to do this in the design grid if possible.

Many thanks to anyone who can help!

 

[Smartin]

I am trying to create a Make-Table Query using the design grid that will have
2 fields named BillProvName and AllowedAmt that come from a table named Gyn
Util by Report Group. The new table is named Top Provider by AllowedAmt. I
want to group by BillProvName and sum the AllowedAmt. The trick is I want
the table to consist of only the top 10 BillProvName based on the sum of the
AllowedAmt. I'd prefer to do this in the design grid if possible.

Many thanks to anyone who can help!

SELECT TOP 10
BillProvName, SUM(AllowedAmount)
INTO [Top Provider by AllowedAmt]
FROM
[Gyn Util by Report Group]
GROUP BY
BillProvName
ORDER BY
SUM(AllowedAmount) DESC
;

First, why the make table query? [It seems there is some kind of
obsession with make tables 'round here (^: ] If your underlying data
changes the data in your table could become outdated and false.
Suggestion -- just use a SELECT query and run it whenever you want the
top 10.

Second, I've provided both solutions: a plain old SELECT, and a
make-table. The line that begins "INTO.." makes this a make table query.
Remove this line to do a plain old SELECT.

Third, it was easier to show the SQL code rather than explain the query
builder design. If you paste this into SQL view then switch to Design
view everything should fall into place. To see all the tweaks be sure to
view Properties and check the "Top Values" and "Destination Table"
properties.

Happy querying!

 

[Guest]

Thanks, that worked beautifully!

One more question though, when I switched over to the design grid, it didn't
show the select criteria, which is fine, but for future use could you show me
how I should write the select function in the design grid?

Thanks again!

I am trying to create a Make-Table Query using the design grid that will have
2 fields named BillProvName and AllowedAmt that come from a table named Gyn
Util by Report Group. The new table is named Top Provider by AllowedAmt. I
want to group by BillProvName and sum the AllowedAmt. The trick is I want
the table to consist of only the top 10 BillProvName based on the sum of the
AllowedAmt. I'd prefer to do this in the design grid if possible.

Many thanks to anyone who can help!

SELECT TOP 10
BillProvName, SUM(AllowedAmount)
INTO [Top Provider by AllowedAmt]
FROM
[Gyn Util by Report Group]
GROUP BY
BillProvName
ORDER BY
SUM(AllowedAmount) DESC
;

First, why the make table query? [It seems there is some kind of
obsession with make tables 'round here (^: ] If your underlying data
changes the data in your table could become outdated and false.
Suggestion -- just use a SELECT query and run it whenever you want the
top 10.

Second, I've provided both solutions: a plain old SELECT, and a
make-table. The line that begins "INTO.." makes this a make table query.
Remove this line to do a plain old SELECT.

Third, it was easier to show the SQL code rather than explain the query
builder design. If you paste this into SQL view then switch to Design
view everything should fall into place. To see all the tweaks be sure to
view Properties and check the "Top Values" and "Destination Table"
properties.

Happy querying!

 

[Smartin]

Hmm... it worked for me. Alright then, here's a facsimile of the query
builder.

First you have your [Gyn Util by Report Group] table up there, then the
query looks something like this:

Field: BillProvName AllowedAmount
Table: [Gyn Util by Report Group] [Gyn Util by Report Group]
Total: Group By Sum
Sort: Descending
Show: (checked) (checked)
Criteria:

Then in the query properties (View, Properties), change this value:

Top Values: 10

To make it into a make-table query, choose Query, Make-Table... , enter
the name of the new table [Top Provider by AllowedAmt].

I'm pretty sure that's it.

Hope this helps!

Thanks, that worked beautifully!

One more question though, when I switched over to the design grid, it didn't
show the select criteria, which is fine, but for future use could you show me
how I should write the select function in the design grid?

Thanks again!

I am trying to create a Make-Table Query using the design grid that will have
2 fields named BillProvName and AllowedAmt that come from a table named Gyn
Util by Report Group. The new table is named Top Provider by AllowedAmt. I
want to group by BillProvName and sum the AllowedAmt. The trick is I want
the table to consist of only the top 10 BillProvName based on the sum of the
AllowedAmt. I'd prefer to do this in the design grid if possible.

Many thanks to anyone who can help!

SELECT TOP 10
BillProvName, SUM(AllowedAmount)
INTO [Top Provider by AllowedAmt]
FROM
[Gyn Util by Report Group]
GROUP BY
BillProvName
ORDER BY
SUM(AllowedAmount) DESC
;

First, why the make table query? [It seems there is some kind of
obsession with make tables 'round here (^: ] If your underlying data
changes the data in your table could become outdated and false.
Suggestion -- just use a SELECT query and run it whenever you want the
top 10.

Second, I've provided both solutions: a plain old SELECT, and a
make-table. The line that begins "INTO.." makes this a make table query.
Remove this line to do a plain old SELECT.

Third, it was easier to show the SQL code rather than explain the query
builder design. If you paste this into SQL view then switch to Design
view everything should fall into place. To see all the tweaks be sure to
view Properties and check the "Top Values" and "Destination Table"
properties.

Happy querying!