Discuss "update Query" in the Microsoft Access forum.

Anyone care to take up the task of showing how to do an update query

example is:

TblContacts
ContactID (Primary Key)
Name
ContNumericalFactorField (field to be updated)

TblCalls
CallsID
ContactID (Foriegn Key)
CallDate (Field to be used to update)
CallBackDate (Field to be used to update)


Relationship:
TblContacts (1) --- (Many) TblCalls


I want to subtract the last 10 record's CallDate from CallBackDate. Sum them all and then take that number and update TblContacts [contNumericalFactorField]

Thanks in advance who ever wants to answer.

Sorry, but I just don't understand that. Could you provide a simple example of what you want to subtract and sum?

Just in case you didnt understand what I tried to convey ill go over it. The blue underlined words are the title of tables.

ContNumericalFactorField is a field that I want to update but it is in the master table. It is a field in tblcontacts which is the "contact table." The contacts will be recejving many calls from the user. These calls will be logged in the tblcalls table "calls." This is also the child table. When the user makes a call to that contact (logging a call) the day will be logged as call date. When the contact calls back it will be logged as callbackdate field in the calls table. I want to subtract that amount of time during each record over the last 10 calls, add those days together and then update ContNumericalFactorField in the master table. Hope that helped and thank you.

Hmmm..just trying to understand here. Say you have a called name Bob, whose initial call in on 1 JUL. He calls back on the following dates:
3 JUL
5 JUL
6 JUL
8 JUL
13 JUL

Now, you say you want to take the number of day between each call and add them together(?)
1 JUL - 3 JUL 2
3 JUL - 5 JUL 2
5 JUL - 6 JUL 1
6 JUL - 8 JUL 2
13 JUL - 8 JUL 5
Total 12 days

If I understand this correctly, how does this differ from comparing the most recent call back with the original (or the 10th most recent in you case)?
13 JUL - 1 JUL = 12?

If this is the case, I don't understand the need to sum up the separate differences.

Also, one of the fundamental rules of database design is that you don't store calculated values in the database that can be recreated from the data already stored. In this case, the number of days between call n-10 and n can be calculated when the data is retrieved and reported.

[EDIT] OK, on a second and third read, I see that you are looking for the number of date between the Call and the Callback for each record TblCall table. Easy enough with the datediff function. Still, this is a calculated value and should not be stored in the database. The query would be something along the lines ofYeah, thats only part of the query, but I'm not an Access expert so I'm sure how you get the TOP 10 records -- but that's at least the idea fo the sub-query needed to JOIN with the TblContact Table[/edit]

__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

Ok I am told that knowing the point for a database is the most important thing to consider when designing one. The point for the number assigned to the customer is to assign a seriousness factor to them. When a salesman is servicing his prospects and clients (customers) he only has so much time to be productive. So the more people he pays attention to the more chances at "winning business." This is why salesman are the people who make the most money in the world. It is also why they are some of the most stressed. And so, when dealing with many people, a good contact management system would incorporate some factor that would how important and how urgent the customer is to buy. So when I "close" the clients info I want it to routinely update this "factor" so then when I "call" a list of tasks the clients who are worth more to the salesman are called first and then prospects who aren’t so serious are ignored for longer (as they may actually prefer). A good way to evaluate them is to judge how long it took them to call back.

I am new to all this so I am unsure what can be done and what cannot be.

Now for what I want

I call Bob

Jul 1 -

I wait for the call back. if he calls 2 times than the first call satisfies that first instance. The second call would be considered initiated by him. Though logged, it would be returned by me. As you can probably tell that I can’t be drumming up all these complex queries at time of need of many names. I have devised that it should probably be merely a "number" (one field) used to quickly evaluate all the subjects at once. That would be tax the system’s performance and would in turn negate need.

Now if I initiate 2 calls and then Bob calls back. His call should satisfy both of those calls. And so should my call back to him after two calls. If I called Jul 3 and then he got back to me jul 5 then he would be judged to have 2 and 4. I picked to sum them because I just wanted a quick easy calculation to “update” but in all reality it will probably be more complicated than that and more like an average.

So what do you think? I apologize for how long it took to get back with you. I don’t always get the chance to screw around on the puter. Thanks though for the help.

Sorry bout all the run-ons, never really did shed that habit, hope you can follow and thanks, ha ha

One other thing

If I did use an append query to update the record how would I automate so that it doesnt ask me that question "you are about to append......"

You have 2 Options that i can think of right now.

In your command button on click event you can add

Or

Make your query part of a Macro, setting warnings off before first. You can then call the Macro from your command button.

__________________
If i helped you, make me famous by clicking the

Thanks. I am a little unclear though how I would really do what you said. I understand that that is code but I dont know where I would write that. Did you also see what I asked above in response to wolffy? He exclaimed that I do not want to include in on a table data that could be calculated. What do you think about my needs? would you mind explaining a little about what that code means or does?

Let me get this straight.....

I see this in two columns (well, sorta)

Left side, we'll call the "saleperson", right side we'll call the "lead"

The salesperson will keep calling, logging all his attempts on the left side, and leaving a blank date on the right side. Now, the salesperson could call like 14 times leaving 14 blank dates on the right, when the "lead" decides to call back. When he(the lead) does(call back), all 14 blanks will be filled in with the final call back date, correct?

Obviously a lead with 14:1 callback ratio will be ranked lower than a lead with a 3:1 callback ratio.

Is this what you're trying to figure out? The strength of the leads?