Thread: Update Query In Ms Access 2003
07-15-07, 03:11 #1Registered User
- Join Date
- May 2007
Unanswered: Update Query In Ms Access 2003
I am designing a student registration database and am stuck with one part of the system which is to automatic update of records from one table to another from a from....
Table Add_and_Drop has columns ID,Course code,Add/drop (combo box with add or drop field),reason,penality,date
and registration table with ID,Course code,semester,year.....
I have a add/drop from in which allows students to add or drop a course, now the update button should activate my update query where the course if added would be add to the registration table,and the droped course would be deleted from the registration....but the records would be kept intact in the add drop table....
Icreated an update query as below:-
SET Registration.Course_Code = Add_and_Drop.Course_Code
this is not correct because the where clause has add, what about drop....i have not catered for that.....
Do I have to create two queries,one for delete and one add....??
Ok I tried that and created an append query as below:
INSERT INTO Registration ( Student_ID, Course_Code )
SELECT Add_and_Drop.Student_ID, Add_and_Drop.Course_Code
and it adds table!!
But when I create the delete query I as below:-
DELETE Registration.Student_ID, Registration.Course_Code, Registration.Semester, Registration.Year, [Add_and_Drop].[Add_or_Drop]
FROM Registration, Add_and_Drop
and try to run it i get an error messge to specify the table containing the records you want to delete....
there is another error when i open the query in datasheet view i see even the add entries displayed in it....whereas the condition should display drop entries only...........
i thought it was because of table design problem since i had done a combo box for add and drop field with default as drop so i removed both and still the add is displayed as drop in this query........
Can u tell me where am going wrong?
How can use an update query here?
Any information would be greatly appreciated
07-15-07, 04:16 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
To delete a table (using the docmd in code) you can use syntax like...
DoCmd.DeleteObject acTable, "TableName"
This is different than deleting records IN the table as it deletes the entire table. If you wanted to delete records IN a table, you can design a "Delete" query (or do it via code).
In regards to creating a table, you can do it via code or just design a "make-table" query.
I'm not sure if you're trying to do this via code or via queries but you may want to look at the different types of queries you can design (i.e. Select, Crosstab, Make-Table, Update, Append, Delete.) You may want to read the MSAccess help on what each one does. A Select query "selects" records in a table. A Make-Table query "makes" a new table. An Update query "updates" records already existing in a table. An Append query "appends/adds" new records to a table. A Delete query "deletes" records out of the table (but doesn't delete the table itself.)
If you're doing it via code, there are good examples of the above query types in code in several posts and some good MSAccess books (and via googling it.) You can also design a query, select what type you want (ie. under Query) and in the "grid" box under File in the upper left corner, select SQL and you'll see the sql statement. This may help you on constructing your sql statement if you're doing it via code.
Last edited by pkstormy; 07-15-07 at 04:33.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
07-17-07, 04:31 #3Registered User
- Join Date
- May 2007
Thanks for the reply
Yes I trying to do this via query that will add records IN an existing table (which is student registration table)or delete records from that table, if a student adds a course or drops a course via an add_drop Form (tabulated as a separate table in my database.
I have created an APPEND Query that works all right....on running it the record is added to the registration table for all courses that the student added after registration........
However my problem is that my DELETE query (shown below) doesnt run.......when i try to run it i get error mesage that tells me to specify the table containing the records you want to delete
This is my DELETE query....
DELETE Registration.Student_ID, Registration.Course_Code, Registration.Semester, Registration.Year
I have specified that I want to delete from registration table all the colums for the student in the record which is stated as drop in the add_and_Drop table..............then why does it tell me to specify the
Can u tell me whats wrong with the above query???
Another thing is that when I look at the datasheet view of this query...
it displays all records in the add_and_Drop table as drop...
even the one that is added.
I thought it was because of a table design problem since i had done a combo box for add and drop field in the Add_and_Drop table with default value as drop.....So i removed both and still the add is displayed as drop in this query........
Any ideas what may be the cause?
I dont have knowledge of VB but its in my programme of study next semester....but for now am wroking with Relational DB (Access 2003),
and have knowledge of sql query coding, where agian am not perfect.....
but learning a lot through research over the net,particularly this forum....
its very informative and usefull....
Thanks and Cheers to dbforums and the moderators.
07-17-07, 05:11 #4www.gvee.co.uk
Provided Answers: 12
- Join Date
- Jan 2007