SQL Questions

[avagodro]

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.

 

[JerryKlmns]

The following sql statements will add to tbl2 for every record in tbl1 that doesnt exist on tbl2

A] one record for the first day of the previous month.

INSERT INTO tbl2 ( RecordRef, PostageID )
SELECT tbl1.RecordID, DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) AS Expr1
FROM tbl2 RIGHT JOIN tbl1 ON tbl2.RecordRef = tbl1.RecordID
WHERE (((tbl2.RecordRef) Is Null));


B] one record for the first day of the previous month if there is also no entry during the period of the previous month

INSERT INTO tbl2 ( RecordRef, PostageID )
SELECT tbl1.RecordID, DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) AS Expr1
FROM tbl2 RIGHT JOIN tbl1 ON tbl2.RecordRef = tbl1.RecordID
WHERE (((tbl2.RecordRef) Is Null)) OR (((tbl2.RecordRef) Is Not Null) AND ((tbl2.PostageID) Not Between DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) And DateSerial(Year(Now()),Month(Now()),0)));

 

[avagodro]

I tested this and I only have 4 records in tbl1, but each time I check the SQL over, it is wanting to append 177 records, which is the number of records currently in tbl2

 

[JerryKlmns]

Hmmmmm.........

INSERT INTO tbl2 ( RecordRef, PostageID )
SELECT tbl1.RecordID, DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1))
FROM tbl2 RIGHT JOIN tbl1 ON tbl2.RecordRef = tbl1.RecordID
WHERE (((tbl2.RecordRef) Is Null)) OR (((tbl2.RecordRef)=(SELECT tbl2.RecordRef FROM tbl2 GROUP BY tbl2.RecordRef HAVING (((Max(tbl2.PostageID)) Not Between DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) And DateSerial(Year(Now()),Month(Now()),0)))));

 

[avagodro]

Thank you. You gave me some ideas to work with. The SQL that worked was:

INSERT INTO tbl2 ( RecordRef, PostageDate )
SELECT tbl1.RecordID, DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)) AS Expr1
FROM tbl2 RIGHT JOIN tbl1 ON tbl2.RecordRef = tbl1.RecordID
GROUP BY tbl1.RecordID;

 

[danvlas]

"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."

Force it to have a 'null' record obtained through a left join between the primary table and a query retrieving the records from the related table that satisfy the condition.

Better than inserting fake records in the database.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html