Trying to pull data from a table... I know, not a helpful description... (1 Viewer)

[Sydcomebak]

Assume:

Table "Confusion":
ID  Old_Name  New_Name
1   Augusta   Augusta
2   Barbados  Doral
3   Cape Cod  Doral
4   Doral     Doral
5   Eagle     Jefferson
 
Table "Models":
ID  Model_Name   3_Bed
1   Augusta       Yes
2   Doral          No
3   Jefferson     Yes
 
Table "Homes":
Lot_ID  Old_Name
  1     Augusta   
  2     Barbados  
  3     Cape Cod  
  4     Doral     
  5     Eagle     
 
Table "Listings"
ID  Lot  Price
1    1   $30000
2    2   $25000
3    3   $20000
4    4   $20000
5    5   $20000

OK, someone walks in and says, I want to list my home for sale, I have the "Cape Cod" over on lot#200

I open a form to create a listing in the "Listings" table. I select from the "Old_Name" dropdown and assign Lot 200. The price is $50000

No problem, but now I would like to have a form where I select an "Old_Name" and the results shown are all listings where their "New_Name" is the same.

THese houses are based on models and the model name changes from year to year by the builder even though the floorplan doesn't. So sellers come in all the time saying that they have a particular model, and even though the name has changed, they want it to show the old model name. If a buyer wants to look at similar models, I can look at all listings where the New_Name is the same. This also normalizes the DB a bit because I only have to enter room dimensions, etc once per model type (regardless of what it was called & when)

Any advice?

Thanks a lot!

-Dave


EDIT: I am not very good with Access yet and find myself using mostly the event builder and the row source for creating relationships between tables. If your solution keeps that in mind, I'll probably understand you better. Thanks!