Help needed for a Query

[raaman rai]

Hello everybody, well i have 3 tables created in my access db i.e tblClient, tblProject & tblPayment. The main table is tblClient where Client information is stored with ClientID as the Primary key. Now the other two tables are linked with Client Table using ClientID as the foreign key. Now the situation is that for each client there can be many projects and the payments can also be made in any numbers (times). So, now i need to have a joined query showing the client information, project information & payment information at one go. If the client has 3 projects in hand and made payment only once then the query result should in the following pattern.

[HTML]
CLIENTID PROJECT PAYMENT
1 abcdefghi 1000
1 ihgfedcba
1 sdhfdjfdjff [/HTML]
Please give me a good query.

[nico5038]

For showing "optional" information you need to use the so-called "OUTER JOIN".
Just connect the PK with the FK in the other table and double-click the connectionline. Now chose 2 or 3 to make one of the tables "always show up".
In this construction however you need to realize that two payments will show the client and the project twice.
As I assume that payments are "project related", I would advise to link Client to Project using the ClientID and Project to Payment using the ProjectID, else you could get multiple payments of a client from different projects linked to one project....

Nic;o)

[Scott Price]

You have inadvertently posted your question in the Articles section. I have moved it across for you to the main Forum.

MODERATOR

[NeoPa]

Very interesting question.
Unfortunately (as Nico has explained in his post) it is not possible to have two independent lists appearing in the same query without duplication. If they are logically linked (as he suggested), that would be another matter, but independent lists cannot be worked that way in SQL (You could write fiddly code to do it but it really would be just that - fiddly).

[raaman rai]

Ya practically the tables doesnot have the normalization norms followed. But please note that the clients make payment for 2 or 3 projects at one go and this payment cannot be updated against each of these projects as to avoid duplication. therefore, its mandatory for me to make two tables i.e tblProject & tblPayment to keep the payment and project details seperately. Thus the essence has been found to find out a solution in this case. Please lemme know more

For showing "optional" information you need to use the so-called "OUTER JOIN".
Just connect the PK with the FK in the other table and double-click the connectionline. Now chose 2 or 3 to make one of the tables "always show up".
In this construction however you need to realize that two payments will show the client and the project twice.
As I assume that payments are "project related", I would advise to link Client to Project using the ClientID and Project to Payment using the ProjectID, else you could get multiple payments of a client from different projects linked to one project....

Nic;o)

[raaman rai]

Ya practically the tables doesnot have the normalization norms followed. But please note that the clients make payment for 2 or 3 projects at one go and this payment cannot be updated against each of these projects as to avoid duplication. therefore, its mandatory for me to make two tables i.e tblProject & tblPayment to keep the payment and project details seperately. Thus the essence has been found to find out a solution in this case. Please lemme know more

Very interesting question.
Unfortunately (as Nico has explained in his post) it is not possible to have two independent lists appearing in the same query without duplication. If they are logically linked (as he suggested), that would be another matter, but independent lists cannot be worked that way in SQL (You could write fiddly code to do it but it really would be just that - fiddly).

[NeoPa]

Raaman,

Perhaps you didn't understand what I was saying.
There is no way (certainly that I know of) to produce what you need in basic SQL.
It is possible to create a table, which could later drive a report, by processing through the recordsets in code. This would be very fiddly and I wouldn't begin to try to take you through that at this stage without some indication that you understand the principles of what's involved and are ready to do the work necessary. The code will use concepts outlined in the article Basic DAO recordset loop using two recordsets. Before we proceed you need to show that this is understood and you're ready to continue working at this level. Does that make sense?

[nico5038]

One solution I see is to add a new table "tblProjectPayment" for recording what (part of) a payment is assigned to a project.
Another solution would be to record the TotalPrice in the project table and add a field "FullyPayed". This will allow to record per Client the Amount of Fully payed projects and the Amount payed in total. The difference would be the partial remainder for the next project....

Still a bit odd, as in general Invoices are used for payments and these will have the Requested Payment. There normally a "date payed" is used to indicate no reminder has to be sent.....

Nic;o)