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
2  1826 
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Gleep |
last post by:
Hey Guys,
I've got a table called Outcomes. With 3 columns and 15 rows
1st col 2nd col 3rdcol
outcome date price
There are 15 rows...
|
by: Gleep |
last post by:
sorry i didn't explain it correctly before
my table is like this
example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)
I know that...
|
by: M.Stanley |
last post by:
Hi,
I have a problem..I'm doing a specific query where I'm joining fields
from a table with appednded data (there are duplicate records, except
for the date/time), and another query. I want the...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: Mark Vergara |
last post by:
Hi!
Is there any way of finding of what position in a
particular record ?
For example I have 3 records
1 Record 1..
2 Record 2..
3 Record 3..
|
by: Brett |
last post by:
I'd like to find URLs inside of an email message. If there is anything
between the <a></a>, I'd like to also get that and associate it with the URL
in the <a> tag.
Content between the <a></a>...
|
by: alanspamenglefield |
last post by:
Hello group,
I have an SQL statement which pulls data from a table as follows:
" SELECT tblSites.sites_siteno, " & _
" tblSites.sites_sitename, " & _
" Sum(tblStockResults.stkr_result) AS...
|
by: JohnMOsborn |
last post by:
I am designing an Access database that will use tab controls. Normally, you place different sets of fields on each page of the tab control – like Fields1-3 on Page 1, Fields 4-6 on Page 2, etc. In...
|
by: lwwhite |
last post by:
Another question. On one of my forms, I have two radio buttons: Topic and Window. Beside each of them is a drop-down to select the corresponding topic or window. When Topic is selected the window...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
