[AccessD] List all tables and their fields

[Stuart McLachlan]

On 24 Mar 2023 at 8:44, Gustav Brock via AccessD wrote:

> You don't need it. 
> I've never had one. Instead, create a tiny query:
> 
> SELECT DISTINCT Abs([id] Mod 10) AS N
> FROM MSysObjects;
> 
> Save it as Ten.
> You can use that in Cartesian queries, for example to list all dates
> of the current year:
> 
> SELECT
> DateSerial(Year(Date()),1,1+[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100
> ) AS [Date] FROM Ten AS Ten_0, Ten AS Ten_1, Ten AS Ten_2 WHERE
> (((DateSerial(Year(Date()),1,1+[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*
> 100))<=DateSerial(Year(Date()),12,31)) AND ((Ten_2.N)<4)) ORDER BY
> DateSerial(Year(Date()),1,1+[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100
> );
> 

Much simpler:  when you first create the application, run this:

Function BuildNumbersTable(nums As Long) As Long
'Create and fill a numbers table - 10000 is a good start
Dim rs As DAO.Recordset
Dim x As Long
DoCmd.RunSQL "CREATE TABLE tblNumbers([Num] INTEGER PRIMARY KEY)"
Set rs = CurrentDb.OpenRecordset("tblNumbers")
For x = 1 To nums
   rs.AddNew
   rs(0) = x
   rs.Update
Next
rs.Close
MsgBox "Table Built"
End Function

Then if you want all dates from today for the next 10 years:, all you need is:

SELECT Date()+[num] AS dt
FROM tblNumbers
WHERE (((Date()+[num])<DateAdd("yyyy",10,Date())));

or the last 90 days

SELECT [Num]+Date()-90 AS dt
FROM tblNumbers
WHERE ((([Num]+Date()-90)<=Date()));

You then join other queries or tables to that sequence so that you have a full set of values 
whether there are matching records or not:

e.g. How many invoices raised per day over the last 30 days (including days when no 
invoices were raised)

SELECT dt ,count(InvoiceDate) as Invoices 
FROM
(SELECT [Num]+Date()-30 AS dt
FROM tblNumbers
WHERE ((([Num]+Date()-30)<=Date()))) d
LEFT JOIN tblInvoices on tblInvoices.Invoicedate = d.dt
GROUP BY dt


or you can find gaps in  a series, such as missing invoices starting from  Invoice number 
12000.

Select inum from
(SELECT [Num]+12000 AS INum
FROM tblNumbers) n
LEFT JOIN tblInvoices i on i.InvoiceNum = n.Inum
where isnull(invoiceNum) and iNum <= dmax("InvoiceNum","tblINvoices")

There are many other ways to use the table once you grasp the concept.