Insert query resuly into a table

[baijuep]

there is a query which i made which counts data from two tables RAUTH AND PERS

SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held
FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy
GROUP BY RAUTH.coy, RAUTH.Auth;

THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table

RAUTH table structure

Coy Auth Held

Tata 12

Dell 11

HP 21

Opera 11

Bangour 10

pers table

Persno Name Coy Qual

124578 Gita Tata Bsc

235689 Sonu Dell BA

748596 Kiran HP BCA

459687 Sony Tata MCA

965214 Baiju Bangour CCNA

963656 Ashok Opera MCA

 

[Pat Hartman]

Storing calculated values violates normal forms and has the potential to cause data anomalies if one of the fields gets updated but the calculation isn't rerun. So, running a query to do this is especially bad. So bad in fact that Access doesn't support it. If you insist on storing a calculated value like this, you will need to calculate the value and save it in a temp table. Then use the temp table to update the other table.

It is a far better practice to perform the calculation in a query as needed. That way you won't need to worry about all the places you might have to force this update to happen to keep the calculated value current. I strongly suggest you reconsider.

 

[carahorse]

hey, I need this answer too, I am working with sharepoint 2010 ms office 363, and it does not reference queries columns from ms access, only tables columns. Soooo any results from formulas either from forms or querys have to be recorded on the table columns if I want the information to be visible to all of my group/organization.

the only other work around that I could think of is that on load of the database or afterUpdate form, the field containg the value from the query.value.textbox fills the value for the associated field in the table needed table.field.value?

Is there a copy and paste method?
Maybe a DoCommand copy querytextfield.value=table!reservations.allinfo.value? on the after update of the form?

I did see this, but how to do it with VBA?

INSERT INTO Phone_Details
SELECT 'USA', 'CA', Phone
FROM Phone_Table
where substring(phone, 1, 3) in ('800', '866', '877', '888')​

 

[baijuep]

YES i agree with your suggestion. how can it be done

 

[carahorse]

I had to do it like this:

I set up a form, with a query expression that said " 2All info"Name: "&[name]& "Age: "&[Age]"Weight: "&[Weight]) and so on until it read John Doe Age: 76 Weight: 150

Then I have a column on the table called All Info.

On update or on load of the form I have a vba code like this

Me.2All_info.value=All_Info.value

and wala! I now have results from a calculated query going to a column in a table

let me know if you need more help =)