[AccessD] A2k: Creating an Array from Start and End Numbers

[Darren]

Stuart - Having said that,
I can see uses for building arrays of say..all the coming 6 Tuesdays. In
this same DB. 
Thanks I'll work on it - nice. Expect emails :-)

D

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Darren
Sent: Wednesday, 25 March 2015 12:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2k: Creating an Array from Start and End Numbers

Howdy

That's even more clever than I had originally planned. Nice.
This is for a community support mob. They provide all sorts of assistance to
the local community. It's a charity - so ProBono work for me.
These guys deliver Govt EAPA vouchers, local business vouchers, internal
services vouchers and so on to their 'clients'.
Apart from the EAPA Vouchers there is no commonality with the number or
sequences (Nothing predictable at least) So just being able to add a start
and end sequence for the 'voucher type'
(And there are types within the types) is enough - as per Gustav's solution.
To be frank I think their poor data entry (Mostly volunteers and mostly
older folk) will probably even make Gustav's solution moot in a short space
of time.
I am building it - Hoping they will come (To the better data entry party)
Many thanks Darren

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Tuesday, 24 March 2015 4:11 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2k: Creating an Array from Start and End Numbers

Darren,

You generaly use a number table for dynamic data, you just use offsets.

Create a number table containing the numbers 0 to 999 as an example.

If you need the next 10 numbers starting from 34567  you would just 

"Select (num + 34567)  from tblNumbers where num < 10" 

The beauty of using such as table is that you can use it to generate any
sequence you like for numbers or dates:

"Select (num * 2) from tblNumbers where num between 1 and 20"  will give you
the first 20 even numbers (2,4,6....40)

If today is Tuesday then , "Select (Date() + (num * 7) from tblNumbers where
num < 10" will give you an array of the next 10 Tuesdays including today.
  
etc, etc

One good way to use it is  as the left side of a join in order show every
number/date in a range whether or not there are matching records in the
table on the right side of the join.


Once you have a number table in your system, you will find more and more
uses for it. :)


--
Stuart


On 24 Mar 2015 at 14:18, Darren wrote:

> Stuart - This is for storing 'voucher numbers'. 
> Users will add the first and last numbers in a sequence and now using
> Gustav's clever code I can present a list of available voucher numbers
> using these first and last numbers.
> 
> These will never be static - always on the move so a fixed table won't
> suffice but it's what I had in mind first, until the client explained
> how their 'voucher' system works.
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com