If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to update table fails
I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table. Before the update: CID EsortKey Event LsortKey Level R1 30 0 solo 1 Novice R2 30 0 solo 2 Beginner R3 30 0 solo 3 Intermediate R4 30 0 solo 4 Advanced R5 30 0 duet 5 Novice R6 30 0 duet 6 Beginner R7 30 0 strut 7 Novice R8 30 0 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner What I need after the update: CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 5 duet 5 Novice R6 30 5 duet 6 Beginner R7 30 7 strut 7 Novice R8 30 7 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner I posted this request a few days ago and it was suggested I try the following: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That produced a “Type Conversion Error so I changed it to: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That code updated the fields but what I now get is CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 1 duet 5 Novice R6 30 1 duet 6 Beginner R7 30 1 strut 7 Novice R8 30 1 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner |
#2
|
|||
|
|||
Query to update table fails
"Steve S" wrote: I am trying to update a field in a table with the first corresponding value in a small (30-40 records) subset of records in the same table. Before the update: CID EsortKey Event LsortKey Level R1 30 0 solo 1 Novice R2 30 0 solo 2 Beginner R3 30 0 solo 3 Intermediate R4 30 0 solo 4 Advanced R5 30 0 duet 5 Novice R6 30 0 duet 6 Beginner R7 30 0 strut 7 Novice R8 30 0 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner What I need after the update: CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 5 duet 5 Novice R6 30 5 duet 6 Beginner R7 30 7 strut 7 Novice R8 30 7 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner I posted this request a few days ago and it was suggested I try the following: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That produced a "Type Conversion Error so I changed it to: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That code updated the fields but what I now get is CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 1 duet 5 Novice R6 30 1 duet 6 Beginner R7 30 1 strut 7 Novice R8 30 1 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner Sure looks like you wanted UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees", "Event = '" & F.Event & "' and [CID] = 30") WHERE F.[CID]=30; Event is text so need to wrap value in single quotes, you aliased table Fee as F so must reuse "F", not "Fee", and don't want quotes around F.Event because this is referencing the value of the current record you are looking at and comes from "outside" the domain function. |
#3
|
|||
|
|||
Query to update table fails
I would try the following.
UPDATE Fees as F SET F.EsortKey = DMin("LSortKey","Fees","CID=" & F.CID " AND Event='" & F.Event & "'" ) WHERE F.CID = 30 '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Steve S wrote: I am trying to update a field in a table with the first corresponding value in a small (30-40 records) subset of records in the same table. Before the update: CID EsortKey Event LsortKey Level R1 30 0 solo 1 Novice R2 30 0 solo 2 Beginner R3 30 0 solo 3 Intermediate R4 30 0 solo 4 Advanced R5 30 0 duet 5 Novice R6 30 0 duet 6 Beginner R7 30 0 strut 7 Novice R8 30 0 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner What I need after the update: CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 5 duet 5 Novice R6 30 5 duet 6 Beginner R7 30 7 strut 7 Novice R8 30 7 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner I posted this request a few days ago and it was suggested I try the following: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That produced a “Type Conversion Error so I changed it to: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That code updated the fields but what I now get is CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 1 duet 5 Novice R6 30 1 duet 6 Beginner R7 30 1 strut 7 Novice R8 30 1 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner |
#4
|
|||
|
|||
Query to update table fails
Thanks much guys. Gary, what you sent was exactly what I got a few days ago
that generated an error message. I think I mistyped the sequence of the single-double quote marks then. This time I cut/pasted your string and it worked great. It looks like the correct sequence is ' " string " '. Is that true. John I didn't test your responce but thanks. steve "Steve S" wrote: I am trying to update a field in a table with the first corresponding value in a small (30-40 records) subset of records in the same table. Before the update: CID EsortKey Event LsortKey Level R1 30 0 solo 1 Novice R2 30 0 solo 2 Beginner R3 30 0 solo 3 Intermediate R4 30 0 solo 4 Advanced R5 30 0 duet 5 Novice R6 30 0 duet 6 Beginner R7 30 0 strut 7 Novice R8 30 0 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner What I need after the update: CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 5 duet 5 Novice R6 30 5 duet 6 Beginner R7 30 7 strut 7 Novice R8 30 7 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner I posted this request a few days ago and it was suggested I try the following: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That produced a “Type Conversion Error so I changed it to: UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " & 'Fees.Event' & " and [CID] = 30") WHERE F.[CID]=30; That code updated the fields but what I now get is CID EsortKey Event LsortKey Level R1 30 1 solo 1 Novice R2 30 1 solo 2 Beginner R3 30 1 solo 3 Intermediate R4 30 1 solo 4 Advanced R5 30 1 duet 5 Novice R6 30 1 duet 6 Beginner R7 30 1 strut 7 Novice R8 30 1 strut 8 Beginner R9 27 0 solo 22 Novice R10 27 0 solo 23 Beginner |
#5
|
|||
|
|||
Query to update table fails
"Steve S" wrote:
snip It looks like the correct sequence is ' " string " '. Is that true. snip UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees", "Event = '" & F.Event & "' and [CID] = 30") WHERE F.[CID]=30; Event is text so need to wrap value in single quotes, you aliased table Fee as F so must reuse "F", not "Fee", and don't want quotes around F.Event because this is referencing the value of the current record you are looking at and comes from "outside" the domain function. That is correct. Maybe this will help... The "where clause" of the domain function must be wrapped in double quotes. That means that any further quotes within the clause should be single quotes (or 2 double quotes). If the clause was not "correlating" back to the main query to get an Event, say for some reason the Event needed to only be "solo," the clause would be properly constructed like: "Event = 'solo' AND [CID] = 30" or "Event = ""solo"" AND [CID] = 30" This way the parser knows the clause starts with "Event... and ends with ...30", i.e., the double quotes tell it where the clause starts and ends. To introduce a specific string within the clause, we wrapped that string in single quotes instead of double quotes so we don't confuse the parser. In our case we want to *correlate* back to the main query to get the Event from the record we are dealing with at the time (that is one reason why domain functions can cause queries to be so slow...our function is evaluated for every record). So, in our clause, instead of "solo" we want the parser to "stop a second," go get the current value for Event, and put it where "solo" used to be, then continue. "Event = ' stop,what is current F.Event? ' and [CID] = 30") We temporarily "stop the clause" with a double quote. "Event = '" We wrap F.Event in ampersands to include the current value of F.Event in the clause. "Event = '" & F.Event & We then "continue the clause" with a double quote. "Event = '" & F.Event & "' and [CID] = 30") Note, if CID and Event had been swapped in the clause... "[CID] = 30 AND Event = '" & F.Event & "'") for sake of clarity (but you must use clause above for the query to work): "[CID] = 30 AND Event = ' " & F.Event & " ' ") |
Thread Tools | |
Display Modes | |
|
|