Enter Dates into Table with SQL

[Guest]

Based on a form's textbox, forms!theform!thetextbox, which is a short date
and is always the the 1st day of a selected month/year (i.e., it can be
7/1/2006, 10/1/2003, 2/1/1999...you get the idea), I want to fill out a table
field, thetable.thefield (the only field in the table), with 12 records
pertaining the the selected month and the previous 11 months. For example,
if forms!theform!thetextbox = 1/1/2007, I want the table to contain the
following 12 records.

2/1/2006
3/1/2006
4/1/2006
5/1/2006
6/1/2006
7/1/2006
8/1/2006
9/1/2006
10/1/2006
11/1/2006
12/1/2006
1/1/2007

I can do this in VBA using a loop and the openrecordset command, but I would
rather do it in SQL if possible. Thanks for any help.

 

[Jeff Boyce]

It sounds a little like you are creating "dummy" records, to be filled
later. If that's the case, be aware that it is rarely necessary to do so,
and can cause confusion.

You've described "how" you are trying to accomplish something. If you
describe a bit more about what you hope to accomplish by having these
entered, the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

So I understand a bit better now what situation you are working with, but
you are intent on filling in the missing months. Why? What will having all
the months, real or dummy, do to help you in your (still unclear) business
need?

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

You might want to take a look at Crosstab Queries and Crosstab Reports.

You might also look into the property called Running Sums in textboxes used
in reports.

It is rarely necessary to create table entries for "none" just to have
"none" show up in a report. In most instances, you can accomplish this via
queries, and NOT end up with dummy records that aren't real, just
placeholders.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am sorry for not laying out the whole situation at first. I just didn't
want to clutter the issue. Ultimately, the result will be shown in a
report,
and the report has to show rolling total data for each of the 12 selected
months even if a particular month contributes zero to the rolling total.
For
example, using my table from below, the report would be:

Month SumofField Rolling Total
2/1/2006 0 0
3/1/2006 0 0
4/1/2006 10 10
5/1/2006 0 10
6/1/2006 9 19
7/1/2006 10 29
8/1/2006 0 29
9/1/2006 8 37
10/1/2006 0 37
11/1/2006 0 37
12/1/2006 7.5 44.5
1/1/2007 11 55.5

Note that the rolling total calculation has already been performed at this
point. The rolling total field is one of the 'other fields' mentioned
below.
This brings up a angle I haven't thought of yet. Can I make the query
that
is doing the rolling total calculation interject records with zero values?
The SQL from that query is copied below.

SELECT Q.Month, Q.Total_Amount_Flared, Q.Pollutant, Q.Total_tpy,
Q.SumofUnplanned, (SELECT Sum(Nz([total_tpy],0)) FROM
t_EmissionTotalsbyMonth_Last23Months AS Q1 WHERE ((q1.pollutant =
q.pollutant) and ([q1].[month] between Q.month and
dateadd("M",-11,q.[month])))) AS RunningSumEmiss, (SELECT
Sum(Nz([Total_Amount_Flared],0)) FROM t_EmissionTotalsbyMonth_Last23Months
AS
Q1 WHERE ((q1.pollutant = q.pollutant) and ([q1].[month] between Q.month
and
dateadd("M",-11,q.[month])))) AS RunningSumFlared
FROM t_EmissionTotalsbyMonth_Last23Months AS Q;

Does this help?

So I understand a bit better now what situation you are working with, but
you are intent on filling in the missing months. Why? What will having
all
the months, real or dummy, do to help you in your (still unclear)
business
need?

Thanks

Regards

Jeff Boyce
Microsoft Office/Access MVP