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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
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?
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.