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

    Help on building an expression in a query (2000)

    Hi,

    Firstly a big thank you to everyone who has assisted me in my project so far it is greatly appreciated and I have learnt from you all.

    I have a tbl with the field call 'Type' which could be Flat, Terrace, Semi-Detached, Bungalow, Detached

    On the purchasers type I have Yes/No boxes for each of these types of houses as sometimes a purchaser may settle for a semi or a detached, etc etc. What I want to do is create a query that gives the result fro the property table base don what the purchaser has ticked in the yes/no box for each property type.

    Any ideas?

    Luke
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    The design is not optimal. It would have been better not to have the Yes/No fields in the purchasers table, but instead have an extra table with puchaser - type combinations. So if purchaser A is interested in Bungalow or Detached, there would be two records for Purchaser A. If purchaser B is interested in all five types, there would be five records. Creating a query would be straightforward.

    With the existing design, you could do something like this:
    - Create a new query in design view.
    - Add the purchasers and properties tables.
    - Add the fields you need, including the Type field from the properties table.
    - Set the criteria for the Type field to
    <code>
    IIf([YesNo1],"Flat") Or IIf([YesNo2],"Terrace") Or IIf([YesNo3],"Semi-Detached") Or IIf([YesNo4],"Bungalow") Or IIf([YesNo5],"Detached")
    </code>
    Replace YesNo1 to YesNo5 with the names of the Yes/No fields in the purchasers table.

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

    Re: Help on building an expression in a query (2000)

    Hans,

    Thank you so much, I am so pleased at this. Forever in your debt!
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    Hans,

    The code from the 13th May works okay on a purchaser side of things, but it comes to the property side of things I think I need to alter it.

    Just to refresh I have The property which has a combo box for an area it resides in, the price of it and the type e.g. semi-detached etc.

    On the purchaser side of things they have yes/no boxes for these so a separate yes/no box for semi-detached and one for detached etc etc.

    How do I build the query to take into account this so that I can match purchasers upto properties as apposed to matching properties upto purchaser?
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    This is where the design of the purchaser table bites you. If at all possible, you should switch to the design I sketched higher up in this thread. That would enable you to join on the property type field. Otherwise, you're doomed to kludgy workarounds.

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

    Re: Help on building an expression in a query (2000)

    Hans,

    This seems okay but might bloat it a lot? We have 5 types of property but then also 11+ different areas that the person may want to consdier, so how could I design the table and how would it work? Sorry to be so stupid.
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    Are the preferences for areas and property types independent of each other, or can the potential purchaser indicate "I'm interested in types A and B in area 1, and also in types B, C and E in area 2, and type D in area 3"?

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

    Re: Help on building an expression in a query (2000)

    Hans,

    The preferences could be

    We are interested in Property Types A and B in Areas 1,2,3 or 4
    Usually people will have more than one property type preference. Usually the areas they woudl like it greater than 1 as well, sometimes as much as 6/7 for investors.

    Hope this helps?

    Luke
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    Can I take that to mean that purchasers select one or more areas they are interested in, and one or more property types, but not specific combinations? Or can they specify "I'm interested in bungalows and semi-detached houses in Parkview, and only bungalows in Meadowcrest"?

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

    Re: Help on building an expression in a query (2000)

    Hans,
    You are correct on your first statement. Purchasers can specify their interest in one or more property types in one or more areas, but NOT specific combinations.
    Best Regards,

    Luke

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

    Re: Help on building an expression in a query (2000)

    In that case, you'd have tables:

    tblAreas
    AreaID (primary key), AreaName, ...

    tblPropertyTypes
    PropertyTypeID (primary key), PropertyType, ...

    tblPurchasers:
    PurchaserID (primary key), LastName, ...

    tblPurchaserAreas
    PurchaserID, AreaID (composite primary key)

    tblPurchaserPropertyTypes:
    PurchaserID, PropertyTypeID (composite primary key)

    tblProperties
    PropertyID (primary key), AreaID, PropertyTypeID, ...

Posting Permissions

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