If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create records using defaults
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 - |
#2
|
|||
|
|||
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 - |
#3
|
|||
|
|||
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" wrote: 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 - |
#4
|
|||
|
|||
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/conrad...essjunkie.html "Alex" wrote in message ... 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" wrote: 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 - |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Automatic create records on table | Ray | General Discussion | 2 | April 17th, 2005 09:23 AM |
How to create Query in Access that will cull out records that are. | Database Weary | Running & Setting Up Queries | 1 | December 14th, 2004 08:23 PM |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 07:25 PM |
Create administrator to approve added records | Kendra | Setting Up & Running Reports | 0 | August 25th, 2004 10:04 PM |
Create a new temp table and populate with n blank records | Gareth Mercer | Database Design | 3 | May 19th, 2004 01:38 PM |