Basic 1:1 query

cmf0106 · #1 11-17-2009, 09:22 AM

Here is the situation, I have two databases that track the amount of lots in a neighborhood. This is in access 2003.

(please see attached files)
"RealLots.xml" This has the correct number of entries at 495 total. In addition, it has the "LOT ID" column which is critical for this join.

"HOA_Entity.xml" This has the incorrect number of entries at 588, so a change in total entries from 588-495= 93, so this database has 93 more entries than I need. BUT it has the "partnership" column which is also critical for the join.

When I query the data, as shown in the image right herehttp://img.photobucket.com/albums/v1...rww/help-6.jpg. Note: the query is doing a 1:1 relationship based on address which is found in both databases. What am I doing wrong?

Many Thanks

ConneXionLost · #2 11-17-2009, 12:16 PM

Hi cmf0106,

Your query is showing you that there are only 470 records "in common" between the two tables when "Address" is used as the join. As you pointed out, "HOA_Entity.xml" has more records (588), but it would seem to have 118 entries that are not in "RealLots2003"; as well, "RealLots2003" has 25 entries that are not in "HOA_Entity.xml".

Cheers,

cmf0106 · #3 11-17-2009, 01:34 PM

Is there a way I could make it such that it displays all 495 records, but have a null value for the 25 with no "common" attribute.

e.g. 470 had a match a "common match", 25 of that 495 did not therefore did not display. I would like it to go ahead and display the 470 common matches + the 25 that did not have the common match to get the 495 total.

Thanks

ConneXionLost · #4 11-17-2009, 03:12 PM

Hi cmf0106,

Yes. While in query design mode, right click on the join (the little black line) and select "Join Properties". Select option 2 or 3, whichever one that says, "Include ALL records from 'RealLots2003' and only those records from 'HOA_Entity' where the joined fields are equal." Click Okay, and run the query.

Cheers,

cmf0106 · #5 11-17-2009, 03:37 PM

Thats good thanks, but is there a way in addition to what you stated to fill the blank cells with <Null> if it doesnt have something in "common"?

ConneXionLost · #6 11-17-2009, 03:52 PM

Hi cmf0106,

Put this in place of "Partnership" in your query:

Code:

Partner: IIf([Partnership] Is Null,"<null>",[Partnership])

cmf0106 · #7 11-19-2009, 10:54 AM

Quote:

Originally Posted by ConneXionLost

Hi cmf0106,

Put this in place of "Partnership" in your query:

Code:

Partner: IIf([Partnership] Is Null,"<null>",[Partnership])

Cheers,

Many thanks, but is there a method to automatically "null" everything in the worksheet in this manner? e.g. not having to specify a particular row or column but rather is null applies to the whole work sheet?

Many thanks

ConneXionLost · #8 11-19-2009, 11:11 AM

Hi cmf0106,

I'm not sure I understand your question, but if you want to remove all the data from the table, then you can just use a delete query such as:

Code:

DELETE HOA_Entity.*
FROM HOA_Entity;

Welcome to the Excel Forum

Forum Statistics