Looking to merge two queries but join results not what I want

[IrishRed]

Thanks, in advance for the help.

Here is the question, I have two queries one called QryMed and the other
called QryRx. I want to merge the two so that I have one listing for each
unique SSN and DOB combination. The problem comes in when I do the join, I
have to join on both SSN and DOB. I am using the join to only show where
records are equal and I know that is not right but I can't join all from one
query because I will have the same problem. I need the match to be on both
but the entries may be in one qry and not in the other (they have Med but not
Rx). So, when I do that join for a scenario like below

QryMed
SSN DOB Med Ded
123456789 19570613 125.00
123456789 19290718 255.00

QryRx
SSN DOB Rx Ded
123456789 19570613 384.37


I end up with just the one entry:

SSN DOB Med Ded RxDed
123456789 19570613 125.00 384.37

and I lose the entry from QryMed for the 19290718 DOB because there is not a
corresponding record in the QryRx.

Is there an easy way to resolve this that I am just not thinking of ?

 

[Albert D. Kallal]

Thanks, in advance for the help.

Here is the question, I have two queries one called QryMed and the other
called QryRx. I want to merge the two so that I have one listing for each
unique SSN and DOB combination. The problem comes in when I do the join, I
have to join on both SSN and DOB. I am using the join to only show where
records are equal and I know that is not right but I can't join all from
one
query because I will have the same problem. I need the match to be on both
but the entries may be in one qry and not in the other (they have Med but
not
Rx). So, when I do that join for a scenario like below

QryMed
SSN DOB Med Ded
123456789 19570613 125.00
123456789 19290718 255.00

QryRx
SSN DOB Rx Ded
123456789 19570613 384.37


I end up with just the one entry:

SSN DOB Med Ded RxDed
123456789 19570613 125.00 384.37

and I lose the entry from QryMed for the 19290718 DOB because there is not
a
corresponding record in the QryRx.

Is there an easy way to resolve this that I am just not thinking of ?

 

[Albert D. Kallal]

I would asume you drop in qryMed, and then drop in qryRx, and join

 

[Albert D. Kallal]

It is strange that you have the same social insurance number, but the birth
date is different?

In these types of designs I usually use internal auto number and join on
that. (I don't use a compound key on two fields). However obviously this is
not your case, and perhaps you don't have control over the design of the
database and the information being given to you anyway.

You could try double clicking on each of the join lines in the query builder
and change them to a left joins

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

However to be honest, I've never tried this with a compound primary key, or
two join lines as you have.

If you left join on both join lines, the above might work However it might
also mean the query will return all child records with same SSN and not
"match" on the DOB. You're simply going to have to give the above
suggestion a try.

It the above idea don't work, then perhaps we can come up with a sub query
that does the trick.

 

[IrishRed]

Hi,
Thanks for responding. Yes, I could understand you thinking it's strange to
have SSN with different DOB. The SSN is used as an account number if you will
and the person holding the account can have a spouse and children. So, they
all use the same account number but their DOB gives us the actual family
member.

I did try the left join and that does return most of the data except where
the person is not listed on the left join table.

I will start researching a sub query now. Please let me know if you have any
other suggestions.

Thanks again.

 

[Albert D. Kallal]

sorry about the accidental posting here....

I did try the left join and that does return most of the data except where
the person is not listed on the left join table.

The above is incorrect and you not done a left join in the query builder
then.

at worst, a left join will return TOO many records....

Your "left" side is your main table, and they should all display regardless
if child records exist..

---------------


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
AppleBee
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
important.

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

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

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
report.

 

[Sal]

sorry about the accidental posting here....


The above is incorrect and you not done a left join in the query builder
then.

at worst, a left join will return TOO many records....

Your "left" side is your main table, and they should all display regardless
if child records exist..

---------------


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
AppleBee
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
important.

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

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

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
report.