Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2010
    Posts
    41

    Question Unanswered: Help with an access query

    Hi i was hoping someone could help on a certain query,

    i am a property developer and my portfolio has grown rapidly and i have many properties purchased (200+) which i rent out - so i decided to design a database...

    i need a query to tell me the number of (how many bedrooms) properties there are: such as how many properties i have with 2 bedrooms, 3 bedroom, 4 bedrooms etc...

    I have a properties table that lists my 200+ properties and ofcourse a field to store the number of bedrooms..

    im not sure which was to go with this - any1 help?

  2. #2
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    I would think it would depend on your table structure and how you want to view the information(via a form, report, or in the query), but you could do a query where like:

    Code:
    SELECT *
    FROM Properties
    WHERE bedroom = 2;
    This is assuming that you want to see all the information from the table [Properties] only showing the information for properties that have 2 bedrooms, also assuming that you used numbers in the field called [bedroom].

    It is a little difficult to help without more information about how your database is designed.
    "Things are only impossible until they are not." ~Jean Luc Picard

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    To be honest, if you are that successful then your time is probably worth far more concentrating on what you do well and sub contracting what you can't. Piddling about in Access is no way for a millionaire to spend his\ her days when there is money to be made.

    Otherwise, have a look here especially concentrating on GROUP BY and aggregate functions:
    SQL Tutorial
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2010
    Posts
    41
    Many thanks for your replies
    to be honest i dont want to pay any1 to do something which i can do myself (if i put my mind to it) same goes for my properties, i used to work as a bricklayer, skimmer, roofer, tyler ect and do EVERYTHING myself which is how i made my business....i want to try do this database myself simply because i want to learn something new and something ive never tried before!

    i am using access 2007 and i dont really know much about SQL - what i did was to open a query in design view, select my properties table which looks like:

    PropertyID
    HouseNo
    Address
    Town
    County
    PostalCode
    TotalBedroom
    GardenSize

    Please note the garden size has come from a different table:
    GardenSizeCode
    Length
    Width

    basically i want a query to show me the total number of 2 bedroom houses, 3 bedroom houses and 4 bedroom houses and i also want to know the garden size stated in square meters...

    Is it possible i could do this without writing any code?

    Thanks

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by redsky View Post
    i want to learn something new and something ive never tried before!
    Oh cool - I love guiding keen people through learning this stuff

    Quote Originally Posted by redsky View Post
    Is it possible i could do this without writing any code?
    Yes - you can design queries without ever looking at a line of SQL by using the query builder. However, you are mostly on your own there since we can only simply exchange query instructions in code. If you don't want to learn any code at all then you had best google "Access query builder" and find a tutorial.

    Note however that although you can do a lot in Access without learning any SQL, you will likely design a dog of a database without some understanding of relational concepts, and your user interface cannot be very sophisticated without learning some Visual Basic.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2010
    Posts
    41
    Well i know a tiny weeny little bit of SQL and a tiny weeny little bit of VB as i have studied BIS as a mature student

    i remember things like select * from table - which means select everything from the table, and i remember doing a decision support system in Excell and i used VB code to toggle toolbard on and off etc...just small things like that but quite a while ago

    So i guess i'll google query builder then? i was hoping someone would guide me through it

  7. #7
    Join Date
    Mar 2010
    Posts
    41
    Quote Originally Posted by Raptorfish View Post
    I would think it would depend on your table structure and how you want to view the information(via a form, report, or in the query), but you could do a query where like:

    Code:
    SELECT *
    FROM Properties
    WHERE bedroom = 2;
    This is assuming that you want to see all the information from the table [Properties] only showing the information for properties that have 2 bedrooms, also assuming that you used numbers in the field called [bedroom].

    It is a little difficult to help without more information about how your database is designed.
    can i use the code like this:

    Code:
    SELECT *
    FROM Properties
    WHERE bedroom = 2 and 3 and 4;
    baically i want 3 columns with totals...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is invalid SQL.

    Building queries is often best done a stage at a time. Try to define very clearly what your three columns are and what you want in each of them.

    To get only properties with those bedroom numbers you are best using IN():
    SQL IN Operator
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2010
    Posts
    41
    Ok, this is what i want from the query,

    i want to highlight the number of properties i have within my portfolio. i want to advertise the number of 2 bedrooms, 3 bedrooms, 4 bedrooms etc
    so i just want a list of how many of each property is availible

    e.g

    BedroomNumbers Total properties Gardensize
    2 rooms 125 properties square meters
    3 rroms 50 properties
    4 room 20 properties

    (the above numbers are rough estimates)

    just assuming there is a set garden size for each property type, i.e a 2 bed property will have a small garden same as the rest of the 2 bed houses. these mesurements are in lenth and width(m) and i need something to calculate the square meters in that filed....

    im having a look at SQL IN Operator....

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What happens if you have 10X2 bed properties with GardenSize 30msquare and 115X2 bed properties with GardenSize 50msquare? Please could you complete your table for that eventuality, assuming that GardenSize is not directly related to number of bedrooms.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2010
    Posts
    41
    Quote Originally Posted by pootle flump View Post
    What happens if you have 10X2 bed properties with GardenSize 30msquare and 115X2 bed properties with GardenSize 50msquare? Please could you complete your table for that eventuality, assuming that GardenSize is not directly related to number of bedrooms.
    i am assuming:

    all properties with 2 bedrooms have a garden size of 4 square meters
    all properties with 3 bedrooms have a garden size of 6 square meters
    all properties with 4 bedrooms have a garden size of 15 square meters

    There is another table for garden size as below:

    GardenSizeCode
    Garden Length
    Garden Width

    Data in the above table is like this:

    GardenSizeCode Garden Length Garden Width
    2x2 2 2
    3x2 3 2
    5x3 5 3

    assuming i only have properties of a 2, 3, or 4 bedroom capacity and assuming there are only the garden sizes mentioned above...

  12. #12
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by redsky View Post
    Well i know a tiny weeny little bit of SQL and a tiny weeny little bit of VB as i have studied BIS as a mature student

    i remember things like select * from table - which means select everything from the table, and i remember doing a decision support system in Excell and i used VB code to toggle toolbard on and off etc...just small things like that but quite a while ago

    So i guess i'll google query builder then? i was hoping someone would guide me through it
    Hi there...
    I would do this as a parameter query - Start by adding all the fields you want to see in your result. In the criteria row for the bedrooms field you put a 'heading' in square brackets like [Enter number of rooms], then when you run the query you will get an entry field where you can enter the number you are interested in seeing the result for at run-time.

    If you need all results in the same result (two dimensions) I would look at a crosstab query.

    If you want a field calculated (like the garden size) you put the expression in the heading of a blank field: Garden size in m2: [garden]![length]*[garden]![width]
    (you can include this in the parameter query I described earlier.)

    Not sure if this is what you're after, but parameter queries help me out all the time...

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nenne is spot on but a few steps ahead of you. For now you want to sort out your IN(), then add a GROUP BY clause (group on bedroom) and a COUNT() aggregate function.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2010
    Posts
    41
    Nenne thanks for the info i will try that right nowand see if i succeed...

    pootle_flump so i need to do what youve mentioned before i go ahead with the parameter query...exept i dont understand what you mean

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by redsky View Post
    pootle_flump so i need to do what youve mentioned before i go ahead with the parameter query...exept i dont understand what you mean
    Parameterising the query is probably best left till last. Let's get EVERYTHING you need before we start working on filtering.

    Steps:
    You currently use SELECT * - this means you get ALL columns. You only want three, and one of those is in your properties table, so list that. You also want a count per property, which requires the COUNT() function (as linked to above). SELECT clause
    You are looking to restrict the properties, so add the IN() operator (as linked to above).
    Since you are using COUNT(), you need a GROUP BY clause. Add this for the properties column (again, linked to).

    Try these three steps, post what you have got and we will sort out any errors.

    Basically, as a beginner you want to build up your queries in little chunks. I can write the whole thing in one go with probably no errors but I have years of experience. You need to add a little bit at a time and test it. Walk before you run in other words. That's why although Nenne's advice is good we are not ready for it yet.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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