Totalling time...

[and]

Hi - Can anyone help me with this?

I have a DB recording clients that register with a jobs placement scheme. Client data is stored in Client_Table fed by the Client_Form. Each time an advisor interacts with the client, the interaction is recorded in an Interactions_subform on the main form, with the interactions data being stored in a separate Interation_table which has the common field ClientID (same field as the main Client_Table).

I need to be able to see a running total of how long each client as been seen by the advisor. An advisor can enter the duration of an interaction into the subform in hours:mins. How can I do this so that I can see on the main form a running total for each client of the total time of interactions they have had so far?

I know this kind of Q has been dealt with before alot but I have just confused myself more looking through all the past posts.....!

If anyone can tell me the general approach for this and help me a little with coding that would be V.V.V appreciated.

TIA.

Andrew.

 

[xuereveds]

Hi Andrew,

I presume the agent picks the client from some sort of combo box or types into text format so in the afterupdate event of these you could write a simple SQL statement that will get the information you require something like

StrSQL = SELECT Sum(Interaction_Table.Time) AS TotalTime
FROM Interaction_Table
GROUP BY Interaction_Table.CLIENT_ID
HAVING (((Interaction_Table.CLIENT_ID)=5127));

of course you will have to set a reference to your db ie set db = currentdb etc

the SQL above should give you the one value you are looking for the one above has no formatting which you may need to add and you can assign that value to a testbox or label on your form which will show the total time of interactions with that client.

Hope that helps.

Scott.

 

[xuereveds]

Andrew just read one of your other threads and noticed a point you made about keeping it simple so here's an easy way to do it create a sub form that has two fields client id and total time......with this for set the the control source to be this.

SELECT Interactions.clientid, Sum(Interactions.Time) AS TotalTime
FROM Interations
GROUP BY Interactions.clientid;

you can then set your controls to clientid and TotalTime when you have this sub form in your main form make the link child field = client id that the user selects and the total time will show time elapsed for that client.

Hope thats easier.

Scott.

Leadership and learning are indispensable to each other.
John F. Kennedy November 22 1963

 

[and]

Thanks Scott.

The Advisor will find a client by searching for them by name (text input) into a search field.

I've never used any SQL before. If I have textbox on my form to display this value, do I just ascribe it's control source as the SQL function you gave me? .....or if not, where do I actually write the SQL?

What do you mean by "set a reference to your db ie set db = currentdb etc" ? How do I do this?

Andrew.

 

[xuereveds]

Hi Andrew,

By the refernece comment that would be if you are creating a recordset to pass the value into. I think a sub form is def. the easiest way for you to go.

After update of the text search field i presume the clients id will be shown in another control....set your sub forms link child to this control and set the record source to be the SQL resembling the above.

Alternitively if you were looking to do it the other way and write your SQL on the fly then you would have code resembling something like this.


Dim db as database
Dim recset as recordset
dim StrSql as string

StrSql = "YOUR SQL STATEMENT like the ones above."
set db = current db
set recset = db.openrecordset (StrSql)


your SQL should resemble the following

StrSQL = SELECT Sum(Interaction_Table.Time) AS TotalTime
FROM Interaction_Table
GROUP BY Interaction_Table.CLIENT_ID
HAVING (((Interaction_Table.CLIENT_ID)=5127));



This will open your recordset and allow you to look at values in that set.......the SQL above will only return one value when you have that value you assign it to your control on the form.

hope that makes sense mate

Scott.






Leadership and learning are indispensable to each other.
John F. Kennedy November 22 1963

 

[and]

Thanks for your help on this Scott.....All is becoming clearer. My subform solution seems to be coming together. Will contact on this thread again if I run into trouble....which I'm sure I will.
Cheers!.
Andrew.

 

[and]

Hi Scott - I don't think I am doing this right - I am a little confused....

I have an Interactions_subform in my main form. ClientID is the linking field. I have a Totaltime_Subform within the Interactions subform with two fields: ClientID (linking) and totaltime - Do I just set the Controlsource of the textbox totaltime to:

SELECT Interactions.clientID, Sum(Interactions.Duration) AS totaltime
FROM Interactions
GROUP BY Interactions.clientID;

Or is it wrong to nest my subforms like this - for get this right do I just need my main form and one subform?

I am confused about where I actually place the statement above: In Expression Builder, in the VB code view, or just in the control source in the properties window? As you can see, I am not really 'getting' Access as yet....feeling rather stooopid....

Any further help greatly appreciated!
Thanks,
Andrew.

 

[xuereveds]

Hi mate,

I'm not too sure about nesting the sub forms i have only ever worked with one subform on the main form unless i am using a multipage control. I think it should be okay though just work on the same principle

The SQL goes in as the Record Source in the forms properties window and as long as you have the link fields set up okay this will update when you pick a new client dont forget to refresh the form to show the new information.

Is that any clearer?

access can be a nightmare cant it!

good luck.

Scott.

Leadership and learning are indispensable to each other.
John F. Kennedy November 22 1963