SQL Question

[spokane29]

I have a database that I have 2 tables using a One-Many relationship. I have
these in a form and subform, linked by the Primary Key.
What I need to be able to do is on the first of every month is to
automatically create a record for the 1st day of the previous month for each
of the related records. This can be done with either code/query that will
update ALL linked records (preferable), or each individually.
The reason that I need to do this is that for a report that I have I need to
show totals for every record, even if the total is 0. With no record present,

the report generates an error since it is trying to pull information via a
Crosstab Query but the requested data does not exist.
My primary Keys and references are:
tbl1 = RecordID
tbl2 = RecordRef and PostageID

RecordID and RecordRef are linked via the One-Many relationship. PostageID
is the unique record for each of the records in tbl2.
Doing the basics of this is fairly straight forward. The problem that I am
having is that I have a table with subrecords. I need one date appended to
the primary records.
For example:
Table1 | Table2
Record1 create 1 date under this record, and
Record2 create 1 date under this record, and
Record3 create 1 date under this record.

Is this a little more clear? I have been trying several variation, and have
been coming up blank. I had some code suggested to me, but I am not getting
it to work and haven't been able to contact the person that suggested it.
The code is:

If IsNull(Me.txtDate) Then Exit Sub
Dim mDay As Integer, mMonth As Integer, mYear As Integer, s As String

mDay = Day(Me.txtDate)
mMonth = Month(Me.txtDate)
mYear = Year(Me.txtDate)

For mDay = 1 To Day(DateSerial(mYear, mMonth + 1, 0))
s = "INSERT INTO [tblMiscPostage](PostageDate) SELECT #" _
& DateSerial(mYear, mMonth, mDay) & "#;"
Debug.Print s
CurrentDb.Execute s
Next mDay

CurrentDb.TableDefs.Refresh
DoEvents

Me.Requery

Any input or suggestions would be appreciated.

 

[John Vinson]

I have a database that I have 2 tables using a One-Many relationship. I have
these in a form and subform, linked by the Primary Key.
What I need to be able to do is on the first of every month is to
automatically create a record for the 1st day of the previous month for each
of the related records.

Ummm....

WHY?

Creating empty "placeholder" records, with no information (other than
a date) is very rarely a good idea. What will be done with this
non-information?

This can be done with either code/query that will
update ALL linked records (preferable), or each individually.
The reason that I need to do this is that for a report that I have I need to
show totals for every record, even if the total is 0. With no record present,
the report generates an error since it is trying to pull information via a
Crosstab Query but the requested data does not exist.

Fix the query, don't wreck your table. Could you post the SQL of the
crosstab query? Almost certainly the query can be fixed to give you
your zeros with or without storing dummy records.



John W. Vinson[MVP]