to get a specific type of table in output from other table

[rushitshah]

I think from an example it will be more clear what I want.

Me.Pcycle is my original table that has all fields and data too.

product | parameter | Item Des.| jan'05 feb'05 jan'06 jan'07.....| Revision
________________________________________________________________

M1 test time SED .2 .3 .4 0
M1 burn time SED .2 .5 .8 0
M1 test time SED .2 .2 .4 1
M2 test time SED .1 .3 .4 0
M2 test time SED .2 .9 .5 1
M2 test time SED .1 .1 .1 2
so this table has all the data in it.

I am running a query for Max(Revised) and by grouping all the rest of critical fields to find out latest records from this table and that is Latest table. Higher revision number indicates latest record.

Since I am running the query
"SELECT s.product, s.parameter, s.Item Des., Max(Revison)" & _
"INTO

" & _
"FROM [" & Me.PCycle & "] s " & _
"GROUP BY s.product, s.parameter, s.Item Des;"

So latest table is,

product | parameter | Item Des.| Revision
________________________________________

M1 burn time SED 0
M1 test time SED 1
M2 test time SED 2
So, this table has only the latest records from the previous table.
As the SELECT clause in this query does not allow me to put s.* over there.....

I want a result table that has records only from latest table records, but they should have their corrosponding data value columns too from the me.Pcycle table.

[in the browser, The spacing of table might cause confusion...so
Product = M1, M2
parameter = burntime, testtime
Item Des. = SED
Revision = 0,1,2,3 etc.]

That is what I am trying to do.

Please help regarding this.

Thanks..

 

[rushitshah]

I am also trying with inner join.....Me.Pcycle is the table with all the fields that is data fields too. Duplicate is the summary table with latest records and I am saving the result table as Final_Duplicate table

strSQL4 = "SELECT p.* " & _
"INTO [Final_Duplicate] " & _
"FROM [" & Me.PCycle & "] p " & _
"INNER JOIN Duplicate d ON d.product, d.parameter, d.ItmDes, d.Revision;"

But the error is "Inner join expression not supported"

Can you modify the above query statement? What is the cause of this error?

Thanks for your continuous help and guidance.