Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-26-2008, 01:41 PM   #1
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
update query

Hi Guys
in the attached database there are 2 tables
table1 has records with the same id number, but different names, claims numbers and the network field
table2 has records with the same id numbers as in the first, network field, name field and claims

is there a way to update the second table's CLAIMS field with the sum of the claims from the first table based on the id number and the network

so the tables have to be joined by the id and the network, and the correct numbers in table2 CLAIMS field after updating should be

16
97
73
142

i tried sum, dsum, but i'm missing something

also, i know there's a way to do it in 2 steps, my question is if there's any way to do it in one query or one step

thank you!!!
Attached Files
File Type: zip SampleQuestion.zip (8.3 KB, 133 views)

lala is offline   Reply With Quote
Old 08-26-2008, 03:39 PM   #2
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

In a nutshell, don't save a calculated value in a table as that would be violation a normalization rule. Calculated values are always easily generated.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-26-2008, 03:43 PM   #3
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

yes, i know, i need to know how to generate it

this is not a database, this is just some data manipulation i'm doing using access, one time thing

so how do i update that field?

lala is offline   Reply With Quote
Old 08-26-2008, 04:01 PM   #4
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

Control "Result" has a "Control Source" of

PHP Code:
=forms!YourFormName!Control1Name forms!YourFormName!Control2Name 
You calculation may be a complex as you want. You can use references to sub forms, sub sub forms and other forms in your calculation.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-26-2008, 04:12 PM   #5
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

no, first of all, why are you multiplying?
second of all, the problem here is that i need it to sum up records grouped by 2 fields and then update a field from table2 to the sum of those records


i understand that i can do any calculations, but that's not what this is about, this is about getting the sum of certain records and then updating to that sum

i don't even think you read my problem, because both of your answers have nothing to do with it

or maybe i didn't explain it clear enough?

let me know

and thank you for your time


also, they're not forms, they're tables, i don't need forms, i just need to update all this data
lala is offline   Reply With Quote
Old 08-26-2008, 04:27 PM   #6
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

Group and sum in a query. You may have to query a query because of the grouping. In a grouping query every column gets grouped.

Suscinct questions get the best results.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-26-2008, 05:36 PM   #7
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

I know that
I need it to group
And I know how to do it with another query, I also said that in my post

I'm asking if anyone knows of a one query way to do this

Listen you don't have to try to help me, but if you are, then at least read my problem carefully enough to understand what I need, or if I'm not clear, ask me whatever you need
Again, this is not what I needed and I said I know how to do it with 2 queries or a table and a query


I'm looking for a one query way to do what I described above
Thank you

lala is offline   Reply With Quote
Old 08-27-2008, 05:54 AM   #8
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

Don't bite the hand the feeds you (even kibble and bits).

If you know how to do it with multiple queries, what's the issue?

Multiple queries are always faster and easier to understand than one elegant query. Elegant queries are very, very difficult to update a day after initial construction.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-27-2008, 05:59 AM   #9
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

what's the issue?
because i'm sure that my way is not the best way (as always) and want to learn from the smart people here (as i always do)

Quote:
Elegant queries are very, very difficult to update a day after initial construction.
what do you mean by this??
lala is offline   Reply With Quote
Old 08-27-2008, 07:21 AM   #10
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

Elegant, that is complex, queries are unnecessarily difficult to understand after they have been constructed, even by the author.

There are many ways to do something in Access. Quite often good (correct) code just doesn't work for no apparent reason. Any method that works is generally sufficient and satisfactory. With today's fast computers, speed of a sequence of instructions is seldom an issue. There's no discernable difference be 100 milli-seconds and 500 milli-seconds or between searching 50 records and 50,000. I recently encountered a client which indexed tables with 150 character text fields. Storage has become less and less of an issue.

I agree that those that know the most tricks are the best. There are so many good books on Access that one doesn't have to spend hours in developing new tricks. The wheel was invented long ago.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-27-2008, 07:31 AM   #11
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

i don't understand what you're trying to say?
that learning or trying to make something on your own is not the way to go these days?

i, for example, like to make my brain work and try to better my applications and even my work routine all the time.
no matter how good it is, it can always be better
and even if it can't, that's how i learn new things in the process

so i don't get your point

i post here and with the exception of this post (i usually post in the FORMS forum) ALWAYS got either an answer or at the very least, some brain work from the people here

they're the same way here, they like to exercise their brain and try to come up with elegant solutions.
sometimes it's not about just getting it to work, to me it's not anyway.
it has to look nice too

anyhow, i guess those people don't come to this forum, will go back to FORMS then
lala is offline   Reply With Quote
Old 08-27-2008, 08:10 AM   #12
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

forgot to add, speed, of course, is nothing these days, but when you have a few million of records, saving one step is a big deal
lala is offline   Reply With Quote
Old 08-27-2008, 08:25 AM   #13
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

PHP Code:
saving one step is a big deal 
Not true.
__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Old 08-27-2008, 08:27 AM   #14
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: update query

why not? do you know how long it takes to run a make-table query with 2 million records?
2-3 minutes
that's just for one of them
i have about 15 of those tables that i have to update
and then after doing the maketable i still have to run the update query, and that's another minute or so

so yes, very true
lala is offline   Reply With Quote
Old 08-27-2008, 10:16 AM   #15
llkhoutx
Newly Registered User
 
Join Date: Feb 2001
Location: Houston,Texas USA
Posts: 4,018
Thanks: 6
Thanked 106 Times in 104 Posts
llkhoutx is on a distinguished road
Re: update query

I regularily deal with multiple 20-30 million record tables and multiple INNER and OUTER joins from huge data warehouses. Query time is relatively insigificant. Long running queries are generally the result of complex joins which can be accelerated by breaking them into multiple queries.

No more quibbling from me.

__________________
Get the job done right, rather than do the right job.
Do it fast or do it right. Teach a man a fish . . .
If it ain't broke, don't fix it.
Note that I don't open posted databases, period.
llkhoutx is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
update query problem Slayboy Queries 1 07-24-2007 02:52 AM
Update query kathat23 Queries 3 05-18-2007 08:49 AM
Update query for capturing log out time of user gold007eye Queries 9 05-18-2006 12:10 PM
Customizing Update Query Badswell Queries 1 03-23-2004 02:16 PM
Update Query problem Blackwidow Queries 1 03-18-2004 03:00 AM




All times are GMT -8. The time now is 04:39 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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World