[AccessD] List all tables and their fields

[Arthur Fuller]

Nice, Stuart. I've never done that before, but I'll give it a go.

On Fri, Mar 24, 2023 at 6:37 AM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> 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.
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Arthur