Very strange Update query needed

[Richardson]

I have inherited a strange query situation that I have not been able to
resolve and this has become an urgent issue. I am open to any suggestions
that will solve the problem. I have included many details, but I can
provide more if you need them.

I have 3 tables, Members, Registration, and Credits
Members includes these : MemberID, Name
Registration includes: RegistrationNumber, MemberID, Date, Class,
Rank,WorkPoints,FinalScore, Credits
Credits includes: Rank, Credits

FinalScore is a blank value in the table. It is determined by taking the
member's rank, and displaying credits based on the rank. Each member also
gets a point for the number of members registered in the class by each date.

Example:3 members register on a particular day for class A
Sally's final rank in class A is 1, so she gets 200 credits for being in
the class and 3 credits for the members for a FinalScore of 203.

Here is the setup so far:
Query1:Combine tables members, registration, and credits to get the credits
for the final score. (this is not updatable becuase there are 3 tables)
Query2:A Summary query that counts the number of registrations in each class
for each date.
Query3: combines query1 and query2 to add the final score credits with the
number of registrations.

Everything works great to this point and the FinalScore calculates
correctly. However, I now need to make the finalscore populate the
FinalScore field in the table registration. (I know it is a bad idea to put
a calculated value in the table, but is has to be that way)
When I attempt to make this an update query, it will not update the
finalscore field because it is in a nonupdatable recordset (query1)

I am forever grateful to the person that can help me resolve this dilema.

Lori

 

[chris]

One solution is to have your final query populate a
worktable, and then use the worktable in place of the
query.

 

[Chris2]

I have inherited a strange query situation that I have not been able to
resolve and this has become an urgent issue. I am open to any suggestions
that will solve the problem. I have included many details, but I can
provide more if you need them.

I have 3 tables, Members, Registration, and Credits
Members includes these : MemberID, Name
Registration includes: RegistrationNumber, MemberID, Date, Class,
Rank,WorkPoints,FinalScore, Credits
Credits includes: Rank, Credits

CREATE TABLE Members
(MemberID INTEGER
,Name Char(72)
,CONSTRAINT pk_Members PRIMARY KEY (MemberID)
)

CREATE TABLE Registration
(RegistrationNumber INTEGER
,MemberID INTEGER
,RegDate DATE
,Class Char(48)
,Rank INTEGER
,WorkPoints INTEGER
,FinalScore INTEGER
,Credits INTEGER
,CONSTRAINT pk_Registration PRIMARY KEY (RegistrationNumber)
,CONSTRAINT fk_Members FOREIGN KEY (MemberID)
REFERENCES Members (MemberID)
,CONSTRAINT fk_Credits FOREIGN KEY (Rank)
REFERENCES Credits (Rank)
)

CREATE TABLE Credits
(Rank INTEGER
,Credits INTEGER
,CONSTRAINT pk_Credits PRIMARY KEY (Rank)
)

Sample Data

Members
1, Sally
2, Rob
3, Danielle

Registration
1, 1, #01/01/04#, "Chemistry", 1, 1, 1, 0
2, 2, #02/01/04#, "Literature", 2, 1, 1, 0
3, 3, #02/25/04#, "Video Game History", 3, 1, 1, 0

Credits
1, 222
2, 444
3, 666

Expectations:

That Regisration.Credits will update with Credits.Credits values.


UPDATE (Credits AS C1
INNER JOIN
Registration AS R1
ON C1.Rank = R1.Rank)
INNER JOIN
Members AS M1
ON R1.MemberID = M1.MemberID
SET R1.Credits = C1.Credits
WHERE R1.Rank = C1.Rank

I pasted the above query into SQL VIEW, and executed it.
Registration.Credits updated successfully with the values from
Credits.Credits. I'd imagine substituting other expressions for "SET
R1.Credits = C1.Credits" would be possible.

 

[Richardson]

Thank you.
I will try this out today.
Lori

CREATE TABLE Members
(MemberID INTEGER
,Name Char(72)
,CONSTRAINT pk_Members PRIMARY KEY (MemberID)
)

CREATE TABLE Registration
(RegistrationNumber INTEGER
,MemberID INTEGER
,RegDate DATE
,Class Char(48)
,Rank INTEGER
,WorkPoints INTEGER
,FinalScore INTEGER
,Credits INTEGER
,CONSTRAINT pk_Registration PRIMARY KEY (RegistrationNumber)
,CONSTRAINT fk_Members FOREIGN KEY (MemberID)
REFERENCES Members (MemberID)
,CONSTRAINT fk_Credits FOREIGN KEY (Rank)
REFERENCES Credits (Rank)
)

CREATE TABLE Credits
(Rank INTEGER
,Credits INTEGER
,CONSTRAINT pk_Credits PRIMARY KEY (Rank)
)

Sample Data

Members
1, Sally
2, Rob
3, Danielle

Registration
1, 1, #01/01/04#, "Chemistry", 1, 1, 1, 0
2, 2, #02/01/04#, "Literature", 2, 1, 1, 0
3, 3, #02/25/04#, "Video Game History", 3, 1, 1, 0

Credits
1, 222
2, 444
3, 666

Expectations:

That Regisration.Credits will update with Credits.Credits values.


UPDATE (Credits AS C1
INNER JOIN
Registration AS R1
ON C1.Rank = R1.Rank)
INNER JOIN
Members AS M1
ON R1.MemberID = M1.MemberID
SET R1.Credits = C1.Credits
WHERE R1.Rank = C1.Rank

I pasted the above query into SQL VIEW, and executed it.
Registration.Credits updated successfully with the values from
Credits.Credits. I'd imagine substituting other expressions for "SET
R1.Credits = C1.Credits" would be possible.