[AccessD] combo from multiple sources

I tend perhaps a little toward the academic on this, Susan, but I strongly discourage stopping one's normalization at 3NF, which leads to constructions such as (in an address table) CityID, StateID and CountryID. This IMO is nonsense. There is only one New York and it resides in New York State, which in turn resides in exactly one country. Therefore in my designs the only column I carry in the addresses table is CityID -- the rest is inferred.

However, there are numerous Springfields in the USA (which, incidentally, is why the name was selected for The Simpsons). And thus the combo-box that uses a query drawing from three tables -- Cites, States and Countries. The actual query would look something like:

Select CityID, CityName + ', ' + States.StateName + ', ' + Countries.CountryName 
FROM Cities Inner Join States on Cities.StateID = States.StateID 
Inner Join Countries on States.CountryID = Countries.CountryID

(Substitute double-quotes for Access. Out of habit I write in SQL.)

 
Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com




----- Original Message ----
From: Susan Harkins <ssharkins at setel.com>
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Sent: Monday, February 19, 2007 3:20:13 PM
Subject: [AccessD] combo from multiple sources


I made a mistake on an article title "How to populate combo box with data
from multiple sources" 

I meant something entirely different, but now I'm wondering -- do any of you
use such a technique? Doing it isn't a problem. I just can't see how it
would be useful, but if there's a good excuse, I'm always game. 

Susan H. 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com