Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Subquery Returns More Than 1 Value

    I know that this has been asked before but I cannot find a specific answer to my problem.

    What I have is an Access frontend that has a datasheet that records contact between a teacher and a parent of a student using a view. When a teacher uses it they select their name from a drop down menu that holds their last and first name. After they select it it only shows their last name in the datasheet. Due to multiple base tables I created a trigger to allow me to update the view.

    Now, when I try to send the data to SQL so that it may be saved, it saves fine, however the teacher's name is blank. Makes sense because that is being held in tbl_Contact_Log as an int not the tbl_personnel table which holds the actual last name.

    So I edited my trigger to account for this and used a subquery with a WHERE clause being WHERE dbo.tbl_personnel.Last = inserted.Last. This works for the most part but the problem is that we have multiple teachers with the same last name. At this point the record cannot be saved due to it not knowing which teacher I would like.

    For a single student I would base everything off of the student ID but any student can have any number of records in this field. When I base it off of the student ID it still does not work the way that I need it to.

    Here is the trigger as it stands.

    Code:
    ALTER TRIGGER [dbo].[trig_Contact_Log_Insert] 
       ON  [dbo].[q_Contact_Log]
       INSTEAD OF INSERT
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for trigger here
    	INSERT INTO dbo.tbl_Contact_Log(emisid, date, Comments, Form_Of_Contact, [4Wk_Average], Current_Wkpts, Goal_Met, Cause, Student_Demeanor, Student_Personal_Plan, ADA_Recommend, staff_mem)
    	SELECT emisid, date, Comments, Form_Of_Contact, [4Wk_Average], Current_Wkpts, Goal_Met, Cause, Student_Demeanor, Student_Personal_Plan, ADA_Recommend, (SELECT persID FROM dbo.tbl_personnel, inserted WHERE dbo.tbl_personnel.Last = inserted.Last)
    	FROM inserted
    
    
    
    END
    And this is the view

    Code:
    SELECT     TOP (100) PERCENT dbo.tbl_Contact_Log.emisid, dbo.tbl_Contact_Log.date, dbo.tbl_Contact_Log.Comments, dbo.tbl_Contact_Log.Form_Of_Contact, 
                          dbo.tbl_Contact_Log.contactid, dbo.tbl_Contact_Log.[4Wk_Average], dbo.tbl_Contact_Log.Current_Wkpts, dbo.tbl_Contact_Log.Goal_Met, dbo.tbl_Contact_Log.Cause, 
                          dbo.tbl_Contact_Log.Student_Demeanor, dbo.tbl_Contact_Log.Student_Personal_Plan, dbo.tbl_Contact_Log.ADA_Recommend, dbo.tbl_personnel.Last,
                          dbo.tbl_Contact_Log.staff_mem
    FROM         dbo.tbl_Contact_Log FULL OUTER JOIN
                          dbo.tbl_personnel ON dbo.tbl_Contact_Log.staff_mem = dbo.tbl_personnel.persID
    ORDER BY dbo.tbl_Contact_Log.emisid
    Thanks

  2. #2
    Join Date
    Jul 2009
    Posts
    47
    Also I just noticed that I can sort of update a record that already exists. If I click to change the teacher, the last name changes in the datasheet even if there is more than one person with that last name. However for some reason it keeps the first name and changes the first teacher's name to the second in tbl_personnel.

    For example if I had two teachers John Doe and Mary Gates. Now, Mary accidentally selected John's name from the list so the field now says Doe. Mary clicks the dropdown again and selects Gates, Mary. The field correctly reports Gates, but it is Gates, John not Gates, Mary. In the personnel table it changes to Gates, John but everything else stays the same.

    Any ideas on this problem too?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is in your nested subquery, which is returning more than one record:
    Code:
    SELECT persID FROM dbo.tbl_personnel, inserted WHERE dbo.tbl_personnel.Last = inserted.Last
    You have more then one person with the same last name, which is not surprising. You will need to include some logic to ensure that only one record is returned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2009
    Posts
    47
    Right, I was able to get that myself. I thought about setting the constraint as WHERE tbl_Contact_Log.contactid = inserted.contactid but contactid is a self incrementing identity column.

    Any ideas for what kind of logic I can use to get what I want?

    Thanks

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what do you have in your tables/view that can help you identify which of the last names you really want? This is your data, we do not know it, nor understand the business rules that would ensure you are getting the correct persID.
    Dave

  6. #6
    Join Date
    Jul 2009
    Posts
    47
    That's the problem, persID is the unique identifier. Whenever I need the data I will just use the persID to get the last name or first name. I have never gone backwards from last name to persID which is where my trouble is coming from.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    persID is your surrogate key. But each table should also have a natural key as well. A combination of columns inherent in the data that uniquely identify each record. Obviously, LastName alone is woefully inadequate for such a purpose. You could possibly get away with a combination of FirstName/LastName, but better would be Social Security number or some other ID. In any case, whatever you choose should be enforced as a unique constraint on the actual table. Otherwise, you code logic is guaranteed to fail at some time, with unexpected consequences.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2009
    Posts
    47
    OK that makes a lot of sense. In tbl_personnel I have Employee_id also which is another ID column that doesn't allow nulls. It seems that would work for what I need but how would I do that in the WHERE clause (or where it needs to be) because the inserted (table?) would not have the Employee_ID due to the fact that it is not being inserted in this command.

    Thanks for both of your help.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Is it in the view? If so, you can use Employee_id as you used the last name. If not, you would need to find someway of joining your view to the table that contains th eappropriate Employee_id.
    Dave

  10. #10
    Join Date
    Jul 2009
    Posts
    47
    Well I at least got it working to do what I need for now. I used Access's row source parameter to pull through the persID, last, first but hide the persID. This way it is getting pulled through and sent back as I need it but only displays the last, first in the list. I will go back later and correct it so that it all happens on the SQL server, but with deadlines approaching it will do for now.

    Thanks for your help.

Posting Permissions

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