By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 IT Pros & Developers. It's quick & easy.

Finding Latest Record for an Associate

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <Ba******@Verizon.net> wrote in message
news:g8********************************@4ax.com...

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

Nov 12 '05 #2

P: n/a
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:
1
--
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" <Ba******@Verizon.net> wrote in message
news:g8********************************@4ax.com...
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.