using an update query to create records within a date period

[Guest]

I plan our events using Access. At the moment when I'm given the dates for
an event e.g. 1st July to 17th July I have to manually create a new record
for each day.

Is there any way in an update query I could type in my constants (event
type, event name, event location) and the start date and the end date and
create records for all of the days between the start and end date?

If there is, I have no idea what to set the "update to" and "criteria" to?
Any help would be greatly appreciated.

JAMES

 

[Jamie Collins]

I plan our events using Access. At the moment when I'm given the dates for
an event e.g. 1st July to 17th July I have to manually create a new record
for each day.

"Why should I consider using an auxiliary calendar table?"
http://www.aspfaq.com/show.asp?id=2519

"A calendar table can make it much easier to develop solutions around
any business model which involves dates. Last I checked, this
encompasses pretty much any business model you can think of, to some
degree. Constant problems that end up requiring verbose, complicated
and inefficient methods include the following questions: How many
business days between x and y?"

Jamie.

--

 

[Guest]

James,

Similiar to the auxilliary dates table, but with less overhead and bloat, I
use another technique.

I have a table (tbl_Numbers) which contains a single field (numValues), and
only has 10 records (0-9). I use this table to create a query (qry_Numbers)
that contains the values from 0 to 1000, but you could easily make that
number much larger. That query looks like:

SELECT Hundreds.numValues * 100 + Tens.numValues * 10 + Ones.numValue
as myValues
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

I can then use this table to generate dates lile you want. Something like:

Insert into SomeTable (EventID, DateField)
SELECT [EventID], [StartDate] + myValues
FROM qry_Numbers
WHERE [StartDate] + myValues <= [EndDate]

Realistically, I would not create a parameter query like this, but it serves
as an example. I would generally store this information, in the format you
have described (EventType, EventName, EventLocation, StartDate, EndDate) in
an Events table. Then, if I really felt I needed another table to store date
information, on a by day basis, I would use a query similiar to the one above
to insert the dates into that table.

HTH
Dale

 

[Jamie Collins]

Similiar to the auxilliary dates table, but with less overhead and bloat, I
use another technique.

I have a table (tbl_Numbers) which contains a single field (numValues), and
only has 10 records (0-9). I use this table to create a query (qry_Numbers)
that contains the values from 0 to 1000, but you could easily make that
number much larger. That query looks like:

SELECT Hundreds.numValues * 100 + Tens.numValues * 10 + Ones.numValue
as myValues
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

Methinks you worry too much about bloat <g>.

My Calendar table has circa 11K rows to cover three decades, hardly
large. My Sequence table is 100K rows out of necessity. Hey, you could
generate your Sequence table on the fly using any existing table e.g.

SELECT Units.nbr + Tens.nbr + Hundreds.nbr + Thousands.nbr +
TenThousands.nbr AS seq
FROM (SELECT nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION
ALL SELECT DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM
Customers UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT
DISTINCT 4 FROM Calendar UNION ALL SELECT DISTINCT 5 FROM Customers
UNION ALL SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7
FROM Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL
SELECT DISTINCT 9 FROM Calendar) AS Digits) AS Units, (SELECT nbr * 10
AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Customers
UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Calendar UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL SELECT
DISTINCT 9 FROM Customers) AS Digits) AS Tens, (SELECT nbr * 100 AS
nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Calendar
UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Customers UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Calendar UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL SELECT
DISTINCT 9 FROM Customers) AS Digits ) AS Hundreds, (SELECT nbr * 1000
AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Customers UNION ALL SELECT
DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM Customers
UNION ALL SELECT DISTINCT 3 FROM Calendar UNION ALL SELECT DISTINCT 4
FROM Customers UNION ALL SELECT DISTINCT 5 FROM Customers UNION ALL
SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7 FROM
Customers UNION ALL SELECT DISTINCT 8 FROM Calendar UNION ALL SELECT
DISTINCT 9 FROM Customers ) AS Digits) AS Thousands, (SELECT nbr *
10000 AS nbr FROM (SELECT DISTINCT 0 AS nbr FROM Calendar UNION ALL
SELECT DISTINCT 1 FROM Customers UNION ALL SELECT DISTINCT 2 FROM
Customers UNION ALL SELECT DISTINCT 3 FROM Customers UNION ALL SELECT
DISTINCT 4 FROM Customers UNION ALL SELECT DISTINCT 5 FROM Calendar
UNION ALL SELECT DISTINCT 6 FROM Customers UNION ALL SELECT DISTINCT 7
FROM Customers UNION ALL SELECT DISTINCT 8 FROM Customers UNION ALL
SELECT DISTINCT 9 FROM Customers) AS Digits) AS TenThousands;

Makes queries that utilise it a little harder to read, though <g>. As
for overhead, doesn't it take more to generate the set on the fly
rather than generate one and store in an auxilliary table?

A Calendar table has other advantages too e.g. differentiate work
days, holidays, etc.

Jamie.

--

 

[Guest]

Jamie and Dale,

Thank you for your help. However I am brand new to Access and have no idea
how to create a table using code, anything beyond using the "create table
wizard" and I'm lost. SO any tips on how to start from the beginning would be
greatly appreciated.

Many thanks

JAMES

 

[Jamie Collins]

Thank you for your help. However I am brand new to Access and have no idea
how to create a table using code, anything beyond using the "create table
wizard" and I'm lost. SO any tips on how to start from the beginning would be
greatly appreciated.

To clarify, disregarding code, the general approach I'm proposing (and
AFAIK so is Dale Fye) is to ditch your original idea to "create
records for all of the days between the start and end date" and
instead create a permanent auxiliary table (Calendar or Sequence or
similar) to enable you to create those dates 'on the fly' in a query.
You now need to figure out how to implement this and you've got a
couple of examples to get you started.

If you post in the 'queries' group you should have a reasonable
expectation of a reply using the SQL language; sure, some people post
how to use the clickety-click query builder thing but I imagine (for I
am not one of them) it becomes tedious do describe all the clickety-
clicks for anything other than the simplest of queries. The conclusion
I'd come to is to for you to learn SQL, then we can all speak the same
language Shuld you wish to take this road, an online tutorial (e.g.
http://www.sqlcourse.com/) is IMO an excellent first step.

Jamie.

--

 

[Guest]

thanks Jamie, definately going to try that online course.

Being the youngest (and newest) in the office, apparently I come with a
complete understanding of code becuase I'm the youngest!!!!!! It's been a
steep learning curve for everyone involved.

Speak to you soon.

JAMES