Show The top valued fields in a report

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

Guest

Say I have 7 fields in a form. Out of the 7 fields I wish to pull the top 3
highest feild on a report. So if
(field1=1),(field2=3),(field3=9),(field4=4),(field5=8),(field5=7),(field6=11),(field7=15)
so on the report for this record only fields 7,6 and 3 would show up on the
report. How would I go about trying to to this? Thanks
 
Marc

Take a step back first. It sounds like you are describing a series of
fields that all hold the same kind of value (else why compare among them?).
This is a standard design ... for a spreadsheet! But not for a
well-normalized relational database table (e.g., what Access is intended to
do).

Until you normalize your data structure, you will find that both you and
Access have to work extra hard to do what you're asking to do. But wait,
there's more! If you need to increase (or decrease the number of "fields"
you compare, you'll have to revise your table structure, your query
structure, and any forms, reports or code that depends on the current
version. This is a LOT of maintenance which you would not need to do with a
better table structure.

Is there a reason you don't just use Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
i would first normalize the table structure. If you can't change it, use a
union query like:

== quniNormalized==========
SELECT PrimaryKeyField, Field1 as TheValue, 1 as FieldNumber
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field2, 2
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field3, 3
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field4, 4
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field5, 5
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field6, 6
FROM tblUnNormalized
UNION ALL
SELECT PrimaryKeyField, Field7, 7
FROM tblUnNormalized;

Then you can write a query like:
SELECT Top 3 TheValue
FROM quniNormalized
WHERE PrimaryKeyField = "some value"
ORDER BY TheValue DESC, FieldNumber;
 
I'm sorry I should have been more specific. Okay I have 7 unbound fields,
they all have the calualtion "date1"-"date2"...and so on. Is there a way to
bring up on a report the top 3 values out of the 7?
 
"fields" are never unbound. Controls such as text boxes might be unbound if
they don't have anything in their control source property.
I don't know where Date1, Date2,... are coming from. Can you provide some
sample records with field names?
 
=IIf([Finished Date Man BR by QA]-[Packaging Date]<0,0,[Finished Date Man BR
by QA]-[Packaging Date])

This is the type of formula I have in 7 different text boxes. I need the top
3 highest values to show up on each record in a report. Can that be done?
 
Looks to be un-normalized to me. Apparently you have multiple date fields
and want to display only the 3 maximum of the dates.

You didn't respond to my request for "sample records with field names" so i
can't be sure.

--
Duane Hookom
MS Access MVP

Marc said:
=IIf([Finished Date Man BR by QA]-[Packaging Date]<0,0,[Finished Date Man
BR
by QA]-[Packaging Date])

This is the type of formula I have in 7 different text boxes. I need the
top
3 highest values to show up on each record in a report. Can that be done?

Duane Hookom said:
"fields" are never unbound. Controls such as text boxes might be unbound
if
they don't have anything in their control source property.
I don't know where Date1, Date2,... are coming from. Can you provide some
sample records with field names?
Click to expand...
Click to expand...
 
Back
Top