Update query with date range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason Reynolds - 20 Mar 2006 18:35 GMT

Hello everyone..

I am attempting to automate the generation of reporting data that is stored
in an access databse. Normally this data would be generated by the
application, but in this situation, I need to fake the data for demo
purposes.

I have a table called AntiSpamUser that holds a number of records petaining
to filtered spam. To manipulate the access database, i wanted to use an
update query that prompts for a date range, and then randomly populates the
TimePeriod field in the AntiSpamUser stats table with dates that fall within
the range I specified. The data in the TimePeriodField is simply a
mm/dd/yyyy and is not indexed.

Any ideas on how to pull that off?

TIA

mrjasonreynolds(AT)gmail.com

Reply to this Message

Michel Walsh - 21 Mar 2006 14:14 GMT

Hi,

You can use a driver table to generate N records. A driver table, in this
case, is just a table with one column, say iota, filled with values from 0
to 999, as example (if N < 1000).

SELECT iota FROM iotas WHERE iota <= N

would generated N different records.

SELECT RND(iota) FROM iotas WHERE iota <= N

would generate N random values between 0 and 1.

SELECT StartingDate + (EndingDate-StartingDate)*RND(iota) FROM iotas WHERE
iota <= N

would generate N random date_time values within the interval [ StartingDate,
EndingDate ]

Hoping it may help,
Vanderghast, Access MVP

> Hello everyone..
>
[quoted text clipped - 15 lines]
>
> mrjasonreynolds(AT)gmail.com

Reply to this Message