PLS HAVE PROBLEM IN A FORMULA

[gabh81]

Hello!!!,,,I need an urgent help in a formula.

I have the tables:
[Ship] [Invoice] [Payments]
IdShip (keyfield) ----->IdShip
DateShipped IdInvoice (keyfield) -------> IdInvoice
Port AmountPaid AmountInvoiced

DatePaid


I have a form A, which contains a key field "IdShip". Each IdShip contains
more than one invoice, called IdInvoice (key field too), and each IdIvoice
have several Payments.

So, I have form A with IdShip, with the subform showing different Invoices,
and the subform is nesting the Payments subform as a 2nd subform.

What I want, it is to show in the Form A the total per IdShip for amounts
invoiced and paids.

I already did for the invoiced amounts, using a text box:
SumInvAmt =nz(Sum([InvAmount]),0), I add this formula in the form footer of
Subform1, and then in Form A, I used another text box: =SumInvAmt...and works
fine, adding all the invoiced amounts which belongs to the respective IdShip.

but I'm not able (or I dont know how) to do the second (for the subform2, or
in a way to say, for the sub-subform) ,,,,to do a formula which shows ALL the
amounts for the different payments presents for the differents invoices in a
particular Idvessel.

any,,,any help will be very appreciated. Thanks in advance,

gab

 

[Ken Sheridan]

One way would be to create a query, qryInvoicePayments say, which joins the
Invoices and payments tables and then use the DSum function to return the sum
of all payment amounts for the current IdShip. The query would be like this;
I've assumed, as its seems more logical, that the AmountPaid column is
actually in Payments and the AmountInvoiced column is in Invoices, rather
than the other way round as shown in your post:

SELECT IdShip, AmountPaid
FROM Invoice INNER JOIN Payments
ON Payments.IdInvoice = Invoice.IdInvoice;

Then in form A add an unbound text box with a ControlSource property of:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = " & [IdShip]),0)

This assumes IdShip is a number data type. If its text use:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = """ & [IdShip] &
""""),0)

Ken Sheridan
Stafford, England

 

[gabh81]

Thanks Ken. (by the way, you 're right in your assumption, the thing is that
when I posted it, get disorder the spaces)...continue with the issue, well, I
can;t do it in that way, cause each invoice has several payments (in a
relation one to many)...and by the other hand, each Ship has many invoices
(in a relation one to many too).

Hence, I have now a main form, with a subform, and inside this subform,
another subform nested (like subdatasheet)...and I want two totals in main
form, one showing the total amount invoiced (for the different invoices per
one ship),,,and one showing the total amount paid (for the different payments
done per all the invoices per one ship)...perhaps the Dsum, or Sum formula
works,,,but I'm really lost it or trapped for this.

Any,,,by really any help is most welcommed. thks

gab

One way would be to create a query, qryInvoicePayments say, which joins the
Invoices and payments tables and then use the DSum function to return the sum
of all payment amounts for the current IdShip. The query would be like this;
I've assumed, as its seems more logical, that the AmountPaid column is
actually in Payments and the AmountInvoiced column is in Invoices, rather
than the other way round as shown in your post:

SELECT IdShip, AmountPaid
FROM Invoice INNER JOIN Payments
ON Payments.IdInvoice = Invoice.IdInvoice;

Then in form A add an unbound text box with a ControlSource property of:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = " & [IdShip]),0)

This assumes IdShip is a number data type. If its text use:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = """ & [IdShip] &
""""),0)

Ken Sheridan
Stafford, England

Hello!!!,,,I need an urgent help in a formula.

I have the tables:
[Ship] [Invoice] [Payments]
IdShip (keyfield) ----->IdShip
DateShipped IdInvoice (keyfield) -------> IdInvoice
Port AmountPaid AmountInvoiced

DatePaid


I have a form A, which contains a key field "IdShip". Each IdShip contains
more than one invoice, called IdInvoice (key field too), and each IdIvoice
have several Payments.

So, I have form A with IdShip, with the subform showing different Invoices,
and the subform is nesting the Payments subform as a 2nd subform.

What I want, it is to show in the Form A the total per IdShip for amounts
invoiced and paids.

I already did for the invoiced amounts, using a text box:
SumInvAmt =nz(Sum([InvAmount]),0), I add this formula in the form footer of
Subform1, and then in Form A, I used another text box: =SumInvAmt...and works
fine, adding all the invoiced amounts which belongs to the respective IdShip.

but I'm not able (or I dont know how) to do the second (for the subform2, or
in a way to say, for the sub-subform) ,,,,to do a formula which shows ALL the
amounts for the different payments presents for the differents invoices in a
particular Idvessel.

any,,,any help will be very appreciated. Thanks in advance,

gab

 

[Ken Sheridan]

I see no reason why the expression I gave you won't do it. All you have to
do is create the qryInvoicePayments query using the SQL I sent you and then
call the DSum function in an unbound text box on the parent form as I
described. It simply sums all the payments relating to the IdShip value in
the parent form's current record, which is what you want.

Ken Sheridan
Stafford, England

Thanks Ken. (by the way, you 're right in your assumption, the thing is that
when I posted it, get disorder the spaces)...continue with the issue, well, I
can;t do it in that way, cause each invoice has several payments (in a
relation one to many)...and by the other hand, each Ship has many invoices
(in a relation one to many too).

Hence, I have now a main form, with a subform, and inside this subform,
another subform nested (like subdatasheet)...and I want two totals in main
form, one showing the total amount invoiced (for the different invoices per
one ship),,,and one showing the total amount paid (for the different payments
done per all the invoices per one ship)...perhaps the Dsum, or Sum formula
works,,,but I'm really lost it or trapped for this.

Any,,,by really any help is most welcommed. thks

gab

One way would be to create a query, qryInvoicePayments say, which joins the
Invoices and payments tables and then use the DSum function to return the sum
of all payment amounts for the current IdShip. The query would be like this;
I've assumed, as its seems more logical, that the AmountPaid column is
actually in Payments and the AmountInvoiced column is in Invoices, rather
than the other way round as shown in your post:

SELECT IdShip, AmountPaid
FROM Invoice INNER JOIN Payments
ON Payments.IdInvoice = Invoice.IdInvoice;

Then in form A add an unbound text box with a ControlSource property of:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = " & [IdShip]),0)

This assumes IdShip is a number data type. If its text use:

=Nz(DSum("AmountPaid"," qryInvoicePayments","IdShip = """ & [IdShip] &
""""),0)

Ken Sheridan
Stafford, England

Hello!!!,,,I need an urgent help in a formula.

I have the tables:
[Ship] [Invoice] [Payments]
IdShip (keyfield) ----->IdShip
DateShipped IdInvoice (keyfield) -------> IdInvoice
Port AmountPaid AmountInvoiced

DatePaid


I have a form A, which contains a key field "IdShip". Each IdShip contains
more than one invoice, called IdInvoice (key field too), and each IdIvoice
have several Payments.

So, I have form A with IdShip, with the subform showing different Invoices,
and the subform is nesting the Payments subform as a 2nd subform.

What I want, it is to show in the Form A the total per IdShip for amounts
invoiced and paids.

I already did for the invoiced amounts, using a text box:
SumInvAmt =nz(Sum([InvAmount]),0), I add this formula in the form footer of
Subform1, and then in Form A, I used another text box: =SumInvAmt...and works
fine, adding all the invoiced amounts which belongs to the respective IdShip.

but I'm not able (or I dont know how) to do the second (for the subform2, or
in a way to say, for the sub-subform) ,,,,to do a formula which shows ALL the
amounts for the different payments presents for the differents invoices in a
particular Idvessel.

any,,,any help will be very appreciated. Thanks in advance,

gab