ranking query

[mcnews]

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

 

[Jerry Whittle]

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.

 

[Philip Herlihy]

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

 

[mcnews]

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 [%$##@_Alias].allserotype, Count([%
$##@_Alias].allserotype) 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 [%$##@_Alias].allserotype
ORDER BY Count([%$##@_Alias].allserotype) 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.

 

[mcnews]

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 [%$##@_Alias].allserotype, Count([%
$##@_Alias].allserotype) 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 [%$##@_Alias].allserotype
ORDER BY Count([%$##@_Alias].allserotype) 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;

 

[John W. Vinson]

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?

 

[mcnews]

this gives me exactly what i was looking for in Access, but doesn't
fly with ms sql:
SELECT TOP 5 [%$##@_Alias].allserotype, Count([%
$##@_Alias].allserotype) 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 [%$##@_Alias].allserotype
ORDER BY Count([%$##@_Alias].allserotype) 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

 

[Arvin Meyer [MVP]]

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;

 

[mcnews]

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;

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