Total in a query

[matthewR]

Is there anyway in a query to do something like the following:

Clientcontracts:total([FA_Total_Contracts]for[Client_Number])

I have a field - FA_Total_Contracts - that gives the contracts for each group. These groups are under one client. I would like to have a field that shows the contracts for the whole client or each client.

It looks something like:

Code: Select all

Client                     Group              Contracts        contracts per client
100                            1                     50                         123
100                            2                     40                         123
100                            3                     33                         123
200                           11                      5                          65
200                           12                     10                          65
200                           13                     50                          65

Can this be done in a query?

[HansV]

You could use this definition:

Contracts per client: DSum("Contracts","NameOfTable","Client=" & [Client])

Subtitute the name of the table for NameOfTable. I have assumed that Client is a number field.

[matthewR]

I am getting an error. I am using the following:

Contracts per client: DSum("FA_Total_Contracts","TC_Workload","Client_Number=" & [Client_Number])

The Client_Number is a text field.

[HansV]

For a text field, you need a slightly different version, since text values must be enclosed in quotes:

Contracts per client: DSum("FA_Total_Contracts","TC_Workload","Client_Number=" & Chr(34) & [Client_Number] & Chr(34))

Chr(34) is the double quote character ".

[matthewR]

Thanks Hans - that worked perfectly.