Need help with relatively complicated query... 1

[dcusick]

What's up all? I have a query question that I just can't seem to figure out... I have two tables that I'm trying to join in my database.. This is the table layout..

Person Table
-------
personID
firstName
lastName
.
.

Phone Table
--------
personID
typeCode(1 for home, 2 for work)
dialNumber
extension

Basically I want to join the person table with the Phone table and get personID, and both home and work numbers in one line. I have the basic SQL statement to do this, but I've come across some problems...

Here is my statment so far..
SELECT Person.personID, Phone.dialNumber, Phone_1.dialNumber
FROM (Phone RIGHT JOIN Person ON Phone.personID = Person.personID) LEFT JOIN Phone AS Phone_1 ON Person.personID = Phone_1.personID WHERE (((Phone.typeCode)=1) AND ((Phone_1.typeCode)=2));

The problem is that I only get records that have 2 entries, one with a typeCode of 1 and one with a typeCode of 2. If a person doesn't have an both entries, then they don't show up in the results. I want to see these people, just assigning null to the missing value. For instance if a person had a home phone entry and no work phone entry then the record would return personID, homePhone, and NULL. If anyone can help me out here, I'd greatly appreciate it..

Doug

 

[cghoga]

hi Doug.

create a backup copy of your table in case something goes awry

the code to do this from a command button is like this:

'to suppress messages.
DoCmd.SetWarnings False

'Update fields to null.
DoCmd.RunSQL "UPDATE YourTableName SET typeCode = 'NULL'
WHERE typeCode Is Null"

'to see messages.
DoCmd.SetWarnings True

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

Or you can create a query

1 After opening new query and selecting table to query, go to Query - Update Query to set the query as an Update query)
2 in the criteria section under the field typeCode, enter

is null

3 in the Update To section enter

"NULL"


to run it in code

'to suppress messages.
DoCmd.SetWarnings False

'run query.
DoCmd.OpenQuery "YourQueryName"

'to see messages.
DoCmd.SetWarnings True


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

For new records, so you don't get this scenario again, you may look at changing the default value setting of the typeCode field to "NULL". So when a new record is created it sets it to NULL until a value is entered.

hope this helps you.

 

[Norris68]

SELECT personID, (SELECT dialNumber FROM Phone WHERE personID=p.personID AND typeCode=1) As HomePhone, (SELECT dialNumber FROM Phone WHERE personID=p.personID AND typeCode=2) As WorkPhone FROM Person p

 

[dcusick]

Thanks for the responses... Norris68, I think you might be on to what I'm looking for, however I'm getting the following error when tyring to run the query...

At most one record can be returned by this subquery...

Any ideas??

THanks..

 

[lespaul]

That means that one of the subquery's is finding two different phone number records for the same person.

 

[Norris68]

Try changing the subqueries to (SELECT TOP 1 dialNumber...

As lespaul says it sounds like you have duplicate entries per personID/typeCode. TOP 1 will return the first record it gets to.

 

[dcusick]

Yes you are correct... I forgot to mention that people could have multiple work phone numbers... Definitely wish that I created this database now instead of contracting the work out to an outside company!

 

[dcusick]

Norris68, You are the man! That did it... Thanks a lot.. Greatly appreciated!

Thanks again.

Doug