Sum Query and text box (3 Viewers)

[JPR]

Hello,

I am having some problems in creating Sum queries for my db.

My db has a table named Clients with 3 fields:

- Clients
- Paymentdate
- Paymentamount

My db has also a form form with a combo box (cboclients) used to select the client's name and two text boxes (txtTo and txtFrom) used to select the range of dates of the payments.

On the same form a cmdbutton runs without any problems a generic Sum query based on the table Clients, whith the fields PaymentDate and Paymentamount. This query returns the total amount of payments done by all the clients.

I would now like to create a spcific Sum query with the fields PaymentDate, Paymentamount and Clients in order to have the total amount of payments done by each clients selected in the cbobox.

I would appreciate your help in designing this query and eventually if it would be a good idea to use a text box to display the results of both queries.

Thank you

 

[Gasman]

Group on Client then.

 

[JPR]

Group on Client then.

Works great.

Is there also a way I could use a text box to display the result of these two different queries? Thank you

 

[Gasman]

Works great.

Is there also a way I could use a text box to display the result of these two different queries? Thank you

I do not think so? I would use a subform for all the records from the second query. So each value would be in a textbox.
The output from the first query could be in the main form.

 

[Ken Sheridan]

I would now like to create a spcific Sum query with the fields PaymentDate, Paymentamount and Clients in order to have the total amount of payments done by each clients selected in the cbobox.

Is the Clients column of text data type, containing the client's name? If so the table should be decomposed so that it contains a ClientID foreign key column of long integer number data type, referencing the primary key of a Clients table in an enforced relationship.

A query to return the detailed payments data along with the total payments per client would then be like this:

PARAMETERS Forms!YourForm!cbClients LONG,
Forms!YourForm!txtFrom DATETIME,
Forms!YourForm!txtTo DATETIME;
SELECT Clients.ClientID, ClientName, PaymentDate, PaymentAmount,
    (SELECT SUM(PaymentAmount)
     FROM Payments AS P2
     WHERE P2.ClientID = P1.ClientID) AS TotalPayments
FROM Payments AS P1 INNER JOIN Clients
  ON P1.ClientID = Clients.ClientID
WHERE P1.ClientID = Forms!YourForm!cbClients
  AND PaymentDate >= Forms!YourForm!txtFrom
  AND PaymentDate < Forms!YourForm!txtTo + 1

Note how the date range is defined as on or later than the start date and earlier than the day following the end date. This is safer than a BETWEEN…AND operation as it allows for PaymentDate values with a non-zero time of day element as the last day of the range. You cannot discount the existence of such rows with complete confidence unless you have specifically prevented such values in the table definition by means of a Validation Rule which constrains the column to integer values only.

 

[JPR]

Is the Clients column of text data type, containing the client's name? If so the table should be decomposed so that it contains a ClientID foreign key column of long integer number data type, referencing the primary key of a Clients table in an enforced relationship.

A query to return the detailed payments data along with the total payments per client would then be like this:

PARAMETERS Forms!YourForm!cbClients LONG,
Forms!YourForm!txtFrom DATETIME,
Forms!YourForm!txtTo DATETIME;
SELECT Clients.ClientID, ClientName, PaymentDate, PaymentAmount,
    (SELECT SUM(PaymentAmount)
     FROM Payments AS P2
     WHERE P2.ClientID = P1.ClientID) AS TotalPayments
FROM Payments AS P1 INNER JOIN Clients
  ON P1.ClientID = Clients.ClientID
WHERE P1.ClientID = Forms!YourForm!cbClients
  AND PaymentDate >= Forms!YourForm!txtFrom
  AND PaymentDate < Forms!YourForm!txtTo + 1

Note how the date range is defined as on or later than the start date and earlier than the day following the end date. This is safer than a BETWEEN…AND operation as it allows for PaymentDate values with a non-zero time of day element as the last day of the range. You cannot discount the existence of such rows with complete confidence unless you have specifically prevented such values in the table definition by means of a Validation Rule which constrains the column to integer values only.

Thank you for your help and time. I will give it a try.

 

[arnelgp]

if you want to show the 2 Sum values in your form, add 2 Unbound textbox.
for the Total Payment, put this as Controlsource:

=DSum("PaymentAmount","PaymentTable", "PaymentDate >=#" & Format$(Nz([txtFrom],1),"m/d/yyyy") & "# And PaymentDate <= #" &  Format$(Nz([txtTo],2958465),"m/d/yyyy") & "#")

for the Total Payment for a particular client:

=DSum("PaymentAmount","PaymentTable","PaymentDate >=#" & Format$(Nz([txtFrom],1),"m/d/yyyy") & "# And PaymentDate <= #" &  Format$(Nz([txtTo],2958465),"m/d/yyyy") & "#  And ClientID = " & Nz([cboClients], 0))

 

[JPR]

if you want to show the 2 Sum values in your form, add 2 Unbound textbox.
for the Total Payment, put this as Controlsource:

=DSum("PaymentAmount","PaymentTable", "PaymentDate >=#" & Format$(Nz([txtFrom],1),"m/d/yyyy") & "# And PaymentDate <= #" &  Format$(Nz([txtTo],2958465),"m/d/yyyy") & "#")

for the Total Payment for a particular client:

=DSum("PaymentAmount","PaymentTable","PaymentDate >=#" & Format$(Nz([txtFrom],1),"m/d/yyyy") & "# And PaymentDate <= #" &  Format$(Nz([txtTo],2958465),"m/d/yyyy") & "#  And ClientID = " & Nz([cboClients], 0))

 

[JPR]

Hello,
appreciate your help. Unfortunately I am getting a character or syntax error code when copying the code in the textboxes.
I am attaching a sample of my db. Than kyou