[AccessD] difficult query??

Thanks Gustav,

it works perfect.

Pedro Janssen


----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, November 09, 2004 12:56 PM
Subject: Re: [AccessD] difficult query??


> Hi Pedro
>
> > i have a table [tblA] with 20 fields.
> > One of these fields [Number] has a value from 1 to 25.
> > I would like each record with a value in field [number] giving the
ammount of records of the value of field Number in a new table. And the
value of field [Number] must change into "X". All other
> > fields must also be present.
>
> > for example
>
> > i have
> > tblA
>
> > ID   fieldA  Number etc etc etc
> > 1       A           3
>
>
> > i need
> > ID   fieldA  Number etc etc etc
> > 1       A           X
> > 1       A           X
> > 1       A           X
>
> > Can this been done?
>
> Yes. Create a table, tbzMultiply, with one integer field, Factor, and
> create records from 1 to 25 or more, say, 100.
>
> Then use:
>
>   SELECT
>     tblA.ID,
>     tblA.FieldA,
>     ...
>     tblA.FieldZ,
>     "X" AS [Number]
>   FROM
>     tblA,
>     tbzMultiply
>   WHERE
>     (tbzMultiply.Factor Between 1 And tblA.[Number])
>   ORDER BY
>     tblA.ID;
>
> Note the missing join. This is a Cartesian (multiplying) query.
>
> /gustav
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>