Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2018, 04:17 AM   #1
basilyos
Newly Registered User
 
Join Date: Jan 2014
Posts: 210
Thanks: 56
Thanked 3 Times in 3 Posts
basilyos is on a distinguished road
update table from query

hello guys,


i have a query that counts how many times the same record exist


and i have table that i want to update the field on counter with the data from this query


this is the query
Code:
SELECT Count(tbl_Personal_Sanctions_02.Sanction_Date) AS txt_Numbers_Original
FROM tbl_Personal_Sanctions_02
WHERE (((tbl_Personal_Sanctions_02.PID)=[Forms]![frm_Sanctions_Out]![PID]) AND ((tbl_Personal_Sanctions_02.Sanction_Date) Between [Forms]![frm_Sanctions_Out]![Sanctions_Date]-3650 And [Forms]![frm_Sanctions_Out]![Sanctions_Date]) AND ((tbl_Personal_Sanctions_02.Sanction_Code.Value)=[Forms]![frm_Sanctions_Out]![Sanctions_Code_01]));

now i want to update the field code1counter in table 1 with the data from the field txt_Numbers_Original in the query


any solution plz

basilyos is offline   Reply With Quote
Old 10-13-2018, 04:59 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,346
Thanks: 100
Thanked 2,148 Times in 1,993 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: update table from query

Why save the results of your count? Just obtain the values as needed using your aggregate query

If you really insist on saving them, you have a problem to overcome.
Aggregate queries are READ ONLY. So if you run an update query on the destination table using your aggregate query as the source data, I believe you will get an error that you must use an updateable query

To get around that, you could save the results of your aggregate query to a TEMP table using a MAKE TABLE query. Then use the TEMP table as the source for your UPDATE query

The issues with this approach are that:
a) the values won't necessarily be up to date.
b) repeatedly making TEMP tables will cause database bloat & may lead to instability
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
basilyos (10-13-2018)
Reply

Tags
query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Create Update Query to update values from form to a table Tor_Fey Queries 1 08-16-2017 01:37 AM
Update query criteria compare a field in another table and update accordingly AccessBee Queries 11 06-29-2015 03:34 PM
Update Query - Update Duplicate value from Single Matching value from another table xxx Queries 4 06-14-2011 11:28 PM
UPDATE query (not update if certain critea exist in the destination table) homer2002 Queries 6 02-15-2003 02:15 PM
Using and Append (or update) Query to update a table from a linked table. Plim Queries 3 10-17-2001 08:25 PM




All times are GMT -8. The time now is 06:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World