473,890 Members | 1,833 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Simple Query question

I created a person table with various fields such as Suffix,
Salutation, etc, Some of these fields may not be mandatory for example
suffix. In the actual table itself, I only have a field for suffix ID
where 1=Phd, 2= MD. To display all of these to the user, I created a
form with an underlying query. The problem I am encountering is this,
when we have an empty field, for example where ID="", the query returns
nothing. How do i work around this?


Jan 22 '06 #1
2 2401
Use a left join...

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.


tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
Jan 22 '06 #2
Thanks Albert. I changed the relationship type and it worked.

Jan 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Maciej Nadolski | last post by:
Hi! I`ve got a simple question but I`m puzzled:( When I create variable: for example $query for query to MySQL its obvieus that I want to use variables. Now should I do something like that: 1) $query = "blahblahblah".$variable1."blahblahblah" ."blahblahblah".$variable2."blahblahblah"; OR 2) $query = "blahblahblah."$variable1."blahblahblah" ."blahblahblah."$variable2."blahblahblah";
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example program #include <list>
by: Eddie Smit | last post by:
field- field- surname town ---- ---- john NY john Vegas john Boston eddie Boston eddie New Orleans eddie NY
by: Richard Hollenbeck | last post by:
For example, one college course has only 24 students in it, but the following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs. When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs. I can't find anything wrong with the code. Here it is: Option Compare Database Option Explicit 'global variables
by: Saber | last post by:
I did a lot of searches and read something about datagrids. But I couldn't find the answer of my simple question, how can I show only my desired columns of a table? for example I wrote this sql query: OleDbDataAdapter1.SelectCommand.CommandText = & _ "Select illNameE From tblIllness" OleDbDataAdapter1.Fill(DsIllness1) But in my datagrid, I get (null) for other ccolumns instead
by: Steven Blair | last post by:
Query application made simple? I have to use ASP.NET quite often to knock up quick protype applications. Generally, these applications have some components for querying and an area of screen for displaying the results. Using GridViews and SqlDataSource, I can almost make the application with no code. My where clause is handled by the SqlDataSource. What I would like to know is, can the SqlDataSource be made to be
by: psuwebmasters | last post by:
I am doing some work developing a OneBox for a Google Mini. All I need to do is take a $_GET values from the Mini (in this case, specifically "query"), format it into a URI to pass off to another script, get the results from that script in XML, and hand that XML back off to the Mini. So basically it acts as a go-between, and simple translator for the initial request. So, the OneBox sends a request like:...
by: deejayquai | last post by:
Hello Simple one this I guess, but I'm quite stuck at the moment. I would like to update the records displayed in my listbox (lstStudents) using criteria selected from my combo (cboForm) in a form. My basic code is:
by: muddasirmunir | last post by:
i have a simple query and does not getting desire results which i want i am using vb6 and access i had a table with with 8 fields but just to simplyfy by question i am just supposing to four. suppose i had a table with field Name----------City----------Type----------Value
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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,...
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...
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();...
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...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
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.