update query (1 Viewer)

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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!!!
 

Attachments

  • SampleQuestion.zip
    8.3 KB · Views: 168

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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?
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
Control "Result" has a "Control Source" of

PHP:
=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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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)

Elegant queries are very, very difficult to update a day after initial construction.
what do you mean by this??
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
PHP:
saving one step is a big deal

Not true.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
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
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
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.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
Code:
Vm1b=dlookup("fdate","firstdate")
Vm1e=dlookup("fdate","firstdate")+7

Vdt=dlookup("sumofclaims","cl_74mpi","claimreceiveddate>=vm1b and claimreceiveddate<vm1e"

Can you fix this?
This will go in vb, I know the syntax is very wrong, I'm on the train and don't have access here

The problem is that I also need the sum of sumofclaims. There's 7 dates, so I have to sum the claims for those dates
How do I specify that
 

llkhoutx

Registered User.
Local time
Today, 15:55
Joined
Feb 26, 2001
Messages
4,018
Ahhhhhhhhh, a train ride!


PHP:
Vm1b=dlookup("fdate","firstdate")
Vm1e=dlookup("fdate","firstdate")+7
doesn't look correct to me. Is "firstdate" a table or query? "dlookup" has three arguments, unless your fetching the first value in the table or query.

Note that compared do a DAO/ADO recordset lookup, "dlookup" is glacially slow.

In your case, date/time parameters, use

PHP:
Vdt=dlookup("sumofclaims","cl_74mpi","claimreceiveddate>=#" & vm1b & "# and claimreceiveddate<#" & vm1e & "#")

If numeric parameters, use
PHP:
Vdt=dlookup("sumofclaims","cl_74mpi","claimreceiveddate>=" & vm1b & " and claimreceiveddate<" & vm1e)

If alphanumeric parameters, use
PHP:
Vdt=dlookup("sumofclaims","cl_74mpi","claimreceiveddate>='" & vm1b & "' and claimreceiveddate<'" & vm1e & "'")

vmib and vm1e are variables, without correctly concantenating them in the WHERE clause, the Dlookup cannot evaluate them as you have it.

I suggest that you use pneumonic names for your object names. Code then is more meaningful when looking at it for the first time. I also use the "Hungarian" prefixes for object names so that the datatype and/or object type of an object is immediately known.
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
Look at you!!!!
You know what you're doing
So why couldn't you just help to begin with instead of giving off the wall answers))))))))

There's only one value in that table, I didn't know how else to tell it which date is the first one

I'm on my way upstairs, will try all your suggestions and I will have a few more questions then if you don't mind
This is very important to me, I will be doing this every month and I want to automate it as much as possible

Thank you for your help
 

lala

Registered User.
Local time
Today, 16:55
Joined
Mar 20, 2002
Messages
741
ok, first of all, what's HUNGARIAN prefixes?


vmib and vm1e are variables, without correctly concantenating them in the WHERE clause, the Dlookup cannot evaluate them as you have it.

what does this mean?


and as far as names, the numbers stand for week numbers, i have 5 sets of vm1e and vm1b for each week



last question for now, how do i tell it that i need a sum of sumofclaims where the date is in that range?


also, i'm not being lazy, i only post here after i tried everything i can think of and googled and searched the help file for everything i can think of

thank you again
 

Users who are viewing this thread

Top Bottom