ranking query

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 + field2 + field3 values into one column?

tia,
mcnewsxp

You do it in a query and not in a table.

Assuming the fields are all number data types:

Ranking: [field1] + [field2] + [field3]

Then you can sort on this new Ranking field. One "gotcha" is if any of the
fields are null (i,e, blank), then it won't return a number. In that case
something like this will work:

Ranking: NZ([field1],0)+NZ([field2],0)+NZ([field3],0)

The NZ() function converts null values to a 0 in this case.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

"mcnews" wrote:

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 + field2 + field3 values into one column?

tia,
mcnewsxp

mcnews wrote:
how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 + field2 + field3 values into one column?

tia,
mcnewsxp

To concatenate two string fields, use an expression like this:

SELECT [tbl_expenses]![Date] & " " & [tbl_expenses]![Item] AS MyName
FROM tbl_expenses;

....which appears in the Query Builder as:
MyName: [tbl_expenses]![Date] & " " & [tbl_expenses]![Item]

Note that I've included a space between the two - you may not want this.
For three fields, or more, keep adding & [fieldname].
These are "expressions". Tip: it's worth learning to use the Expression
Builder for things like this.

To pick the Top 5, look for the drop-down on the tool bar (when using
the Query Builder) which will probably have "All" visible. Change that
to "5" or whatever you want. This gives the following SQL:

SELECT TOP 5 [tbl_expenses]![Date] & " " & [tbl_expenses]![Item] AS MyName
FROM tbl_expenses;


Watch out - this will give you the first 5 records in the query output,
even if you forgot to sort them, and sorting on a field which is the
concatenation of two (or three) fields means you might have to think
about this carefully - easy to produce nonsense.

Phil, London

On Jan 27, 11:49*am, mcnews wrote:
how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 *+ field2 + field3 values into one column?



this gives me exactly what i was looking for in Access, but doesn't
fly with ms sql:
SELECT TOP 5 , Count([%
) AS serocount
FROM
(SELECT specimen.serotype As allserotype
FROM specimen WHERE NOT IsNull(specimen.serotype) AND
specimen.serotype 'NEGATIVE' AND batchnum = 20075
UNION All
SELECT specimen.serotype2 As allserotype
FROM specimen WHERE NOT IsNull(specimen.serotype2) AND
specimen.serotype 'NEGATIVE' AND batchnum = 20075
UNION All
SELECT specimen.serotype3 As allserotype
FROM specimen WHERE NOT IsNull(specimen.serotype3) AND
specimen.serotype 'NEGATIVE' AND batchnum = 20075)
GROUP BY
ORDER BY ) DESC;

i know this is not an ms sql group, but anybody know how to make it
work. NOTE: i know the IsNull won't work as is.

On Jan 27, 1:05*pm, mcnews wrote:
On Jan 27, 11:49*am, mcnews wrote:

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 *+ field2 + field3 values into one column?

this gives me exactly what i was looking for in Access, but doesn't
fly with ms sql:
SELECT TOP 5 , Count([%
) AS serocount
FROM
(SELECT specimen.serotype *As allserotype
FROM specimen WHERE NOT IsNull(specimen.serotype) AND
specimen.serotype 'NEGATIVE' AND *batchnum = 20075
UNION All
SELECT specimen.serotype2 As allserotype
FROM specimen *WHERE NOT IsNull(specimen.serotype2) *AND
specimen.serotype 'NEGATIVE' *AND *batchnum = 20075
UNION All
SELECT specimen.serotype3 As allserotype
FROM specimen *WHERE NOT IsNull(specimen.serotype3) *AND
specimen.serotype 'NEGATIVE' *AND *batchnum = 20075)
GROUP BY
ORDER BY ) DESC;

i know this is not an ms sql group, but anybody know how to make it
work. *NOTE: i know the IsNull won't work as is.

here it is:

SELECT TOP 5 allserotype, Count(allserotype) AS serocount FROM
(SELECT specimen.serotype As allserotype
FROM specimen WHERE specimen.serotype '' AND specimen.serotype
'NEGATIVE' AND batchnum = 20075
UNION All
SELECT specimen.serotype2 As allserotype
FROM specimen WHERE specimen.serotype2 '' AND specimen.serotype
'NEGATIVE' AND batchnum = 20075
UNION All
SELECT specimen.serotype3 As allserotype
FROM specimen WHERE specimen.serotype3 '' AND specimen.serotype
'NEGATIVE' AND batchnum = 20075)
As a
GROUP BY allserotype
ORDER BY Count(allserotype) DESC;

On Tue, 27 Jan 2009 08:49:32 -0800 (PST), mcnews
wrote:

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 + field2 + field3 values into one column?

tia,
mcnewsxp

It may not be necessary to combine the three fields into one. You can sort by
any desired number of fields and set the Top Values property, and use whatever
ranking algorithm.

If this isn't working please post some details, such as the SQL view of your
current query. What are these fields? Could you post some sample data?
--

John W. Vinson [MVP]

On Jan 27, 1:20*pm, mcnews wrote:
On Jan 27, 1:05*pm, mcnews wrote:



On Jan 27, 11:49*am, mcnews wrote:

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 *+ field2 + field3 values into one column?

this gives me exactly what i was looking for in Access, but doesn't
fly with ms sql:
SELECT TOP 5 , Count([%
) AS serocount
FROM
(SELECT specimen.serotype *As allserotype
FROM specimen WHERE NOT IsNull(specimen.serotype) AND
specimen.serotype 'NEGATIVE' AND *batchnum = 20075
UNION All
SELECT specimen.serotype2 As allserotype
FROM specimen *WHERE NOT IsNull(specimen.serotype2) *AND
specimen.serotype 'NEGATIVE' *AND *batchnum = 20075
UNION All
SELECT specimen.serotype3 As allserotype
FROM specimen *WHERE NOT IsNull(specimen.serotype3) *AND
specimen.serotype 'NEGATIVE' *AND *batchnum = 20075)
GROUP BY
ORDER BY ) DESC;

i know this is not an ms sql group, but anybody know how to make it
work. *NOTE: i know the IsNull won't work as is.

here it is:

SELECT TOP 5 allserotype, Count(allserotype) AS serocount FROM
(SELECT specimen.serotype *As allserotype
FROM specimen WHERE specimen.serotype '' AND specimen.serotype
'NEGATIVE' AND *batchnum = 20075
UNION All
SELECT specimen.serotype2 As allserotype
FROM specimen *WHERE specimen.serotype2 '' *AND specimen.serotype
'NEGATIVE' *AND *batchnum = 20075
UNION All
SELECT specimen.serotype3 As allserotype
FROM specimen *WHERE specimen.serotype3 '' *AND specimen.serotype
'NEGATIVE' *AND *batchnum = 20075)
As a
GROUP BY allserotype
ORDER BY Count(allserotype) DESC;

= 20075 is replaced by = @BatchNum

"mcnews" wrote in message
...
how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 + field2 + field3 values into one column?

Use a query, which should look like:

SELECT TOP 5 NZ([field1],0)+NZ([ST],0)+NZ([Test],0) AS Ranking, field1,
field2, field3
FROM MyTable
ORDER BY NZ([field1],0)+NZ([field2],0)+NZ([field3],0) DESC;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Jan 28, 8:08*am, "Arvin Meyer [MVP]" wrote:
"mcnews" wrote in message

...

how would i return the values from 3 columns into one then rank the
top 5?
i suppose i would get the top 5 rows sorted DESC.
how to get

field1 *+ field2 + field3 values into one column?

Use a query, which should look like:

SELECT TOP 5 NZ([field1],0)+NZ([ST],0)+NZ([Test],0) AS Ranking, field1,
field2, field3
FROM MyTable
ORDER BY NZ([field1],0)+NZ([field2],0)+NZ([field3],0) DESC;
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

my bad. my wording sounded like i wanted to concatenate the 3 fields,
but i needed to merge them.
i worked it out.
thanks.