Mode Function in Access

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

Guest

Is there a way to create an expression that would return the most frequently
recurring value from multiple fields?

Dave
 
Dear Dave:

If you find yourself needing to look for a value across multiple
columns, then the first problem is probably normalization. There are
ways of putting all such values into a single column, and database
theory and practice will penalize you severely if you do not.

It may be reasonable to use a UNION ALL to create a normalized view of
this data, at least temporarily. You could then proceed to product a
Maximum of the Count for each value, using that to filter back to the
value(s) that have that Maximum Count.

In order to give you more specific details on doing this, I would need
more specific information about your situation.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Let's assume that your table has the fields

PrimaryKey,
Field1
Field2
Field3
Field4
Field5
Field6
Field7

I would create a query

Select PrimaryKey, 'F1' as SourceField, Field1 as FieldData from YourTable
Union
Select PrimaryKey, 'F2' as SourceField, Field2 as FieldData from YourTable
Union
Select PrimaryKey, 'F3' as SourceField, Field3 as FieldData from YourTable
Union
Select PrimaryKey, 'F4' as SourceField, Field4 as FieldData from YourTable
Union
Select PrimaryKey, 'F5' as SourceField, Field5 as FieldData from YourTable
Union
Select PrimaryKey, 'F6' as SourceField, Field6 as FieldData from YourTable
Union
Select PrimaryKey, 'F7' as SourceField, Field7 as FieldData from YourTable

Save this as qrySource

Then create a second query

Select PrimaryKey, FieldData, count(SourceField) as Occurance from qrySource
Group by PrimaryKey, FieldData

Save this as qry Source1

Then a third query

Select PeimaryKey, FieldData, max(Occurance) from qrySource1
Group by PrimaryKey, FieldData

This should give you what you are looking for.
Note that if in one of your source rows you have 3 ones, 3 twos and 1 three,
you may get either returned
 
Sorry for the delay, I just got back to this project. This looks like the
direction that I need to go, but I'm afraid that I don't now SQL (it appears
that that's your notation). My data looks just like you laid it out. A
primary key (Control Number), followed by fields for Saturday through Friday.
In each field will be a value for a work shift (1, 2, or 3). Depending on
what shift the employee is assigned the most, the correct value would be
returned. Could you explain your queries a bit more?

Dave
 
If you can send me the name of the table and the names of the fields and
perhaps some sample data, I can write the query for you and try it against
the sample data.

Send to
chris
at
mercury-projects
dot
co
dot
nz
 
Back
Top