Simple Access Help.. null values

[Roccapriore]

Sorry if I'm repeating other questions, I searched your boards and didn't come up with anything.

I have (what I think) is a pretty simple question...

In Access.. I'm trying to query from two tables at once.

Example: TableNames and TableCity

Not all records listed in TableNames are accounted for in TableCity.

Say Names has 100 records, and City only has 50. Is there a way to query both, and have the City show up as null/blank if there is no association with the Names table?

Please let me know if you have the time. Thanks!!

[zepper]

Welcome to the TechIMO forums.

Are the TNames and TCity tables part of the same database or different databases? If part of the same database, then this may be a case of "over normalization". When storage space was at a premium, very fine normalization might have been justified. That is no longer the case.
. How do the records in TNames relate to the records in TCity, or is there no detectable relationship? If there is some sort of relationship, create a linking table based on the relationship.
. Perhaps the data entry forms or scripts were not designed correctly. If there is a relationship, the data entry method should have included that - with the City table as a lookup or something when entering the info into the Names table.

.bh.

[Roccapriore]

Same database.

Here's the short story..

I have a large database with a bunch of people in it.. and each individual is assigned a NameID.

There are a bunch of other tables too.. using TCity as an example.. in there is a list of Citys, and each is assigned a CityID.

The main table has a field for CityID, which is related to the TCity table.

I can query the main table for the "CityID", but that just spits out a bunch of unusable numbers. The actual names of the cities which relate to the ID are in the TCity table, and when I use that to query the names it limits the outcome to only the individuals in the TCity table.

Example:
(in the names table)
Name CityID
John 4353456
Dave 232452
Sue (null)


If I query using both the tables (So I can see "New York" as opposed to 4353456).. all I get are the first two names because there is no value associated with Sue.

I hope I made sense. Thanks for the quick reply!!!

[zepper]

If value="" (null) then use "Not Available".
You get the idea, but you'll have to find out the way Access does it. There are forums out there specifically for Access and I think a specific section on the Tek-Tips site.

.bh.

[Acid_ICE24]

Example:
(in the names table)
Name CityID
John 4353456
Dave 232452
Sue (null)


If I query using both the tables (So I can see "New York" as opposed to 4353456).. all I get are the first two names because there is no value associated with Sue.

try this query:
"SELECT a.Name, b.CityID
FROM TableNames a
LEFT JOIN TableCities b
ON a.CityID = b.CityID"

hope this helps.