RE: Mode Function in Access
From: ChrisJ (ChrisJ_at_discussions.microsoft.com)
Date: 11/17/04
- Next message: John Spencer (MVP): "Re: Selection Criteria"
- Previous message: Chip Asmus: "Query for all but one item"
- In reply to: QVCJDN: "RE: Mode Function in Access"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Nov 2004 15:05:01 -0800
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
"QVCJDN" wrote:
> 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
>
> "ChrisJ" wrote:
>
> > 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
> >
> >
- Next message: John Spencer (MVP): "Re: Selection Criteria"
- Previous message: Chip Asmus: "Query for all but one item"
- In reply to: QVCJDN: "RE: Mode Function in Access"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
