Update query with date range

  • Thread starter Thread starter Jason Reynolds
  • Start date Start date
J

Jason Reynolds

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
 
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
 
Back
Top