SELECT DISTINCTROW / Updating a query

[Pharcyde]

Hey all,
This relates to both forms and queries, so i stuck it in here...

In my dept there are 4 forms for entering/displaying exam marks. Its the typical thing where you enter the marks, and the form calculates the wightings, pass/fail and so on. Nothing complicated there...

The forms dont use a query as a source, they use a SELECT DISTINCTROW statement to gather the data, then work it all out from there.

Heres the problem

I need to amalgamate (good word!) the forms and tables (from 1 db) into one (on our new db), so that 1 students form holds all their exams/results.

• I merged the tables into one as best I could (there were a few before)

• I then made a query to bring up the necessary students

Looks like I cant enter the marks, only view them. The reason I tried to use a query was so that we could display all the subjects...

So, does this make sense? What are my options here? Can anyone help me out pleeeeeese?

Mucho Grasias, LeeBoy

 

[Len Boorman]

If the query has DISTINCT, any AGGRAGATION in it then update is not possible because it is not possible to determine exactly which value is to be updated.

L

 

[Pharcyde]

sweet. thats logical. but i still dont really get it...Its one of those weeks Len. Would you mind If I attached a copy of the SQL statement I'm using? I'm hoping you could tell me what I've done wrong...?

Cheers, LeeBoy.

P.S - Sky blues fan by any chance?

 

[Len Boorman]

Sky Blue. Did were looking for a bit of assistance weren't you !!!!

Okay

Bit difficult working purely with the sql

The joins are going to give you grief as well

Work at the query level to start with and only one table. The one where you want to update a value. Run the query and make sure that you can update the value you want to change. Extend the query to restrict the record set to as few rows as possible. Ideally just the row containing the data element you wish to update.

Bear in mind that a DISTINCT blows it immediately

With a bit of luck you will not need to go beyond a single table.

Then extend it as needed checking all the time that you can still update.

Check carefully that your tables are normalised.

I have got to the stage before where I have had to create a table with the first query (i.e. one with joins) then a simple select query from the tempory table, Update the value and then update backwards to the original table. This can be pretty messy

Good Luck

Len