I cannot edit information through a query

[jammyo2k]

This database has kept me up all night. I need to make a query storing performance data for students in a school. To produce input forms I need to have a query that I can use to update the data held within the tables.

There is a single student entity which has a one to many relationship with a table for each subject. The SQL for the query looks like this:

SELECT Students.[Admission Number], Students.Forename, Students.Surname, Students.Year, Students.House, Art.[Art 1], Geography.[Geo 1], History.[His 1], IT.[IT 1], Maths.[Maths 1], Music.[Music 1], PE.[PE 1], RE.[RE 1], Science.[Sci 1], Technology.[Tech 1], English.[Eng 1], Students.[Maths Set], French.[French 1], German.[Ger 1], Spanish.[Span 1]
FROM (((((((((((((Students INNER JOIN Art ON Students.[Admission Number] = Art.[Admission Number]) INNER JOIN English ON Students.[Admission Number] = English.[Admission Number]) INNER JOIN French ON Students.[Admission Number] = French.[Admission Number]) INNER JOIN Geography ON Students.[Admission Number] = Geography.[Admission Number]) INNER JOIN German ON Students.[Admission Number] = German.[Admission Number]) INNER JOIN History ON Students.[Admission Number] = History.[Admission Number]) INNER JOIN IT ON Students.[Admission Number] = IT.[Admission Number]) INNER JOIN Maths ON Students.[Admission Number] = Maths.[Admission Number]) INNER JOIN Music ON Students.[Admission Number] = Music.[Admission Number]) INNER JOIN PE ON Students.[Admission Number] = PE.[Admission Number]) INNER JOIN RE ON Students.[Admission Number] = RE.[Admission Number]) INNER JOIN Science ON Students.[Admission Number] = Science.[Admission Number]) INNER JOIN Spanish ON Students.[Admission Number] = Spanish.[Admission Number]) INNER JOIN Technology ON Students.[Admission Number] = Technology.[Admission Number]
WHERE (((Students.Year)="7"))
ORDER BY Students.[Admission Number];

Sorry, I know the query is quite large.
If anyone can help I would be very grateful
Thanks
Jim

 

[raskew]

Database design is the major problem here. There is no need for separate tables for each course.

Take a look at the Access StudentsAndClasses database template and model your application around that format.

 

[shahid]

same with me !

I had the same problem, so I used simple select query to do the modification. Its strange that the access query if ran within access allowed me to update data, but the same query didn't allow modification when ran from ASP page.

Can someone tell why is that ?

 

[jammyo2k]

Well, practically speaking, changing the structure of the database will not be particularly easy. But I find that if I use more than two tables in a query then I cannot edit the data. Is there anything else that could be done?

 

[raskew]

Make the investment, spend the time getting it right, and you'll save yourself a whole lot of grief in the long-run.

 

[Pat Hartman]

While you're at it, rename your fields to remove the embedded spaces. And don't use function names or property names such as Date and Name as column names. If you ever need to use any VBA, you're in for a bit of a hassle.

Take a look at the ideas for table design presented in the following articles:

http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

http://support.microsoft.com/support/kb/articles/Q288/9/47.ASP

At the moment your tables look like spreadsheets. Spreadsheets work great with Excel but are lousy within Access.