Alternative method for crosstab qury

[Alp Bekisoglu]

Hi Experts,

Here's the environment: A2K, FE on each pc and BE on one PC on the network
I have a table to record forms returned from clients with fields as:
kat_id, firma_id, fair_id as Long, form_nm as text, form_no as Long,
form_tarihi as date/time

I have actually created a crosstab query to do what I need but it is taking
too long to return the result in a report, i.e. 20-30 seconds at least.
There are only around 350 records. With a reqular SLECT query it is almost
instantaneous but I need to have grouping so that all form_no's recorded
under one company should be displayed as a single line (just as crosstab
qery does). The first 3 fields will be the same per company but form_no will
be different. i.e.
155 8557 2 XMATERIALS 4 27/04/2007
155 8557 2 PARTICIPANT 3 27/04/2007
155 8557 2 CO ADDRESS 1 27/04/2007
Needed result: If form received 1, else 0 or ""
155 8557 2 <will not be used> 1 <blank> <blank> 3 4
<blank> <blank> <blank> <blank>

There are 8 forms aclient can return but I opted to design the table as
above instead of having fields from 1 to 8.

Any suggestions, advice would be highly appreciated be it code or query or
even a redesign of the data collection (table).

Thanks in advance,

Alp

 

[KARL DEWEY]

Try this crosstab --
TRANSFORM First(Alp_Bekisoglu.Field5) AS FirstOfField5
SELECT Alp_Bekisoglu.Field1, Alp_Bekisoglu.Field2, Alp_Bekisoglu.Field3
FROM Alp_Bekisoglu
GROUP BY Alp_Bekisoglu.Field1, Alp_Bekisoglu.Field2, Alp_Bekisoglu.Field3
PIVOT Alp_Bekisoglu.Field5 IN(1,2,3,4,5,6,7,8);
Substitute your table name for Alp_Bekisoglu and your field names for
Field1...Field5.