Tables & Queries:
An Append query puts a copy of the records and fields that you select into an existing table.
What good is an append query?
Danger with an append query - Nothing will stop you or warn you about running the query again and appending another copy of the same records. Access assumes that you knew what you were doing! The exception is an append query that includes the primary key for the table. Access will not let you repeat values for a primary key.
It bears repeating:
Project 3: Tables & Queries
Example: Append Query
The query Append to Scores - Pick A/T in the LanguageArtsClasses database creates a partial record in the Scores table for each student for the Assignment/Test ID number that you enter. (There must already be a matching record in the Assignment/Test table.)
Problem: Each assignment or test needed a record in Scores for each of the 92 students. Boring to create all 92 manually!
Solution: Use a query to create partial records automatically. The teacher (me!) enters an ID number for the assignment or test. Running the query adds a record to Scores for each student for the selected assignment or test. There are no scores yet. After running this append query, I could then open the Scores table and finish the newly appended records by typing in a score for each student.
Steps in the process:
About the blank fields: The LetterGrade values are added later with an Update query, which we will look at shortly. The Ignore? field is a Yes/No field that would normally be blank. A Yes in this field would mean that the score would not be included in averages.
Point of Confusion/Disadvantage: Unnecessary records are created by this append query if the assignment or test was not actually assigned to all classes! A/T ID 87 is an example of this. Assignment 87 was a spelling test that was not assigned to class D for scheduling reasons. The C/S value of 8 is the code for class D and Spelling. So, the records for assignment 87 and C/S value of 8 have no scores.
The unwanted records could be deleted manually or by using a Delete query.
AutoNumbers: The AutoNumber primary key will not reuse the ID numbers of deleted records. That is why the ID numbers for the new records are over 9000 when there are only 7537 records in the table. Quite a number of records were deleted along the way.
If there were going to be a lot of assignments that would not be given to all
three classes, the query could be changed to include a parameter for the C/S field,
which picks out the class and subject of the assignment. Wonder why I
didn't do that??
Start with: , Projects database open.
An append query adds new records to an existing table by copying data from other tables or queries. One use for this kind of query is to create partial records to be sure that the correct number of records are created.
You will add a new project to the Projects table. You want to assign all staff members in Argentina to the new project. You will use a query to select those staff members and append them to the ProjectStaff table. Since they will not all have the same job for the project, you will have to enter the data for the Job field separately.
Add Record to Projects Table
What fields need to be in the append query?
Create Select Query
Change to Append Query
View Results and Complete Records by Entering Values for Job
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~