Results 1 to 4 of 4
  1. #1
    Join Date
    May 2007

    Question Unanswered: Update Query In Ms Access 2003

    Hi all

    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:-

    UPDATE Registration
    SET Registration.Course_Code = Add_and_Drop.Course_Code
    WHERE [Add_and_Drop].[Add_or_Drop])="add";

    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
    FROM Add_and_Drop
    WHERE Add_and_Drop.Add_or_Drop="add";

    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
    WHERE [Add_and_Drop].[Add_or_Drop]="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

  2. #2
    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)

  3. #3
    Join Date
    May 2007
    Hi pkstormy

    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
    FROM Registration
    WHERE [Add_and_Drop].[Add_or_Drop]="drop";

    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.


  4. #4
    Join Date
    Jan 2007
    Provided Answers: 12
    Incorrect syntax for delete query.
    FROM Registration
    WHERE [Add_and_Drop].[Add_or_Drop]='drop'
    Home | Blog

Posting Permissions

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