Question -sub query

I’m needing help with a query here. I have a table with an ID and Date which are my PK’s.

I believe this will take a sub query. I want several fields pulled (one per ID). It would be the one with the max(date)

Here is what I have so far (yes I know it doesn’t work but you should get the idea).

select myID, myDate, field1, field2, field3, field4

FROM

(select myID,max(myDate)

from myTable1

WHERE

DATALENGTH(field1)> 0OR

DATALENGTH(field2)> 0OR

DATALENGTH(field3)> 0OR

DATALENGTH(field4)> 0

groupby myID)

By the way the sub query here works on it’s own and gives me the records I want, I just need the other fields pulled in there

Here you are:

SELECT t1.myID, t1.myDateMax, t2.field1, t2.field2, t2.field3, t2.field4FROM

(SELECT myID,max(myDate)AS myDateMax

FROM myTable1WHERE field1ISNOTNULLOR field2ISNOTNULLOR field3ISNOTNULLOR field4ISNOTNULL

GROUPBY myID)AS t1INNERJOIN myTable1AS t2ON t1.myID=t2.myIDAND t1.myDateMax=t2.myDate

|||

Beautiful!!

Thank you Limno

sql

Question regarding connections with MSAccess

I have a Access 2002 (saved as 2K format) front end to a SQL Server (2000 I believe) and everything worked fine until I added 30 new fields to the main table. All of these fields are data type Bit and all are initialized to zero. I can add new records without any issues. The problem is that any time I try to update or delete a record I get the dreaded "Write Conflict..." msg in Access. If I use an older version of the system without refreshing the linked tables I have no problems. This behavior is also exhibited if I try to update/delete a record directly in the table so I know it is not something in my forms. Any help would be greatly appreciated.

Ron

The solution was to change the data type on the SQL Server to a 1 byte text field and poputate it programatically rather then having it directly bound to a control such as a checkbox. I never got a good answer as to why this was happening so I had to use the previously described workaround.

Question regarding connections with MSAccess

I have a Access 2002 (saved as 2K format) front end to a SQL Server (2000 I believe) and everything worked fine until I added 30 new fields to the main table. All of these fields are data type Bit and all are initialized to zero. I can add new records without any issues. The problem is that any time I try to update or delete a record I get the dreaded "Write Conflict..." msg in Access. If I use an older version of the system without refreshing the linked tables I have no problems. This behavior is also exhibited if I try to update/delete a record directly in the table so I know it is not something in my forms. Any help would be greatly appreciated.

Ron

The solution was to change the data type on the SQL Server to a 1 byte text field and poputate it programatically rather then having it directly bound to a control such as a checkbox. I never got a good answer as to why this was happening so I had to use the previously described workaround.sql