Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Insert New Record

    Hi all I have a question, what if I created INSERT STATEMETN that would insert a new field everytime the user enter data into it. I was thinking this would make data entry easier for the user.
    for instance when a user needs to enter hours for a client

    so instead of them enter the same data over and over again for different clients how about an insert statement that would do that. I am unsure as to hwo to do that though.

    Code:
    CREATE PROCEDURE InsertHours
    as
    INSERT INTO  (TanfActivity_tbl.[Earned hours], TanfActivity_tbl.[Purpose of Contact], TanfActivity_tbl.[Type of Contact], TanfActivity_tbl.[Catagory for hours], 
                          TanfActivity_tbl.[Services Covered], TanfActivity_tbl.[State Catagory], TanfActivity_tbl.[State Services Covered])
    
    
    SELECT     People_tbls.[Parent First Name], People_tbls.[Parent Last Name], TanfActivity_tbl.[Type of Contact], TanfActivity_tbl.[Purpose of Contact], 
                          TanfActivity_tbl.[Referral Date], TanfActivity_tbl.[Earned hours], TanfActivity_tbl.[Catagory for hours], TanfActivity_tbl.[Services Covered], 
                          TanfActivity_tbl.[State Services Covered], TanfActivity_tbl.[State Catagory]
    FROM         TanfActivity_tbl INNER JOIN
                          People_tbls ON TanfActivity_tbl.[Parent ID] = People_tbls.[Parent ID]

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by desireemm
    what if I created INSERT STATEMETN that would insert a new field everytime the user enter data into it. I was thinking this would make data entry easier for the user.
    for instance when a user needs to enter hours for a client

    so instead of them enter the same data over and over again for different clients how about an insert statement that would do that. I am unsure as to hwo to do that though.
    I'm afraid I don't know what you mean, and you haven't explained what the code does or does not do.

    An INSERT statement does not "insert a new field". Insert statements insert 0 to many rows.
    An ALTER TABLE statement can add new columns. If you want users to do this then you are almost certainly on the wrong track.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I guess what I want to do is an Update. See lets say the user has to to put in the same data but for different records. What I wanted to do is create a procedure that would update or insert a new with the information and append it to the TanifActiivty Tabl which has a one to many relationship with the parent table.
    So if the user does a search for the Last Name or First name Or by the Parent ID (which) and in the [Catagory for hours] and the Purpose of contact and the
    [Earned hours] , [Catagory for hours], [Services Covered] then they would go to the next record and just append the same data into those fields. I am just trying to save time on allot of data entry. I hope that makes sense


    CREATE TABLE [dbo].[TanfActivity_tbl](

    [ID] [int]

    IDENTITY(1,1) NOT NULL,

    [Contact Date] [datetime]

    NULL,

    [Parent ID] [nvarchar]

    (50) NULL,

    [Type of Contact] [nvarchar]

    (50) NULL,

    [Purpose of Contact] [nvarchar]

    (max) NULL,

    [Referral Date] [datetime]

    NULL,

    [Earned hours] [float]

    NULL,

    [Catagory for hours] [nvarchar]

    (255) NULL,

    [Services Covered] [nvarchar]

    (255) NULL,

    [State Catagory] [nvarchar]

    (255) NULL,

    [State Services Covered] [nvarchar]

    (255) NULL,

    [Month] [nvarchar]

    (50) NULL,

    [Week] [nvarchar]

    (50) NULL,

    [DateReferredBack] [datetime]

    NULL,

    [DateOfCall] [nvarchar]

    (50) NULL,

    [Notes] [ntext]

    NULL,

    [DateofPostCard] [datetime]

    NULL,

    [SCAIRCaseWorker] [nvarchar]

    (50) NULL,



    CONSTRAINT [PK_StateActivity_tbl] PRIMARY KEY CLUSTERED

    (

    [ID]

    ASC

    )



    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )



    ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



    which is
    has a one to many relationship with the People_tbls

    CREATE



    TABLE [dbo].[People_tbls](

    [Parent ID] [nvarchar]

    (50) NOT NULL,

    [Family ID] [nvarchar]

    (50) NULL,

    [Date of Referral] [nvarchar]

    (50) NULL,

    [Parent First Name] [nvarchar]

    (50) NULL,

    [Parent Last Name] [nvarchar]

    (50) NULL,

    [Parent SS#] [nvarchar]

    (50) NULL,

    [Telephone #] [nvarchar]

    (50) NULL,

    [Message #] [nvarchar]

    (50) NULL,

    [Address] [nvarchar]

    (50) NULL,

    [City] [nvarchar]

    (50) NULL,

    [State] [nvarchar]

    (50) NULL,

    [Zip] [nvarchar]

    (50) NULL,

    [E Mail Address] [nvarchar]

    (50) NULL,

    [Tribal Affiliation] [nvarchar]

    (50) NULL,

    [Event ID] [nvarchar]

    (50) NULL,

    [TANF staff making Referral] [nvarchar]

    (50) NULL,

    [Type Participant] [nvarchar]

    (50) NULL,

    [Weekly Hrs] [int]

    NULL,

    [Mo Hours] [int]

    NULL,

    [Special Instrution] [nvarchar]

    (max) NULL,

    [Referral Status] [nvarchar]

    (255) NULL,

    [Service Area] [nvarchar]

    (255) NULL,

    [ScannedDocuments] [nvarchar]

    (50) NULL,

    [PeopleID] [int]

    IDENTITY(1,1) NOT NULL,

    [DOB] [datetime]

    NULL,

    [Age] [numeric]

    (18, 0) NULL,

    [____] [nvarchar]

    (50) NULL,



    CONSTRAINT [PK_People_tbls] PRIMARY KEY CLUSTERED

    (

    [Parent ID]

    ASC

    )



    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )



    ON [PRIMARY]
    Last edited by desireemm; 08-27-09 at 04:24.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So - a user is viewing a record. They want to create a new record, using most of the data in the record they are viewing, just changing the odd field here and there - correct?

    If so, this is a (trivial) problem for the UI not the database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    no the users adds a record types in the fields and instead of having to retype (things like the hours, Date, and notes) over and over again for each record to just have a statement that will insert it for them rather them having to keep typing it in

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by desireemm
    no the users adds a record types in the fields and instead of having to retype (things like the hours, Date, and notes) over and over again for each record to just have a statement that will insert it for them rather them having to keep typing it in
    Yes, but that facility is to do with the UI not the database. Unless you can explain it more clearly, perhaps by using an example of some rows, I can't say any more.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    aparently its a ui thing that works in access. its a short cut key

    Insert the data from the same field in the previous record CTRL+'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •