Access Databases and AccessDataSource Control

Re: Select Statement Parameter

Naom — Wed, 01 Jul 2009 19:23:21 GMT

select SoNumber, min(RegDate) as RegDate from myTable group by SoNumber

Re: Select Statement Parameter

mrgasp07 — Wed, 01 Jul 2009 16:53:35 GMT

SELECT DISTINCT sonumber, reqdate
FROM [Your Results]

sonumber reqdate

0001 06/07/09

0002 06/08/09

0003 06/09/09

0003 06/10/09

0003 06/11/09

I would like it only display the next req date for the sonumber. (Top 1, ASC ????)

sonumber reqdate

0001 06/07/09

0002 06/08/09

0003 06/09/09

Thanks in advance

Re: Select Statement Parameter

Naom — Fri, 26 Jun 2009 20:56:42 GMT

Can you list your tables structures? Do you want to get the latest date per SoNumber? Without knowing the details of your problem I can only suggest to review

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#5 for ideas of how to get the info. This is in SQL Server, but in Access the approach of using derived table is the same.

Select Statement Parameter

mrgasp07 — Fri, 26 Jun 2009 18:01:22 GMT

SELECT DISTINCT [Your Results].sonumber, [Your Results].company, [Your Results].custnumber, [Your Results].sodate, jobdata.dept, jobdata.status, jobdata.ID, jobdata.notes, jobdata.sonumber AS Expr1, [SO total query].SumOfextprice, [Your Results].reqdate

FROM (([Your Results] INNER JOIN jobdata ON [Your Results].sonumber = jobdata.sonumber) INNER JOIN [SO total query] ON jobdata.sonumber = [SO total query].sonumber)

****************************************

The above statement works fine. I would like to add a parameter to only selects the next req date and not show all the req dates in the gridview for the particular sonumber. Only display one reqdate which should be the soonest date.

Thanks in advance