Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best Method? (2000)

    Looking for input on the best method to do this within the db.

    On the purchasers they have the following fields

    Maxprice
    min bedrooms
    max bedrooms
    yes/no fields for areas (15 in total as they may want more than one area)
    Type of house (yes no boxes, 6 in total)

    I want to match these people up with suitable people from the properties on the tblProperty table / form which has the following fields to marry up to:
    Bedrooms(fixed figure no multiples, etc)
    Area (set area no multiples, etc)
    Price (set price)
    Type (set type no multiples, etc)
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best Method? (2000)

    Can the table structure still be modified? It would be much easier if you didn't have the multiple Yes/No fields in the Purchasers table, but instead a separate table tblPurchaserAreas, with a record for each desired purchaser-area combination and another separate table tblPurchaserTypes, with a record for each desired purchaser-house type combination.
    Matching purchasers and properties would then be a straightforward query. With the current design, it'll be more complicated.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Best Method? (2000)

    Yes it can still be modified
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best Method? (2000)

    Then I strongly recommend doing so. You'd have tables:

    tblPurchasers
    - PurchaserID (AutoNumber, primary key)
    - Name, address etc.
    - MinBedrooms
    - MaxBedrooms

    tblAreas
    - AreaID (AutoNumber, primary key)
    - Area (text)

    tblPurchaserAreas
    - PurchaserID (number, long integer, related to tblPurchasers)
    - AreaID (number, long integer, related to tblAreas)
    The combination of these two fields is the primary key.

    tblTypes
    - TypeID (AutoNumber, primary key)
    - HouseType (text)

    tblPurchaserTypes
    - PurchaserID (number, long integer, related to tblPurchasers)
    - TypeID (number, long integer, related to tblTypes)
    The combination of these two fields is the primary key.

    See Re: Many-to-many relationships (XP, 2000) for an example of using a form and a subform for this kind of design.

Posting Permissions

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