473,847 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help needed for a Query

114 New Member
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.
Oct 13 '07 #1
7 1339
nico5038
3,080 Recognized Expert Specialist
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)
Oct 13 '07 #2
Scott Price
1,384 Recognized Expert Top Contributor
You have inadvertently posted your question in the Articles section. I have moved it across for you to the main Forum.

MODERATOR
Oct 13 '07 #3
NeoPa
32,584 Recognized Expert Moderator MVP
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).
Oct 13 '07 #4
raaman rai
114 New Member
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)
Oct 15 '07 #5
raaman rai
114 New Member
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).
Oct 15 '07 #6
NeoPa
32,584 Recognized Expert Moderator MVP
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?
Oct 15 '07 #7
nico5038
3,080 Recognized Expert Specialist
One solution I see is to add a new table "tblProjectPaym ent" 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)
Oct 15 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2971
by: Erich Trowbridge | last post by:
Has anybody seen this tool? It is awesome. check out http://vw.vermeer.org/ . It's a php front end for large-scale syslog deployments. It makes managing syslog in large networks a snap. The idea is to fifo pipe network syslog into a MySQL database backend. The php scripts reference the database, and print stuff to the screen. My Info -> RH9.0 , MySQL 4.0.14, apache1.3.28 I have successfully gotten everything installed, compiled, etc....
1
2532
by: Ralph Freshour | last post by:
I'm not sure the follow multiple table query is the right way to do what I need to do although it seems to be working: $php_SQL = "SELECT * ". "FROM basics, personal, photos ". "WHERE basics.member_name = personal.member_name ". "AND basics.member_name = photos.member_name ". "AND basics.account_creation_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
4
10241
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline View Query: Select Sq from ( Select substr(to_date(End_Date,"DD-MON-YYYY"),4), End_Date, Rank() Over (Partition by substr(to_date(End_Date,"DD-MON-YYYY"),4) Order by End_Date) As Sq
9
3143
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
28
3312
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical make-believe challenge in order to avoid confusing the issue further. Suppose I was hosting a dinner and I wanted to invite exactly 12 guests from my neighborhood. I'm really picky about that... I have 12 chairs besides my own, and I want them all...
6
2430
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18. I need a query that will return the c date for TypeID 18 if it exist else the date for TypeID 1, for all jobs. the table structure is the following Job TypeID
5
1927
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
7
2200
by: Aaron | last post by:
Complete code follows. I am new to .NET programming (and programming in general) and I am having a difficult time understanding how to fill a variable in one sub, and then access it from another. I have tried declaring them as shared, public, friend, etc and I always get an error stating that something is not valid on a local variable declaration. For example, in the following code for Sub DataGrid_Select, I have CurrentID and...
6
6440
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the Autoexec Macro looks like the way to go. Could someone please assist? Thank you very much! Mike
0
2461
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself). First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon...
0
9887
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10707
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10341
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7883
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5719
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4529
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3164
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.