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/conrad...essjunkie.html



"Alex" wrote in message
...
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()),Mon th(Date())-2,1)))
OR
(((GoalsMain.GoalDate) Is Null));

Thank you very much -