[AccessD] Multiplying query (was: SQL INSERT Question)

:) I know about the Cartesian join, I am just surprised to see it show up in 
a multi-table INSERT INTO -- interesting. If I weren't so old and feeble, I 
could probably figure it out. :)

Susan H.


> Hi Susan
>
> It even has a name: Cartesian join.
>
> It can be quite useful for some special cases.
> Create a table, tbzMultiply, with one Integer field, Factor, with 100 
> records - from 1 to 100.
>
> For example,to create a query that will return one record for every minute 
> of the year - 527040 records for 2008! - do like this:
>
> PARAMETERS
>  Year Integer;
> SELECT DISTINCT
>  DateSerial([Year], [M]![Factor], [D]![Factor]) + 
> TimeSerial([H]![Factor] - 1, [N]![Factor] - 1, 0) AS MinutesOfYear
> FROM
>  tbzMultiply AS M,
>  tbzMultiply AS D,
>  tbzMultiply AS H,
>  tbzMultiply AS N
> WHERE
>  M.Factor Between 1 And 12
>  AND
>  D.Factor Between 1 And 31
>  AND
>  H.Factor Between 1 And 24
>  AND
>  N.Factor Between 1 And 60
> ORDER BY
>  DateSerial([Year], [M]![Factor], [D]![Factor]) + 
> TimeSerial([H]![Factor] - 1, [N]![Factor] - 1, 0);
>
> Now, this can easily be expanded to return the 31622400 records for every 
> second of this year or for 10 years but I haven't tried that.
> Perhaps someone with a fast machine will do a test run?