Make one row for every invoice and it’s payment (1 Viewer)

[mobarak ahmed]

I’m now try to mack clients management database for contracting company
It Consists of two parts invoices and payments
Every invoice have unique number
To be considered that every payment should be against specific invoice and not necessary that the client send all invoice at one time let’s have example for clarification
We make contract with the client by 1000,000 USD
And we have invoiced 4 invoices
Invoice number 1 = 200,000
Invoice number 2 = 300,000
Invoice number 3 = 400,000
Invoice number 4 = 100,000
The client has send 150,000 USD against invoice number 3 by check number 68219 on 15/5/2021
On 20/6/2021 he send another payment 250,000 against invoice number 3 by transfer on account number 99222044
Now we have two payment on invoice number 3 by different methods and different date and
I’m now have to tables one for the invoices and one for payments
I want to make query to collect the same information about the invoice and it’s payment
The problem when i want to do this i have now two different payment method on same invoice which i want to make every row for one invoice and it’s payment
It gave me chooses to sum or average or count
What can i do to mack the two different payment on same invoice appear on same row ?
sorry for talking too much and bad explanation

 

[arnelgp]

1. Create a Total query against the payment.
Group by invoice , do not include the date when grouping.(query1)

2. Create another query from invoice table Joining query1 on invoice number.
Show the innvoice, invoice amount, invoice payment.

see Query2 and Query3.

 

[mobarak ahmed]

big thanks ! you are amazing

 

[mobarak ahmed]

1. Create a Total query against the payment.
Group by invoice , do not include the date when grouping.(query1)

2. Create another query from invoice table Joining query1 on invoice number.
Show the innvoice, invoice amount, invoice payment.

see Query2 and Query3.

sorry i can't find Chr(13) or Chr(10) !!

 

[mobarak ahmed]

here is my database if you can solve it i have done everything you've said

 

[mobarak ahmed]

should i change anything in modAzli module ?!

 

[arnelgp]

should i change anything in modAzli module ?!

i already made the changes.

 

[mobarak ahmed]

i already made the changes.

thank you ! I wonder, where was the error?
if you have time to describe !
if you don't i am already appreciated

 

[arnelgp]

you need to see dconcat() function.

 

[mobarak ahmed]

i have two problem now
1- if payment method was the same it appeared once also the date and payment amount
you can see the screen shoot ,
2- it's doesn't appeared in front of each other
like
payment date is 22/6/2021
payment method cancelled
amount 25,000
it appeared 14/6/2021 cancelled 8,200

 

[mobarak ahmed]

Notice i could need to make more than 5 or 6 payments against same invoice

 

[arnelgp]

so it is not obeying the sort order.
i my own custom function AConcat().

see Query3.

 

[arnelgp]

Notice i could need to make more than 5 or 6 payments

you need to Adjust the Row height of the resulting query.

 

[mobarak ahmed]

You Are my Hero a lot of thanks

so it is not obeying the sort order.
i my own custom function AConcat().

see Query3.

 

[mobarak ahmed]

please don't be upset but I've tried to add bank name and check num to query 3
but i failed i don't know why whoever i do the same code
so please do it for me last time if you have time and sorry for Disturb

 

[mobarak ahmed]

or you can learn me how can i do it in order to not bother you ,,

 

[arnelgp]

see Query3 in design view.

 

[mobarak ahmed]

see Query3 in design view.

thank you !

 

[mobarak ahmed]

i have create new invoices but i wondered why it doesn't appeared in query 2 or query 3 ?

 

[mobarak ahmed]

Reminder