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!!!

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.

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?

Control "Result" has a "Control Source" of

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

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

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.

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

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.

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??

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.

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

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

saving one step is a big deal

Not true.

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

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.

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

Ahhhhhhhhh, a train ride!


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

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

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

If alphanumeric parameters, use
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.

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

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

So why couldn't you just help to begin with instead of giving off the wall answers))))))))

That was a joke, right?

of course, )))))))))))))))))))) means a laugh

or at least to me it does

Cool. Hungarian prefixes refer to a method of naming your programming objects and variables using a given standard. Usually, a prefix at the beginning of a text box name will be "txt". A combo-box/drop down list will be "cbo", and so forth. Some people (like me) extend that to variable names in code to define scope. For instance p_strIn would mean a parameter passed in to the subroutine of type string.

HTH.

HTH.

let me make the question simpler


VDt = DLookup("sumofclaims", "cl_74mpi", "claimreceiveddate>=#" & V1b & "# and claimreceiveddate<#" & V1e & "#")
VDtSum = sum(VDt)



this doesn't work
the first line is fine
the second line doesn't work

how do i get the sum of VDT?

Cool. Hungarian prefixes refer to a method of naming your programming objects and variables using a given standard. Usually, a prefix at the beginning of a text box name will be "txt". A combo-box/drop down list will be "cbo", and so forth. Some people (like me) extend that to variable names in code to define scope. For instance p_strIn would mean a parameter passed in to the subroutine of type string.

HTH.

HTH.

first of all, i didn't mean to offend you with that comment
trust me, i realize noone owes me anything, i was just trying to be funny
i guess it didn't work)))))))))))))

now, i understood the txt part, but p_strin - not really, probably because i don't know what subroutine means


also, can you maybe answer my previous question about summing that up?

again, i only post here after i try everything i can think of, i don't want you, guys, to think that i run here everytime i need something and i'm too lazy to do some research
not my case

Nag, nag, nag. Don't bite the hand that feeds you.

Ahhhhhhh upstairs! Steps, I presume. Daily exercise, except for running for the train and letting your mouth run you fingers.

To find the first value in a set (table or query), do a grouping query and specifying the "First" value for desired column(s).

"Concantenating" is the method of building a string, e.g. "A" & "B" = "AB"; "A=#" & B & "#" where B is a date (8/27/08) is A=#8/28/08#. See: http://en.wikipedia.org/wiki/Concatenation. You obviously didn't Google it.

VDtSum = sum(VDt) is meaningless. VDT is a result of a Dlookup, a (single) value, there's nothing to sum. Query the Dlookup table or query, summing the VDT column.

Take a look a DAO coding, it'll help solve you manipulation problems. O'Reilly's Access Database Design & Programming (3rd Edition) by Steven Roman, $5 used. It'll change your life. It's well worth the $35 retail price. Be sure to get the 3rd Edition, not a prior ed.

I think that a new thread is in order.

http://msdn.microsoft.com/en-us/library/aa260976.aspx

http://msdn.microsoft.com/en-us/library/aa260976.aspx

I'm unsubscribing this thread.

Ain't nothin' to it. Just saw two other posts where someone new was dissing the experienced person who was trying to help them. I guess you weren't doing that.

Are you sure "sum()" is what you want?

This thread seems to have morphed quite a bit from what it started out as. I opened the attached DB (from post 1) and can't see any relevance to the rest of the post.

So, I'll try to answer what I think the question is based on the information I have that I understand:
select name, sum(claims) from table1 group by name;
will give you the sum of all the claims from any given "name". Of course you can filter/sort it however you want.

select network, sum(claims) from table1 group by network;
will give you the sum of all the claims for any given network.

And, since you have no unique index on table 1:
select name,network, sum(claims) from table1 group by name, network;
will give you the sum of all claims for a name within a network.

Thing is, I'm new to this party and am not sure how you got to where you were when I butted in. If you need much more from me, I'll need a lot more detail as concise as you can get it (I'm having problems with my attention span).

Obviously, you can replace sum() with count() and get a different, yet still valid answer. Or you can use them both and get a very valid answer.

i'm not biting))))))))))))
i was joking!!!!!!!!!!

and how am i nagging?

anyway, thank you, i will definitely get this book.
i'm selftaught and i really need to read something, i just wasn't sure what.
so if you say that book is good, i will order it right now


now, as far as the sum, there's no way to do it in VB? i have to make a query?

I'm unsubscribing this thread.


don't leave me!!!!!!!!!!!!!!! ))))))))))))))))))))))))

Holy mackarel, you two are acting like an old married couple.

lala, yell if you need more help. llkhoutx, yell if you want to grab lunch some time.

George, no, i'm not dissing anyone
if you pull up all my posts, half of them are praises and thank yous to all the people here and how amazing this forum is and so on
and i mean it all, the people here are amazing, and there wasn't one time when i didn't get an answer


yes, this is a new problem, has nothing to do with the topic, sorry

i'm trying to automate some data manipulation that i have to do

i get data, and it has to be put in a specific format
if you want, i can post it here, the real tables and the real format it has to be in, maybe then i won't even need to explain anything, since your attention span doesn't like boring long posts)))))))))))))))

you guys know each other??????????

OK George

the CLALL table is what it has to look like

all the CL_ tables are what the data looks like when i get it

the form that first opens up and has the button UPDATE TABLES - has all the code behind it. i'm not done with it yet, i copied it from a similar program i made, so it't not fixed as far as the field names and stuff

but it will give you an idea of what it's doing


so i'm trying to make a program that will automatically take all the data, manipulate it and plug it in the correct fields

right now i need to get it to sum that variable


also, maybe you will have a better idea of how to do this whole thing?
thank you so much for taking the time out

I gotta go somewhere and won't be able to get to this until later tonigh. And another poster wanted me to look at his/her stuff, too.

No, we don't know each other. I've watched his/her posts and have gained a respect for him/her. Just thought it would be friendly to grab lunch.

thank you!!
i will also keep trying

I'm about to leave but wanted to check something first. I've looked through your data pretty quickly.

Is there a reason unioning the tables (CL_*) together into one query and then using the cross tab wizard on that single union query didn't work? Like I said earlier, I'm getting to the game late and have the attention level of a kitten.

have the attention level of a kitten

that's so cute


Is there a reason unioning the tables (CL_*) together into one query and then using the cross tab wizard on that single union query didn't work
i'm not sure how
i mean i know what you're saying and how to do it, but i don't know how to connect them, they all have different number of records for each day, and some other differences
and i'm not sure what i will gain from it

ok George, i'm leaving
will check for your reply tomorrow
and thank you very much

and thank you llkhoutx for all your help too

Lala,

Your baggage is becomming a bit heavy around here. :D Lots of posts...

Lala,

Your baggage is becomming a bit heavy around here. :D Lots of posts...

What can I tell you, lots of ideas and not enough knowledge to put them to work

Am I annoying you?

of course not. I'm not helping you this time around. :D (ha ha)

Lala,

Here is the beginning of the answer:
SELECT CL_74MPI.CLAIMRECEIVEDDATE, CL_74MPI.SumOfCLAIMS FROM CL_74MPI
UNION SELECT CL_74PHCS.CLAIMRECEIVEDDATE, CL_74PHCS.SumOfCLAIMS FROM CL_74PHCS
UNION SELECT CL_FNX.CLAIMRECEIVEDDATE, CL_FNX.CLAIMS FROM CL_FNX;

You'll notice I didn't union all the tables together. That's because I have absolutely no clue which tables you're trying to work with. So your job is to come back with a working union query based on what I've provided.

After that, if you want to take a stab at it, read up on how to use the cross tab query wizard. Here is the help from version 2007. I imagine any other version will be quite different but I don't have access to them right now:
<H3>Create a crosstab query by using the Crosstab Query Wizard

Using the Crosstab Query Wizard requires that you use a single table or query as the record source for your crosstab query. If a single table does not have all the data that you want to include in your crosstab query, start by creating a select query that returns the data that you want. For more information about creating a select query, refer to the See Also section.

On the Create tab, in the Other group, click Query Wizard.
In the New Query dialog box, click Crosstab Query Wizard, and then click OK. The Crosstab Query Wizard starts.
On the first page of the wizard, choose the table or query that you want to use to create a crosstab query.
On the next page, choose the field that contains the values that you want to use as row headings. You can select up to three fields to use as row headings sources, but the fewer row headings you use, the easier your crosstab datasheet will be to read.
Note If you choose more than one field to supply row headings, the order in which you choose the fields determines the default order in which your results are sorted.
On the next page, choose the field that contains the values that you want to use as column headings. In general, you should choose a field that contains few values, to help keep your results easy to read. For example, using a field that has only a few possible values (such as gender) might be preferable to using a field that can contain many different values (such as age).
If the field that you choose to use for column headings has the Date/Time data type, the wizard adds a step that lets you specify how to group the dates into intervals, such as months or quarters.
If you choose a Date/Time field for column headings, the next page of the wizard asks you to specify the interval to use to group the dates. You can specify Year, Quarter, Month, Date, or Date/Time. If you do not choose a Date/Time field for column headings, the wizard skips this page.
On the next page, choose a field and a function to use to calculate summary values. The data type (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or number characters, but a Number field can store only numerical data.) (javascript:AppendPopup(this,'defFieldDataType_7') ) of the field that you select determines which functions are available.
On the same page, select or clear the Yes, include row sums check box to include or exclude row sums.
If you include row sums, the crosstab query has an additional row heading that uses the same field and function as the field value. Including a row sum inserts an additional column that summarizes the remaining columns. For example, if your crosstab query calculates average age by location and gender (with gender column headings), the additional column calculates the average age by location, across all genders. Note You can change the function that is used to produce row sums by editing the crosstab query in Design view.

On the next page of the wizard, type a name for your query and then specify whether you want to view the results or modify the query design.

</H3>If you cannot figure it out, post back with the working union query and I will walk through the help file, performing each step exactly as described to create your crosstab query. I'm hoping you'll be able to do that yourself, though.