Access 2010 SQL Query with Date Range

Question

I'm new here and quite new to SQL and Access. What I have is a table called 'Apartments' that contains a bunch of rows of information. It has Building, Letter, SSN, LeaseDate, MonthlyRent, MoveinCondition and MoveoutCondition. For my class I have to figure out how many times a specific apartment was leased given all the information in the table and display by Building, Letter and NumberLeased.

What I have so far is this:

SELECT Building, Letter, COUNT(*)  
FROM Apartments  
GROUP BY Building, Letter;

This displays it almost correctly! However there is a catch. There can be multiple tenants on the lease at the same date, but it only counts as one active lease.

So what I did to check was this:

SELECT Building, Letter, LeaseDate, COUNT(*)  
FROM Apartments  
GROUP BY Building, Letter, LeaseDate;

Now this in fact does group by the building, letter and the lease date and counts the number of leases on the date.

But how do I display it so that it's not counting these duplicates, and add some sort of where or having statement to specify this.

for example: If apartment 1A was leased on 1/1/14 but by 4 tenants and also 1/1/13 by 3 tenants, it should only show the NumberLeased as 2, not 7.

HansUp · Accepted Answer · 2014-12-05 02:33:37Z

1

Start with a query which gives you a single row for each apartment lease term. Per your example, the following query will condense the rows for each of the 4 apartment 1A tennants for the 1/1/14 LeaseDate into a single row:

SELECT DISTINCT Building, Letter, LeaseDate
FROM Apartments

Then use that as a subquery and base the lease counts on its distinct rows:

SELECT sub.Building, sub.Letter, Count(*) AS NumberLeased
FROM
    (
        SELECT DISTINCT Building, Letter, LeaseDate
        FROM Apartments
    ) AS sub
GROUP BY sub.Building, sub.Letter;

edited Dec 5, 2014 at 2:33

answered Dec 5, 2014 at 2:23

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alexander Cohen Over a year ago

Thanks, unfortunately it said there was a syntas error in Count(sub.*)

Alexander Cohen Over a year ago

Awesome thankyou! So the way a subquery works is that you esentially create the query within the query and assign it a name, and then use that name - sub - in front of the parameters in the beginnign select statement and in the group by?

HansUp Over a year ago

AS sub is how I assigned an alias to that subquery. Then the fields are referenced by qualifying the field names with that alias. Aliasing the data source is especially useful for subqueries. But aliasing can be used basically any time, even with a very simple single table SELECT such as SELECT y.* FROM YourTable AS y;

Alexander Cohen Over a year ago

Hey man, another question that leads off this if you know the answer to: If I have another table called Apartment and it has a list of all the AllApartments but in the Apartment table from before it has only apartments that have been leased, how do I connect the two tables so that it merges all of the apartments even if they haven't been leased. Thanks

Collectives™ on Stack Overflow

Access 2010 SQL Query with Date Range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related