Updating a table via Query (1 Viewer)

[ypma]

EDITED BY THE_DOC_MAN to correct the code bracketing. NO edits to the actual content.

I would appreciate advice on updating a table via a select query which has two IIF fields .Query SQL below

SELECT QueryTraining.ID, QueryTraining.FirstName, QueryTraining.Hampshire, IIf([Hampshire]=True,20,0) AS HampshireFees, QueryTraining.[Cams league], IIf([Cams League]=True,18,0) AS CamsFees, Nz([camsFees])+Nz([hampshirefees]) AS ExtraFees, QueryTraining.Team
FROM QueryTraining
WHERE (((QueryTraining.Team)="Topaz"))

The above SQL work well in that the select query up dates when check boxes are ticked,, however the results are not updating the Training Table .

Regards Ypma

 

[Gasman]

SELECT queries do not update, they just select data.
UPDATE queries update.

Try changing the query in the QBE window to an UPDATE query and set the fields accordingly.

 

[theDBguy]

Depending on what QueryTraining is, you might still not be able to update the actual table. Perhaps it's better to show us the table and explain how you want it updated. Just a thought...

 

[The_Doc_Man]

Just as a suggestion to ease your typing, .... in that query you have a single table as the source, so you don't need to qualify any of the fields from that table. If you also removed that blank from Cams league, you could reduce the brackting.

SELECT ID, FirstName, Hampshire, IIf([Hampshire]=True,20,0) AS HampshireFees, CamsLeague], IIf(CamsLeague=True,18,0) AS CamsFees, Nz([camsFees])+Nz([hampshirefees]) AS ExtraFees, Team
FROM QueryTraining
WHERE (((Team)="Topaz"))

As to the original question, I concur with Gasman and theDBguy. We need to know what you want to happen - in simple language, not in some type of code.

 

[ypma]

Thanks for all who took the time to reply. Gasman , I tried using update , however the calculated fields don't seemed to be recognised .
The_Doc_Man , I don't have problem with the query as mentioned in my Post 1 . The demo database shows exactly what I am trying to achieve, I notice that no one has viewed it , so am at a loss as how else I can explain my desired result . There are over 200 record in the working database and due to price changes in the various leagues ,I thought I could create a query for each League., then clicking on the check box , which in turn would activate the calculated fields . It would have been nice if by just ticking all the check boxes in the select query the table would have been updated to reflect the calculated fields . c'est la vie

Regards Ypma

 

[Uncle Gizmo]

Just as a suggestion to ease your typing, .... in that query you have a single table as the source, so you don't need to qualify any of the fields from that table. If you also removed that blank from Cams league, you could reduce the brackting.

Further to Doc Man's observation, have a look at the Video "Take out the Tables" in this list of Nifty Access Tips YouTubes:-

Nifty Tips - Nifty Access

Nifty Video Tips Using an excellent new feature of Elementor Page Builder I am now able to show you time indexed clips from my MS Access Videos. This way you can quickly and easily see a video demonstration of just a few minutes, a Demo that gives you a leg up in your MS Access

www.niftyaccess.com

The video "Take out the Tables" demonstrates an easy method of removing the table reference....

 

[Gasman]

Ok, this is all fresh to you remember, but just how is this meant to work?

You have one form here and it is using query queryTraining as its source.?
querytraining

SELECT Training.ID, Training.FirstName, Training.AmountDue, Training.TotalPaid, Training.NoLongerAplayer, Training.Hampshire, Training.HampshireFees, Training.[Cams league], Training.CamsFees, Training.Another, Training.AnotherFees, Training.ExtraFees, Training.Team
FROM Training;

If I enter a value into fees on the form, that will go back to the table via the query. I suppose you could say then that Select queries can update

You then have the query ExtraRates that you posted. All that does is calculate a value depending on another field. But that is all it does.?
QueryExtraFees

SELECT QueryTraining.ID, QueryTraining.FirstName, QueryTraining.Hampshire, IIf([Hampshire]=True,20,0) AS HampshireFees, QueryTraining.[Cams league], IIf([Cams League]=True,18,0) AS CamsFees, Nz([camsFees])+Nz([hampshirefees]) AS ExtraFees, QueryTraining.Team
FROM QueryTraining
WHERE (((QueryTraining.Team)="Topaz"));

You are lucky that I am able to read the DB as most uploaded I cannot.

For what you are now saying, I would have thought an Update query would work if these are one off extra fees

I am going to say I think your structure is all wrong. Those fees should be in their own table with a field to identify what type of fee is it is.
Then when you have a new fee, you just add record with that data to a MemberFee table. To see what is owed you sum all debits and credits, so perphaps the table should be called MemberTransactions table.

 

[ypma]

Ok, this is all fresh to you remember, but just how is this meant to work?

You have one form here and it is using query queryTraining as its source.?
querytraining

SELECT Training.ID, Training.FirstName, Training.AmountDue, Training.TotalPaid, Training.NoLongerAplayer, Training.Hampshire, Training.HampshireFees, Training.[Cams league], Training.CamsFees, Training.Another, Training.AnotherFees, Training.ExtraFees, Training.Team
FROM Training;

If I enter a value into fees on the form, that will go back to the table via the query. I suppose you could say then that Select queries can update

You then have the query ExtraRates that you posted. All that does is calculate a value depending on another field. But that is all it does.?
QueryExtraFees

SELECT QueryTraining.ID, QueryTraining.FirstName, QueryTraining.Hampshire, IIf([Hampshire]=True,20,0) AS HampshireFees, QueryTraining.[Cams league], IIf([Cams League]=True,18,0) AS CamsFees, Nz([camsFees])+Nz([hampshirefees]) AS ExtraFees, QueryTraining.Team
FROM QueryTraining
WHERE (((QueryTraining.Team)="Topaz"));

You are lucky that I am able to read the DB as most uploaded I cannot.

For what you are now saying, I would have thought an Update query would work if these are one off extra fees

I am going to say I think your structure is all wrong. Those fees should be in their own table with a field to identify what type of fee is it is.
Then when you have a new fee, you just add record with that data to a MemberFee table. To see what is owed you sum all debits and credits, so perphaps the table should be called MemberTransactions table.

Gasman : Thank you for you input. I took your advise and moved the IIF calculations into the query querytraining which has the training table as its source. I can now open the query extra fees which has the querytraining as its source and tick the appropriate check boxes and the records are edited to reflect the extra charges . You are correct I could have tried an update query , but now this is up and running I will leave well alone .
This net ball fees database has only one user and her desired outcome has been achieved . Thanks again

Regards Ypma

 

[The_Doc_Man]

OK, a clarification is in order... perhaps not for you, @ypma, because you now have things sorted out, but it might be helpful for other potential readers who might be confused about using a SELECT query to update a table.

A SELECT query cannot update a table.

BUT a FORM that uses a proper SELECT query as its recordsource CAN update the underlying table. In this case, it is not the query that actually does the update. It is the form.

This is because forms don't "care" so much about the exact structure of their recordsource. They simply want something that returns a recordset. Which is of course either a table or a SELECT query.

When you open a query in datasheet mode, you can also update the underlying table. But that is because "datasheet view" acts like a continuous form, and again it isn't the query that does the update. It is the form.