Sum first record only from multple results with the same ID number

[sashapixie]

Hi There

I have a query and report displaying a list of projects with a Project ID number as per below:

Project Number Client Total Work 1 Status

111 Client A £600.00 Won
111 Client B £600.00 Lost
111 Client C £600.00 Current

112 Client A £400.00 Won
112 Client B £400.00 Lost
112 Client C £400.00 Current

What I need to work out is how can I show on this report a total sum of the 1 result from these records for example:

Currently the report sums them all giving a result of £3,000 but I want a result from each first line of the new project number giving a total of £1000.

Hope this makes sense, any help is greatly appreciated as always

 

[MickG]

Try this:-
Results in column "E".

[COLOR="Navy"]Sub[/COLOR] MG11Apr02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Not Dn.Value = vbNullString [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 3).Value = Dn.Offset(, 1).Value
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value).Offset(, 3).Value = _
        .Item(Dn.Value).Offset(, 3).Value + Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sashapixie]

Hi Mick

Thanks for your input, this code looks like it is for Excel and not Access, is that correct?

 

[MickG]

Yes, Sorry about that , I did not see the Reference to "Access", hopefully someone else can help you !!!

 

[sashapixie]

Thank you anyway, I think I could do this really easily in Excel, Access is slightly restricting sometimes

 

[alansidman]

Is there any identifier in your table that would indicate the first item in the list other than the location in the query. ie. date? Will the selection always have the status of Won? If so, then in your criteria, place ="Won" for that field.

 

[sashapixie]

The query is pulled from a form with a subform, the subform contains the client names and the status, the values are form the main form. These are linked by the project number but I do not have anything that indicates the first row.

I want the report to show all the information but I also want a sum to show in the report header with the sums.