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

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?

/gustav

>>> ssharkins at gmail.com 24-01-2008 02:56:13 >>>
Really! Now that's interesting.

Susan H.

>A join without values is equivalent to relational multiplication: the 
>result set has the number of rows in T1 * the number of rows in T2.