Need help with a Group Count query

[Joe Cilinceon]

What I'm trying to do is count the number of move-ins and move outs grouped
by months from Jan 2005 to Current Date. I want this to help me get the
average number of both move-ins and move-outs by month. The table this query
is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Duane Hookom]

I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move as the
Column Heading, and Count(Move) as the Value.

 

[Joe Cilinceon]

Thanks for responding Duane but I'm not following this. StartDate is when
they move in and EndDate is when the move out happens. There will always be
a startdate but not always and enddate. The crosstab completely lost me
since I've never used one before. I will want to use this in a statistical
report at the end of the year.

I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move as
the Column Heading, and Count(Move) as the Value.

--
Duane Hookom
MS Access MVP

What I'm trying to do is count the number of move-ins and move outs
grouped by months from Jan 2005 to Current Date. I want this to help
me get the average number of both move-ins and move-outs by month.
The table this query is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Duane Hookom]

OK, slight correction of my sql...create a union query like:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([EndDate],"yyyy mm"), "Outs"
FROM Leases
WHERE EndDate is not Null;

Save the above query and then create a new select query based on your union
query. Once the union query is displayed in the top panel of your new query,
change the query type to Crosstab. Add YrMth and two copies of Move to the
query grid. In the Crosstab property for YrMth, select Row Heading. In the
Crosstab for the first Move, select Column Heading. In the Crosstab for the
second Move, set the Total to Count and the Crosstab to Value.

--
Duane Hookom
MS Access MVP
--

Thanks for responding Duane but I'm not following this. StartDate is when
they move in and EndDate is when the move out happens. There will always
be a startdate but not always and enddate. The crosstab completely lost me
since I've never used one before. I will want to use this in a statistical
report at the end of the year.

I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move as
the Column Heading, and Count(Move) as the Value.

--
Duane Hookom
MS Access MVP

What I'm trying to do is count the number of move-ins and move outs
grouped by months from Jan 2005 to Current Date. I want this to help
me get the average number of both move-ins and move-outs by month.
The table this query is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Joe Cilinceon]

Ok I'll mess with this a bit and see how to make it for the current year
only. At the moment it is showing me everything.

Thanks again Duane

--

Joe Cilinceon

OK, slight correction of my sql...create a union query like:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([EndDate],"yyyy mm"), "Outs"
FROM Leases
WHERE EndDate is not Null;

Save the above query and then create a new select query based on your
union query. Once the union query is displayed in the top panel of
your new query, change the query type to Crosstab. Add YrMth and two
copies of Move to the query grid. In the Crosstab property for YrMth,
select Row Heading. In the Crosstab for the first Move, select Column
Heading. In the Crosstab for the second Move, set the Total to Count
and the Crosstab to Value.
--
Duane Hookom
MS Access MVP

Thanks for responding Duane but I'm not following this. StartDate is
when they move in and EndDate is when the move out happens. There
will always be a startdate but not always and enddate. The crosstab
completely lost me since I've never used one before. I will want to
use this in a statistical report at the end of the year.

I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move
as the Column Heading, and Count(Move) as the Value.

--
Duane Hookom
MS Access MVP

What I'm trying to do is count the number of move-ins and move outs
grouped by months from Jan 2005 to Current Date. I want this to
help me get the average number of both move-ins and move-outs by
month. The table this query is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Joe Cilinceon]

Thanks Duane I got it and learned something new too. I've never done a Union
query but it worked great.

Ok I'll mess with this a bit and see how to make it for the current
year only. At the moment it is showing me everything.

Thanks again Duane

OK, slight correction of my sql...create a union query like:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([EndDate],"yyyy mm"), "Outs"
FROM Leases
WHERE EndDate is not Null;

Save the above query and then create a new select query based on your
union query. Once the union query is displayed in the top panel of
your new query, change the query type to Crosstab. Add YrMth and two
copies of Move to the query grid. In the Crosstab property for YrMth,
select Row Heading. In the Crosstab for the first Move, select Column
Heading. In the Crosstab for the second Move, set the Total to Count
and the Crosstab to Value.
--
Duane Hookom
MS Access MVP

Thanks for responding Duane but I'm not following this. StartDate is
when they move in and EndDate is when the move out happens. There
will always be a startdate but not always and enddate. The crosstab
completely lost me since I've never used one before. I will want to
use this in a statistical report at the end of the year.

Duane Hookom wrote:
I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move
as the Column Heading, and Count(Move) as the Value.

--
Duane Hookom
MS Access MVP

What I'm trying to do is count the number of move-ins and move
outs grouped by months from Jan 2005 to Current Date. I want this
to help me get the average number of both move-ins and move-outs
by month. The table this query is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Duane Hookom]

Welcome to the "SQL Writers Union Local #123". Your union card is in the
mail.

--
Duane Hookom
MS Access MVP
--

Thanks Duane I got it and learned something new too. I've never done a
Union query but it worked great.

Ok I'll mess with this a bit and see how to make it for the current
year only. At the moment it is showing me everything.

Thanks again Duane

OK, slight correction of my sql...create a union query like:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([EndDate],"yyyy mm"), "Outs"
FROM Leases
WHERE EndDate is not Null;

Save the above query and then create a new select query based on your
union query. Once the union query is displayed in the top panel of
your new query, change the query type to Crosstab. Add YrMth and two
copies of Move to the query grid. In the Crosstab property for YrMth,
select Row Heading. In the Crosstab for the first Move, select Column
Heading. In the Crosstab for the second Move, set the Total to Count
and the Crosstab to Value.
--
Duane Hookom
MS Access MVP

Thanks for responding Duane but I'm not following this. StartDate is
when they move in and EndDate is when the move out happens. There
will always be a startdate but not always and enddate. The crosstab
completely lost me since I've never used one before. I will want to
use this in a statistical report at the end of the year.

Duane Hookom wrote:
I would first create a union query:

SELECT Format([StartDate],"yyyy mm") as YrMth, "Ins" as Move
FROM Leases
UNION ALL
SELECT Format([StartDate],"yyyy mm"), "Outs"
FROM Leases;

You can then create a crosstab with YrMth as the Row Heading, Move
as the Column Heading, and Count(Move) as the Value.

--
Duane Hookom
MS Access MVP

What I'm trying to do is count the number of move-ins and move
outs grouped by months from Jan 2005 to Current Date. I want this
to help me get the average number of both move-ins and move-outs
by month. The table this query is on is as follows:

LEASES
StartDate
EndDate (will only have a date if vacated else Null)

I'm looking for something as follows:
Move Ins Move Outs
Jan. 9 5
Feb. 15 10
Mar 10 12

Hope this is clear.

 

[Joe Cilinceon]

Funny you should mention that, I belonged to United Sheet Metal Workers
Union #123 till 1980.

I have another question for you if you don't mind. Is there a site or sites
that will explain the other types of SQL and what they are used for. To date
I've only done what is available using the query builder in Access. I see
there is a lot more power there than I've been taking advantage of and
really want to learn this side of the equation.

 

[Duane Hookom]

Actually, my father is a retired SMW from I believe Local 10 in Mpls. I
still carry bandaids in my wallet because he always did.

You might do a search on "SQL Syntax". John Viescas co-authored a very good
book on Access Queries.

--
Duane Hookom
MS Access MVP
--

Funny you should mention that, I belonged to United Sheet Metal Workers
Union #123 till 1980.

I have another question for you if you don't mind. Is there a site or
sites that will explain the other types of SQL and what they are used for.
To date I've only done what is available using the query builder in
Access. I see there is a lot more power there than I've been taking
advantage of and really want to learn this side of the equation.

 

[Joe Cilinceon]

Thanks again Duane I'll check it out. I went from SMW to Systems Analyst to
Property Manager over the last 30 years. Quite a change in direction but
then only in America. ;-)

 

[Duane Hookom]

My college degree and only "higher" education is Parks, Recreation, and
Liesure studies.

--
Duane Hookom
MS Access MVP
--

Thanks again Duane I'll check it out. I went from SMW to Systems Analyst
to Property Manager over the last 30 years. Quite a change in direction
but then only in America. ;-)