List Box total values (1 Viewer)

[Groundrush]

Anyone know how to get the total value from a field in a list box please?

I have text boxes on the main form above the list box & can't work out how to get the totals in the text boxes above like I've typed them in the sample above

Any ideas?


Thanks

 

[gemma-the-husky]

you probably need to run a summary query of the source of the list box, and populate the total boxes with that query

this is easier (and quicker probably) than writing a for each loop to iterate the listbox.

 

[Groundrush]

Thanks for your reply

I have created seperate query to sum up the hours, the problem is trying to link it to to the main form once you've selected a record to report on using the list box & calandar for the start & end dates

Here are my Queries that I'm working on....

Main query that the list box is based on (qryConcept39HrsReport)

SELECT dbo_F_RESOURCE.RES_ID_NUMBER, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC AS Trade, dbo_F_TASKS.TA_TASK_ID AS [Job No], dbo_FLOCATE.BG_SITE AS Property, dbo_F_TASKS.TA_SHORT_DESC AS [Job Details], dbo_F_TASK_TIME.TT_STARTED AS [Date Started], dbo_F_TASK_TIME.TT_NORM_TIME AS Basic, dbo_F_TASK_TIME.TT_OVR_TIME1 AS [Band 1], dbo_F_TASK_TIME.TT_OVR_TIME2 AS [Band 2], dbo_F_TASK_TIME.TT_OVR_TIME3 AS [£0-75], dbo_F_TASK_TIME.TT_TRAV_TIME AS [£0-50], dbo_F_TASK_TIME.TT_PARKING_TIME AS £2
FROM ((dbo_F_TASK_TIME INNER JOIN dbo_F_TASKS ON dbo_F_TASK_TIME.TT_FKEY_TA_SEQ = dbo_F_TASKS.TA_SEQ) INNER JOIN dbo_FLOCATE ON dbo_F_TASKS.TA_FKEY_BG_SEQ = dbo_FLOCATE.BG_SEQ) INNER JOIN dbo_F_RESOURCE ON dbo_F_TASK_TIME.TT_FKEY_RES_SEQ = dbo_F_RESOURCE.RES_SEQ
WHERE (((dbo_F_TASK_TIME.TT_NAME)=[forms]![frmConceptTimesheetReport]![lstOperatives]) AND ((dbo_F_TASK_TIME.TT_STARTED) Between [forms]![frmConceptTimesheetReport]![TxtStartDate] And [forms]![frmConceptTimesheetReport]![TxtEndDate]))
ORDER BY dbo_F_TASK_TIME.TT_STARTED;

Query to sum up the results in each field (qryConcept39TotalHrsReport)

SELECT qryConcept39HrsReport.TT_NAME, Sum(qryConcept39HrsReport.Basic) AS SumOfBasic, Sum(qryConcept39HrsReport.[Band 1]) AS [SumOfBand 1], Sum(qryConcept39HrsReport.[Band 2]) AS [SumOfBand 2], Sum(qryConcept39HrsReport.[£0-75]) AS [SumOf£0-75], Sum(qryConcept39HrsReport.[£0-50]) AS [SumOf£0-50], Sum(qryConcept39HrsReport.£2) AS SumOf£2
FROM qryConcept39HrsReport
GROUP BY qryConcept39HrsReport.TT_NAME;

The problem I have is that you can't refer to a query in a text box so I don't know how to pull the required results through.

 

[gemma-the-husky]

i see what you mean. I would save the summary query, and then do a dlookup from the summary to populate the boxes - or open the summary query with a recordset and do the same.

i am not used to using sql directly, i suppose you would have to have an individual sum query attached to each of the textboxes and populate these at the same time as you populate the list box - ie with the load or with the current event

 

[Groundrush]

i see what you mean. I would save the summary query, and then do a dlookup from the summary to populate the boxes - or open the summary query with a recordset and do the same.

i am not used to using sql directly, i suppose you would have to have an individual sum query attached to each of the textboxes and populate these at the same time as you populate the list box - ie with the load or with the current event

I've sussed it...at last

I just created another lst box with the totals from the second query & it works fine.


thanks for your help.

 

[gemma-the-husky]

have you displayed in red on white like your pic, or did you have to accept default colours

 

[___]

Use a subform rather than a listbox and put the Text boxes in the subform footer to calculate the relevant columns. You could format the subform to look just like a listbox if prefer that look.

 

[Groundrush]

have you displayed in red on white like your pic, or did you have to accept default colours

I only used that to highlight the area that I was working on, but you can change colours to match the red with white background.

I think I'm going to stick with red font but the same background as the main form.

Use a subform rather than a listbox and put the Text boxes in the subform footer to calculate the relevant columns. You could format the subform to look just like a listbox if prefer that look.

I need it to be a list box because there is related data that I plan to view via another form that will display data when a record is selected by the onclick event of the list box.

 

[___]

I need it to be a list box because there is related data that I plan to view via another form that will display data when a record is selected by the onclick event of the list box.

So it's not a multi-select listbox? Then a command button could be used in the detail line of the subform to open your other form at the selected line ID.

 

[Groundrush]

So it's not a multi-select listbox? Then a command button could be used in the detail line of the subform to open your other form at the selected line ID.

I'll have a look into that......thanks