Create record using defaults

[Alex]

This is my second post for this - thank you

Each month I create a new table using the SQL below, where the records are
just copied from the GoalsMain table, except that the NewDate changes each
month. This works great unless I've added a new record in the
RecordLocatorMain table. What I need to happen is for that new record,
that won't have any previous data in the GoalsMain table, to be created in
the
GoalsNew table using the GoalsMain defaults. Is there a way to do this?

SELECT RecordLocatorMain.RCDLOC, RecordLocatorMain.DeskCode,
DateSerial(Year(Date()),Month(Date())-1,1) AS NewDate,
GoalsMain.DayDemandBT,
GoalsMain.InventoryTurnsNormal, GoalsMain.InventoryTurnsStretch,
GoalsMain.WorkloadNormal, GoalsMain.WorkloadStretch INTO GoalsNew
FROM GoalsMain RIGHT JOIN RecordLocatorMain ON (GoalsMain.DeskCode =
RecordLocatorMain.DeskCode) AND (GoalsMain.RCDLOC =
RecordLocatorMain.RCDLOC)
WHERE (((GoalsMain.GoalDate)=DateSerial(Year(Date()),Month(Date())-2,1)))
OR (((GoalsMain.GoalDate) Is Null));


Someone posted and asked me why I'm doing this. Because:
I have a table that tracks monthly goals for Deskcode/RCDLOC. In many
reports, I need to look back at past goals so I can't have a date field that
changes; I need a record for Jan/05 goals, Feb/05 goals, etc. . . I create a
temporary GoalsNew table that inserts the new date and copies the most recent
goals from the GoalMain table. If, however, there has been a new
DeskCode/RCDLOC added to the RecordLocatorMain table, the goals will be null.
So, I'd like those null goals for the new RCLOC to use the defaults in the
GoalsMain table. Then I append the GoalsNew table to the GoalsMain table.
Any ideas?

GoalsMaintbl -
Field: Deskcode
Field: RCDLOC
(combined primary key)
Field: GoalDate
Field: DayDemandBT
Field: InventoryTurnsNormal
Field: InventoryTurnsStretch
Field: WorkloadNormal
Field: WorkloadStretch

RecordLocatorMain:
Field: DeskCode
Field: RCDLOC
(combined primary key)
and some additional fields not being used for this query

Thank you for your help -

 

[Michel Walsh]

Hi,


The exact syntax depends if you INSERT INTO or UPDATE after having append
some records. If you append records, you can use something like:

INSERT INTO tableName ( f1, f2)
SELECT Nz( fieldName1, defaultValueForField1), Nz(fieldName2,
defaultValueForField2)
FROM myTable
WHERE ....

would insert fieldName1 under f1, and fieldName2 under f2, but if they have
a NULL value, the NULL would be replaced by the "specified" value under the
Nz.



Hoping it may help,
Vanderghast, Access MVP