Need Help in query

[djkoon]

I create a make table query
from two tables
which gave me data as

id fnclId SpecCd

1260 24502 125
1260 24502 018
1260 24502 033
1261 24555 111
1261 24555 555


I have to update other table with this result
but other table has
go as unique record with multiple SpecCd

as

id fnclId SpecCd1 SpecCd2 SpecCd3
1260 24502 125 018 033
1261 24555 111 555 000

Mean other table do not repeat the record instead it split the specCD



Any help will be great

Thanks

 

[hkaing79]

If you only have 3 columns...

Select DISTINCT ID, fncID, Dlookup("DMax(SpecCD)","tableName","ID=" & ID) AS SpecCD1, Dlookup("DMin(SpecCD)","tableName","ID=" & ID) AS SpecCD2, Dlookup("SpecCD","tableName","ID=" & ID & " AND SpecCD>" & SpecCD2 & " AND SpecCD<" & SpecCD1) AS SpecCD3 FROM tableName

I'm assuming same ID and fncID.

 

[djkoon]

It's give me message wrong number of argument in DMax(SpecCD)",

 

[hkaing79]

Are you sure you typed it in correctly?

Dlookup( "DMax(SpecCD)" , "tablename" , "ID=" & ID )

 

[djkoon]

it does not work or it's not workable

 

[djkoon]

Yes I typed exactly as you say

DMax(expr, domain[, criteria]) how come you didn't write domain

again I'm great full of you that you took effort to help me

 

[hkaing79]

Odd, it worked for me. Are those fields numerical?

 

[djkoon]

here is the data from table a

Coast_Id FNM_LN_ID LN_SPCL_FEAT_CD
1261 4000024502 3
1261 4000024502 175
1261 4000024502 402
1262 4000024501 3
1262 4000024501 135
1262 4000024501 402
1274 4000024446 3
1274 4000024446 122

Now I have to place this data in to table say B
like this

Coast_Id FNM_LN_ID
1261 4000024502
1262 4000024501
1274 4000024446

LN_SPCL_FEAT_CD1,LN_SPCL_FEAT_CD2,LN_SPCL_FEAT_CD3
3 175 402
3 135 402
3 222 000

there could be 20 record forn one Coast_id and then I have to place for on Coast_id 20 LN_SPCL_FEAT_CD ...

I try my best to explain the senario.
Hope fully you will help me better way now
all field are number type.

Thanks

 

[hkaing79]

Is there a reason that you want to denormalize your data?

First thing I did was to create a rank order query and turned it into a temporary table (for some reason I couldn't do a cross tab query based on the rank order query...think it has to do something with the subquery). Then I created a crosstab query based on the temporary query.

The two SQL I used are:

SELECT Field1, Field2, (SELECT Count(*) FROM tblTest AS A WHERE tblTest.Field1=A.Field1 AND tblTest.Field2>A.Field2) AS Rank INTO tmp
FROM tblTest;

TRANSFORM First(tmp.Field2) AS FirstOfField2
SELECT tmp.Field1
FROM tmp
GROUP BY tmp.Field1
PIVOT tmp.Rank;

 

[djkoon]

Thanks hkaing79 for your kind help
let me try this

I appreciate the time you took to look at my problem

Great