Create records using defaults

[Alex]

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));

Thank you very much -

 

[Lynn Trapp]

Alex,
Why are you creating a new table every month? You should be able to simply
add an appropriate date field to your table and, then, query the table for
all records in any given month.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Alex]

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

 

[Lynn Trapp]

You don't have to use a changing date field, but a static one. Then create a
query for any given month -- past or present -- using the
Month(YourDatefield) function as a criteria.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html