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: 191
Thanks: 52
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: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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.

Web 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
Previously known as ridders

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 05:44 PM.


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

Sponsored Links

How to advertise

Media Kit


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