Page 6 of 6 FirstFirst 123456
Results 76 to 88 of 88
  1. #76
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55

    Hey June7, the post above is the subquery code I gave to add to the existing query that selects all students who play the guitar, but its not correct. Can you help me with this part? Thanks.

  2. #77
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you need a subquery?


    SELECT tblContract.LessonType, tblContract.StudentID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.MonthlyRentalCost
    FROM tblContract
    WHERE (((tblContract.LessonType)="Guitar") AND ((tblContract.MonthlyRentalCost)<>0))
    ORDER BY tblContract.LessonType, tblContract.ContractStartDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #78
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Hey, thanks for dropping by . The assignment requires a subquery so that is why I needed it. I'm going to try the code out to see if it works, and if I got any questions I will let you know later on tonight.

  4. #79
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need the Guitar criteria somewhere, maybe the subquery:

    SELECT tblContract.LessonType, tblContract.StudentID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.MonthlyRentalCost
    FROM tblContract
    WHERE (((tblContract.MonthlyRentalCost)<>0))
    AND LessonType IN
    (SELECT LessonType
    FROM tblContract
    WHERE LessonType="Guitar")
    ORDER BY tblContract.LessonType, tblContract.ContractStartDate;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #80
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Hey June7, I had to go back to lab 9 because I had some errors with numbers 6 through 8. Here is the lab assignment: http://books.google.com/books?id=udW...eacher&f=false

    I thought I had the append query and update query fixed, but my teacher said I had to do it over again. Also, what would be the correct way of doing the delete query because I put this down in my criteria underneath the ContractEndDate field value within the tblSpecialLesson (this is all within Design view of the qryLessonsByTeacher). I put this down as my criteria <#7/1/2013# but when I ran it I just got a blank screen. Can you help me with fixing these 3 types of queries for this assignment? I need to get this done as soon as possible because its due this Thursday and I want to make sure I dont have anything else that is incorrect. I'll post my lab below. Thanks and hope to hear from you soon.
    Attached Files Attached Files

  6. #81
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't see any Append or Update queries in your db. Cannot analyze something that isn't there. Not interested in reading the book. What is wrong with the results? Post the query statements for analysis. Go back to the posts that dealt with those problems and review. I remember my tests worked.

    If you are using the same query object with modifications to perform these actions, that is part of the issue. Do a separate query object for each, built from scratch.

    Since you are applying the date criteria to the field from tblSpecialLesson and no records in tblSpecialLesson meet that criteria, the query is of course empty. Why do you have ContractEndDate fields in both tblContract and tblSpecialLesson? Why is there no join between the two tables? Delete queries delete entire records therefore there is no need to list a bunch of fields, just the criteria fields are enough. Try:

    DELETE LessonType, ContractEndDate FROM tblContract
    WHERE (((tblContract.LessonType)="Cello" Or (tblContract.LessonType)="Piano" Or (tblContract.LessonType)="Violin" Or (tblContract.LessonType)="Voice") AND ((tblContract.ContractEndDate)<#7/1/2013#));
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #82
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    The reason why you dont see any append or update queries in the db is because in the instructions it tells you not to save after you make changes to the TblSpecialLesson.

  8. #83
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I suggest you save them. Doesn't hurt anything and can always delete later.

    How is your instructor evaluating if there is nothing to see? Right now I don't have your effort to evaluate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #84
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Hey, I'm having a problem here with lab 9 still. Here is the code for qryLessonsByTeacher in SQL view,

    SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost, tblSpecialLesson.LastName, tblSpecialLesson.StudentID, tblSpecialLesson.ContractEndDate, tblSpecialLesson.LessonType, tblSpecialLesson.LessonLength, tblSpecialLesson.MonthlyLessonCost INTO tblSpecialLesson
    FROM tblSpecialLesson INNER JOIN (tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID) ON tblSpecialLesson.ContractEndDate = tblContract.ContractEndDate
    WHERE (((tblContract.LessonType)="Cello" Or (tblContract.LessonType)="Piano" Or (tblContract.LessonType)="Violin" Or (tblContract.LessonType)="Voice"));

    however, when I add your code at the bottom of the code I posted above, it does not work:

    DELETE LessonType, ContractEndDate FROM tblContract
    WHERE (((tblContract.LessonType)="Cello" Or (tblContract.LessonType)="Piano" Or (tblContract.LessonType)="Violin" Or (tblContract.LessonType)="Voice") AND ((tblContract.ContractEndDate)<#7/1/2013#));

    Am I placing it in the wrong area or what can I do to fix this? In a moment I will post the updated version of the other query issues I was having.

  10. #85
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Ok I decided to save them now, so far I completed the append and update query, now I have to figure out how to do the delete query correctly. Do I have to just click show table and add tblSpecialLesson and drag all the records from that table into the field properties table below and then enter the date <7/1/2013 into the criteria underneath the ContractEndDate field value? Here is the lab so far below
    Attached Files Attached Files

  11. #86
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't add the DELETE action to another query. It works on its own.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #87
    k14ton is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    OkI got that it works on its own for the delete query, now do I have to just click show table and add tblSpecialLesson and drag all the records from that table into the field properties table below and then enter the date <7/1/2013 into the criteria underneath the ContractEndDate field value?

  13. #88
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    From earlier post "Delete queries delete entire records therefore there is no need to list a bunch of fields, just the criteria fields are enough."

    Yes, use the query designer to build the DELETE query. Select table, drag desired fields to grid, place criteria under appropriate fields, click DELETE button from design tab on ribbon. It appears there is one record in tblSpecialLesson that meets the criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 6 of 6 FirstFirst 123456
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-09-2012, 04:21 PM
  2. Replies: 0
    Last Post: 02-23-2012, 10:42 AM
  3. Microsoft Access 2010 - Filtered report?
    By keaccesshelp in forum Reports
    Replies: 1
    Last Post: 02-21-2012, 03:36 PM
  4. Replies: 1
    Last Post: 09-13-2011, 01:52 PM
  5. Microsoft Access 2007 - 2010 Compatibility
    By AccessFreak in forum Forms
    Replies: 4
    Last Post: 01-05-2011, 07:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums