Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    1,215

    * Access database design help

    Hey everyone, I haven't used access for a while and i'm struggling to work out how to solve this problem.

    Ok, i'm building a database for an estage agent. Main tables will be the property table (address, bedrooms, garage etc) purchaser table (details of the purchaser such as name, address, telephone number) vendor table (details of the person selling the property).

    Ok, so the person who is interested in buying a property registers their details with the estate agent (walk into a shop, the staff type in their details) and then they are added to the system, they must also register details of the property they are interested in e.g 4 bed, semi. (not sure where to store this information)

    Same principle applys with the vendor, they register their details and also the details of the house they are selling.

    The problem I have is, the system should provide information on any new properties that are added to the system that match the potential purchasers criteria. E.g I walk into the shop and the staff add my details, e.g I want a 3 bed house, no garden, 2 reception rooms. It should then be able to query the database and pull out any suitable matches.

    I'm just really stuck on how to go about doing this....Any help would be appreciated.

  2. #2
    Join Date
    Mar 2004
    Posts
    1,303
    have you come up with all the table properties yet?

    it would be easier to see your tables.

    ---
    i came up with mine like this, i am sure someone might want to revise it.

    Properties (pid, address, bedrooms, garage, ....) --> id is primary key for properties
    Purchaser (purid, name, address, phone ) --> purid is primary key for purchase
    Vendor (vid, ...detail the vendor ) --> vid is the primary key for the Vendor
    Purchaser_Order (oid, purid, pid, vid) --> oid is the primary key for order, purid is foregin key for purchaser; pid is the foregin key for Properties; and vid is the foreign key for Vendor.
    Vendor_Offer(vo_id, pid) --> vo_id is the primary key, and pid is the properties that the vendor is saling.

    ---
    from here, we can use LEFT JOIN, etc to pull out the search based on different criteria
    Last edited by orbitz; 04-21-2005 at 11:05 PM.

  3. #3
    Join Date
    Sep 2003
    Posts
    1,215
    Yes, I have the data dictionary completed, I have a list of the various entities/fields and such that the database needs.

    I can't seem to work out in my mind how to create the query/relationship though that will enable a end user to match the purchaser with the property.

    The purchaser is going to need to input their personal info and then the type of property they are after. Would these be in the same table or not? The vendor is going to have to put their details and property they're selling in too.

    The question is, how can I get access to query the purchasers criteria agaisnt the properties for sale?

    Just need a kick in the right direction! I mean it's easy to do queries like...what purchasers are interested in 3 bedroom houses, but that's not what i'm aiming for.

  4. #4
    Join Date
    Mar 2004
    Posts
    1,303
    Originally posted by talkfreelance
    [B]
    The purchaser is going to need to input their personal info and then the type of property they are after. Would these be in the same table or not? The vendor is going to have to put their details and property they're selling in too.
    I would suggest you to have their request on another table (such as purchaer_order) separated from the table's purchaser info. The same thing for vendor. The reasons: you don't want to duplicate the purchaser info everytime they come back for another house
    The question is, how can I get access to query the purchasers criteria agaisnt the properties for sale?
    I would suggest you to read more on LEFT JOIN. Your results of searching based on the comebination of all the tables: purchaser info, purchaser_order, Vendor, Properties, and Vendor Order .... all this should be in one single query. This is because it needs to check if the properties are already taken or not, how much it cost, who is the vendor....

    Right now, without your data dictionary, I can point you to this website, there is a tutorial on how to pull data from many tables.
    http://www.w3schools.com/sql/sql_join.asp
    Last edited by orbitz; 04-21-2005 at 11:26 PM.

  5. #5
    Join Date
    Jan 2004
    Location
    Texas, United States
    Posts
    131
    Hello:

    I know this will be of great value for you:

    http://www.databaseanswers.org/data_models/index.htm

    Thanks!

  6. #6
    Join Date
    Mar 2004
    Posts
    1,303
    awesome info!
    thanks erod84 for sharing

  7. #7
    Join Date
    Sep 2003
    Posts
    1,215
    Thanks for your help...i'm still stuck

    I've looked into LEFT JOIN but where do I enter the query? I need to do it through access and it needs to be automatic. Every purchaser will require different things, so whatever they specifiy needs to basically generate a new query that can be run at the touch of a button to pull any suitable matches out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •