SQL problems....Please Help!!!

[BigFizil]

.Source = "select Development.[KinderNumber],Development.[Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], developmentpictures.[Image], developmentpictures.[Revision_ Letter]FROM developmentpictures, Development WHERE developmentpictures.[KinderNum]= Development.[KinderNumber]"



There is my SQL statement that looks to 2 tables and one table is info and the other houses REVISION LETTER and PICTURE.

What I need to do.....Is return the HIGHEST LETTER in the REVISION_Letter column for each ID# as of now I get all ID # 's with all revisions.

I need to set up a SQL statement that will return the highest revision letter for each ID#.

I tried to SELECT MAX(developmentpictures.[Revision_ Letter]) but it gave me a error?


Please Help!!!!

 

[jrbarnett]

This should do it:

select development.[KinderNumber], Development.[Date], Development.[Client], Development.[MFG], Development.[Fixture], Development.[Dimensions (HxWxD)], Development.[MFG Price], Development.[Item Number], developmentpictures.[Image], Max(developmentpictures.[Revision_ Letter])
FROM developmentpictures, Development WHERE developmentpictures.[KinderNum]= Group By development.[KinderNumber], Development.[Date], Development.[Client], Development.[MFG], Development.[Fixture], Development.[Dimensions (HxWxD)], Development.[MFG Price], Development.[Item Number], developmentpictures.[Image]

You were almost there with your item: Note the Group By on all statements except the Max( Revision_Letter) field.

John

 

[mpastore]

Try this:

Source = "select Development.[KinderNumber],Development.[Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], dp.[Image], dp.[Revision_ Letter] FROM developmentpictures dp, Development WHERE dp.[KinderNum]= Development.[KinderNumber] AND dp.[Revision_ Letter] IN(select max([Revision_ Letter]) from developmentpictures where kindernum = dp.kindernum)




"

 

[BigFizil]

MPASTORE....your solution worked!


thank you....now I am trying to add a left or right join to see all the PICTURES in the picture table regardless of the information in the DEV table.....SO I want to keep the SQL statement the same but I need to add the JOIN into it

Source = "select Development.[KinderNumber],Development.[Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], dp.[Image], dp.[Revision_ Letter] FROM developmentpictures dp, Development WHERE dp.[KinderNum]= Development.[KinderNumber] AND dp.[Revision_ Letter] IN(select max([Revision_ Letter]) from developmentpictures where kindernum = dp.kindernum)

 

[mpastore]

I think this is it:

Source = "select Development.[KinderNumber],Development.[Date],Development.[Client],Development.[MFG],Development.[Fixture],Development.[Dimensions (HxWxD)],Development.[MFG Price],Development.[Item Number], dp.[Image], dp.[Revision_ Letter] FROM developmentpictures dp LEFT JOIN Development ON dp.[kindernum] = development.[kindernum] WHERE dp.[Revision_ Letter] IN(select max([Revision_ Letter]) from developmentpictures where kindernum = dp.kindernum)"

 

[BigFizil]

I recieved an error message " NO VALUE GIVE FOR ONE OR MORE OF THE REQUIRED PARAMETERS"

Also when I debug ".OPEN" is highlighted

 

[mpastore]

Try this: (note, I've replaced first field in SQL kindernum with dp.kindernum because outer join would return a null):

Source = "SELECT dp.kindernum, Development.date, Development.client, Development.MFG, Development.fixture, Development.[Dimensions (HxWxD)], Development.[MFG Price], Development.[item number], dp.image, dp.[Revision_ Letter]
FROM developmentpictures AS dp LEFT JOIN Development ON dp.kindernum = Development.kindernumber
WHERE (((dp.[Revision_ Letter]) In (select max([Revision_ Letter]) from developmentpictures where kindernum = dp.kindernum)))"