A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create records using defaults



 
 
Thread Tools Display Modes
  #1  
Old September 12th, 2005, 09:31 PM
Alex
external usenet poster
 
Posts: n/a
Default 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  
Old September 12th, 2005, 10:28 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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  
Old September 13th, 2005, 02:52 PM
Alex
external usenet poster
 
Posts: n/a
Default

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  
Old September 13th, 2005, 07:55 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 09:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.