Finding Latest Record for an Associate

Hi:

I have used this group a number of times simply because while the manuals for Access may
show the technology, but most are really weak on how to apply it in special situations and
the indexes are less that complete!. This is also the first real application iI have
developed for Access, although I have done a number in Lotus Approach.


I have a table with information on how an Associate is to be handled under a specific
Purchase Order for services, and how he/she is to be allocated and tracjed withing the
project. For reasons I wont go into, however, this becomes complicated quickley.

There may be multiple record for an associate under a PO in this table, and there could be
more than one record for the latest date span. I want to select the latest (first) record
for each associates under a given purchase order, and am having trouble doing it with a
query (I am not an SQL maven).

The fields of interest are PO number; Associate ID; Start Date; End Date. My challenge is
to find the first record for each Associated and PO in a data groupings.

Putting the records in order by PO; Associate; Start Date; End Date is a begtinning, but
at that point I get stuck. Suggestions would be appreciated.

I also would like to be able to validate to ensure that (unless an exceptional condition
identified by a yes/no switch exists) there are no duplicate from through dates for an
associated (this would be a different query).

Any help anyone could give with either of these troublesome issues would be much
appreciated.

Regards

John Baker

re: Finding Latest Record for an Associate

See:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The article discusses 4 solutions. The subquery will probably be the most
useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Baker.JH@Verizon.net> wrote in message
news:g81ruv0rmmd2k6810bae5mppi7vvbqb7dq@4ax.com...[color=blue]
>
> I have used this group a number of times simply because while the manuals[/color]
for Access may[color=blue]
> show the technology, but most are really weak on how to apply it in[/color]
special situations and[color=blue]
> the indexes are less that complete!. This is also the first real[/color]
application iI have[color=blue]
> developed for Access, although I have done a number in Lotus Approach.
>
>
> I have a table with information on how an Associate is to be handled under[/color]
a specific[color=blue]
> Purchase Order for services, and how he/she is to be allocated and tracjed[/color]
withing the[color=blue]
> project. For reasons I wont go into, however, this becomes complicated[/color]
quickley.[color=blue]
>
> There may be multiple record for an associate under a PO in this table,[/color]
and there could be[color=blue]
> more than one record for the latest date span. I want to select the latest[/color]
(first) record[color=blue]
> for each associates under a given purchase order, and am having trouble[/color]
doing it with a[color=blue]
> query (I am not an SQL maven).
>
> The fields of interest are PO number; Associate ID; Start Date; End Date.[/color]
My challenge is[color=blue]
> to find the first record for each Associated and PO in a data groupings.
>
> Putting the records in order by PO; Associate; Start Date; End Date is a[/color]
begtinning, but[color=blue]
> at that point I get stuck. Suggestions would be appreciated.
>
> I also would like to be able to validate to ensure that (unless an[/color]
exceptional condition[color=blue]
> identified by a yes/no switch exists) there are no duplicate from through[/color]
dates for an[color=blue]
> associated (this would be a different query).
>
> Any help anyone could give with either of these troublesome issues would[/color]
be much[color=blue]
> appreciated.
>
> Regards
>
> John Baker[/color]

re: Finding Latest Record for an Associate

Use a Totals query to GROUP BY whatever makes a duplicate, and COUNT the
primary key. Set Criteria on the Count of primary key to:[color=blue]
> 1[/color]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Baker" <Baker.JH@Verizon.net> wrote in message
news:g81ruv0rmmd2k6810bae5mppi7vvbqb7dq@4ax.com...[color=blue]
>
> I also would like to be able to validate to ensure that (unless an[/color]
exceptional condition[color=blue]
> identified by a yes/no switch exists) there are no duplicate from through[/color]
dates for an[color=blue]
> associated (this would be a different query).
>
> Any help anyone could give with either of these troublesome issues would[/color]
be much[color=blue]
> appreciated.
>
> Regards
>
> John Baker[/color]