Similiar to the auxilliary dates table, but with less overhead and bloat, I
use another technique.
I have a table (tbl_Numbers) which contains a single field (numValues), and
only has 10 records (0-9). I use this table to create a query (qry_Numbers)
that contains the values from 0 to 1000, but you could easily make that
number much larger. That query looks like:
SELECT Hundreds.numValues * 100 + Tens.numValues * 10 + Ones.numValue
as myValues
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones
Methinks you worry too much about bloat <g>.
My Calendar table has circa 11K rows to cover three decades, hardly
large. My Sequence table is 100K rows out of necessity. Hey, you could
generate your Sequence table on the fly using any existing table e.g.
SELECT Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr AS seq
FROM (SELECT nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION
ALL SELECT DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM
Customers UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT
DISTINCT 4 FROM Calendar UNION ALL SELECT DISTINCT 5 FROM Customers
UNION ALL SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7
FROM Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL
SELECT DISTINCT 9 FROM Calendar) AS Digits) AS Units, (SELECT nbr * 10
AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Customers
UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Calendar UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL SELECT
DISTINCT 9 FROM Customers) AS Digits) AS Tens, (SELECT nbr * 100 AS
nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Calendar
UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Customers UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Calendar UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL SELECT
DISTINCT 9 FROM Customers) AS Digits ) AS Hundreds, (SELECT nbr * 1000
AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Customers
UNION ALL SELECT DISTINCT 3 FROM Calendar UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Customers UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Customers UNION ALL SELECT DISTINCT 8 FROM Calendar UNION ALL SELECT
DISTINCT 9 FROM Customers ) AS Digits) AS Thousands, (SELECT nbr *
10000 AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Calendar UNION ALL
SELECT DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM
Customers UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT
DISTINCT 4 FROM Customers UNION ALL SELECT DISTINCT 5 FROM Calendar
UNION ALL SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7
FROM Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL
SELECT DISTINCT 9 FROM Customers) AS Digits) AS TenThousands;
Makes queries that utilise it a little harder to read, though <g>. As
for overhead, doesn't it take more to generate the set on the fly
rather than generate one and store in an auxilliary table?
A Calendar table has other advantages too e.g. differentiate work
days, holidays, etc.
Jamie.
--