Crosstab counting

[ablatnik via AccessMonster.com]

Currently I have a simple query that runs calculations for my work orders
database. I am pasteing the SQL version for Access 2007.

Right now I have a macro that opens 36 reports (hidden) based off of 36
queries. 4 queries per Division to find out what work orders are currently
open under 30 days, over 30 days, over 60 & over 90 days.

I am trying to create a croostab, with not much success. Any ideas?

My thinking is to get a crosstab to report these numbers to reduce the amount
of time running the reports as well as memory congestion. Needless to say
when I start pickup up more work divisions.

SELECT Count([Work Orders].ID_Numbers) AS [Total Carpenters Active +30]
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.
Department_ID = [Work Orders].Department) ON Status.Status_ID = [Work Orders].
Status
HAVING (((Department.Department)="carpenters") AND ((Status.Status)="active"
Or (Status.Status)="On Hold" Or (Status.Status)="Pending" Or (Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[Michel Walsh]

It seems your query has an excess of constants, and thus, have to make a new
query each time one of these constants has to change. Use tables! As
example, for your four period of time, have one table like:

TimeLimits ' table name
LowerLimit, UpperLimit, LimitName ' fields name
0 -30 "Less than 30 days"
-30 -60 "30-60 days"
-60 -90 "60-90 days"
-90 -10000 "more than 90 days"


and use something (using only the relevant tables):


SELECT LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY LimitName



And now, you have only one query rather than 4 (one for each period of
time).


Sure, if the high number of query is also due to "per division", such as
"carpenters" and other, then, rather than making a query per division, only
add the field in the GROUP BY clause:


SELECT division, LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY division, LimitName


and now, you have just one query for the whole job.



Vanderghast, Access MVP

 

[KARL DEWEY]

Use one query instead of so many and all in one report with grouping.
SELECT Department.Department, Count([Work Orders].ID_Numbers) AS [Total
Carpenters Active +30]
FROM Status INNER JOIN (Department INNER JOIN [Work Orders] ON Department.
Department_ID = [Work Orders].Department) ON Status.Status_ID = [Work
Orders].Status
GROUP BY Department.Department
HAVING ((Status.Status)="active" Or (Status.Status)="On Hold"
Or(Status.Status) ="Pending" Or (Status.Status) ="Active - Damaged") AND
(([Work Orders].[Date Opened])<Date()-30));

 

[ablatnik via AccessMonster.com]

I'm confused. I understand creating the table with the field names but the
data shown below, is this example data??? It looks like a legend I would put
in Description field when creating the TimeLimits table.

I think I just don't understand how the current data will be read into this
new table or will this be an update query. I'm asking myself to many
questions.

It seems your query has an excess of constants, and thus, have to make a new
query each time one of these constants has to change. Use tables! As
example, for your four period of time, have one table like:

TimeLimits ' table name
LowerLimit, UpperLimit, LimitName ' fields name
0 -30 "Less than 30 days"
-30 -60 "30-60 days"
-60 -90 "60-90 days"
-90 -10000 "more than 90 days"

and use something (using only the relevant tables):

SELECT LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY LimitName

And now, you have only one query rather than 4 (one for each period of
time).

Sure, if the high number of query is also due to "per division", such as
"carpenters" and other, then, rather than making a query per division, only
add the field in the GROUP BY clause:

SELECT division, LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY division, LimitName

and now, you have just one query for the whole job.

Vanderghast, Access MVP

Currently I have a simple query that runs calculations for my work orders
database. I am pasteing the SQL version for Access 2007.

[quoted text clipped - 21 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[Michel Walsh]

The example was about the table, its fields, and data in its four records.
You would have to fill the data in the table, manually. After all, it is
only four records (one per period of time). In fact, the records are about
the offset to be added to the actual Date, as in Date() + LowerLimit
and Date() + UpperLimit becoming, as example, for the second period:
Date() + -30 and Date() + -60, or, if you prefer, between 30 and 60 days
from now (in the past).

That was how about the data would be in that new table: you fill that small
table (small, but useful) manually, as your requirements for the periods
are.

About how it is read? Well, just have to add it in the FROM clause.

if WorkOrders has the records:
DateOpen
X-12
X-67

and
If we were using a Cartesian join:

SELECT WorkOrders.DateOpen, TimeLimits.LowerLimit, TimeLimits.UpperLimit
FROM WorkOrders, TimeLimits





where X == today, then
we could have got:


DateOpen LowerLimit UpperLimit
X-12 0 -30
X-12 -30 -60
X-12 -60 -90
X-12 -90 -10000
X-67 0 -30
X-67 -30 -60
X-67 -60 -90
X-67 -90 -10000



Now, adding the inner join, only the combinations satisfying the ON clause
would be kept:

ON DateOpen >= Date() + LowerLimit
AND DateOpen < Date()+UpperLimit


DateOpen LowerLimit UpperLimit ON clause
X-12 0 -30 true <
keep it
X-12 -30 -60 false
X-12 -60 -90 false
X-12 -90 -10000 false
X-67 0 -30 false
X-67 -30 -60 false
X-67 -60 -90 true <
keep it
X-67 -90 -10000 false




so, the initial records are now in the correct period of time, and we can
use the description we made for that period of time as representative to
the period of time ( or, in SQL general speaking, of "group")


Vanderghast, Access MVP

I'm confused. I understand creating the table with the field names but
the
data shown below, is this example data??? It looks like a legend I would
put
in Description field when creating the TimeLimits table.

I think I just don't understand how the current data will be read into
this
new table or will this be an update query. I'm asking myself to many
questions.

It seems your query has an excess of constants, and thus, have to make a
new
query each time one of these constants has to change. Use tables! As
example, for your four period of time, have one table like:

TimeLimits ' table name
LowerLimit, UpperLimit, LimitName ' fields name
0 -30 "Less than 30 days"
-30 -60 "30-60 days"
-60 -90 "60-90 days"
-90 -10000 "more than 90 days"

and use something (using only the relevant tables):

SELECT LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY LimitName

And now, you have only one query rather than 4 (one for each period of
time).

Sure, if the high number of query is also due to "per division", such as
"carpenters" and other, then, rather than making a query per division,
only
add the field in the GROUP BY clause:

SELECT division, LimitName, COUNT(*)
FROM [Work Orders] INNER JOIN TimeLimits
ON [Work Orders].[Date Opened] >= Date() + TimeLimits.LowerLimit
AND [Work Orders].[Date Opened] < Date() + TimeLimits.UpperLimit
GROUP BY division, LimitName

and now, you have just one query for the whole job.

Vanderghast, Access MVP

Currently I have a simple query that runs calculations for my work
orders
database. I am pasteing the SQL version for Access 2007.

[quoted text clipped - 21 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[ablatnik via AccessMonster.com]

Michel, thanks for the response. I've built the table as shown, built the
sql query exactly as shown. But when I run the query, I get no data
populating in the query answer table. I checked the table just to ensure
the data was not appending itself there. No go.

I started reading up on joins at wikipedia.com and that has got me even more
confused that I laughed at some of the paragraphs.

After reading the syntex sql multiple times, I can see the statement forming
a sentence. I just don't know why there is no answer. I have 1200 records
in the [Work Orders] table starting at the beginning of this year. I'm sure
something has to match or fall within the criteria.

For created the folling table:

TimeLimits

The Field Names and Data Type are listed:

LowerLimit, Number
UpperLimit, Number
LimitName, Text

The SQL query is the same as yours:

SELECT LimitName, Count(*)
FROM [Work Orders] INNER JOIN TimeLimits ON ([Work Orders].[Date Opened]<Date
()+TimeLimits.UpperLimit) AND ([Work Orders].[Date Opened]>=Date()+TimeLimits.
LowerLimit)
GROUP BY LimitName;

I've read it over and over and I got nothing. What am I missing. The SQL is
not erroring out either. It runs fine but with no data.

The example was about the table, its fields, and data in its four records.
You would have to fill the data in the table, manually. After all, it is
only four records (one per period of time). In fact, the records are about
the offset to be added to the actual Date, as in Date() + LowerLimit
and Date() + UpperLimit becoming, as example, for the second period:
Date() + -30 and Date() + -60, or, if you prefer, between 30 and 60 days
from now (in the past).

That was how about the data would be in that new table: you fill that small
table (small, but useful) manually, as your requirements for the periods
are.

About how it is read? Well, just have to add it in the FROM clause.

if WorkOrders has the records:
DateOpen
X-12
X-67

and
If we were using a Cartesian join:

SELECT WorkOrders.DateOpen, TimeLimits.LowerLimit, TimeLimits.UpperLimit
FROM WorkOrders, TimeLimits

where X == today, then
we could have got:

DateOpen LowerLimit UpperLimit
X-12 0 -30
X-12 -30 -60
X-12 -60 -90
X-12 -90 -10000
X-67 0 -30
X-67 -30 -60
X-67 -60 -90
X-67 -90 -10000

Now, adding the inner join, only the combinations satisfying the ON clause
would be kept:

ON DateOpen >= Date() + LowerLimit
AND DateOpen < Date()+UpperLimit

DateOpen LowerLimit UpperLimit ON clause
X-12 0 -30 true <
keep it
X-12 -30 -60 false
X-12 -60 -90 false
X-12 -90 -10000 false
X-67 0 -30 false
X-67 -30 -60 false
X-67 -60 -90 true <
keep it
X-67 -90 -10000 false

so, the initial records are now in the correct period of time, and we can
use the description we made for that period of time as representative to
the period of time ( or, in SQL general speaking, of "group")

Vanderghast, Access MVP

I'm confused. I understand creating the table with the field names but
the

[quoted text clipped - 51 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[ablatnik via AccessMonster.com]

Michel, hold that thought. I deleted the 2nd part of the from statement
after AND and info populates. Thanks for your help. I will try to figure it
from here. Time to learn.

The example was about the table, its fields, and data in its four records.
You would have to fill the data in the table, manually. After all, it is
only four records (one per period of time). In fact, the records are about
the offset to be added to the actual Date, as in Date() + LowerLimit
and Date() + UpperLimit becoming, as example, for the second period:
Date() + -30 and Date() + -60, or, if you prefer, between 30 and 60 days
from now (in the past).

That was how about the data would be in that new table: you fill that small
table (small, but useful) manually, as your requirements for the periods
are.

About how it is read? Well, just have to add it in the FROM clause.

if WorkOrders has the records:
DateOpen
X-12
X-67

and
If we were using a Cartesian join:

SELECT WorkOrders.DateOpen, TimeLimits.LowerLimit, TimeLimits.UpperLimit
FROM WorkOrders, TimeLimits

where X == today, then
we could have got:

DateOpen LowerLimit UpperLimit
X-12 0 -30
X-12 -30 -60
X-12 -60 -90
X-12 -90 -10000
X-67 0 -30
X-67 -30 -60
X-67 -60 -90
X-67 -90 -10000

Now, adding the inner join, only the combinations satisfying the ON clause
would be kept:

ON DateOpen >= Date() + LowerLimit
AND DateOpen < Date()+UpperLimit

DateOpen LowerLimit UpperLimit ON clause
X-12 0 -30 true <
keep it
X-12 -30 -60 false
X-12 -60 -90 false
X-12 -90 -10000 false
X-67 0 -30 false
X-67 -30 -60 false
X-67 -60 -90 true <
keep it
X-67 -90 -10000 false

so, the initial records are now in the correct period of time, and we can
use the description we made for that period of time as representative to
the period of time ( or, in SQL general speaking, of "group")

Vanderghast, Access MVP

I'm confused. I understand creating the table with the field names but
the

[quoted text clipped - 51 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[Michel Walsh]

.... check if the LowerLimit was not higher than the UpperLimit (if the
number were right). Remember that -60 is LOWER than -30. In other words,
you won't have any records, in the result, if your test ends up with
something like:

... ON someDate > #01/01/2008# AND someDate < #01/01/2001#


ie, the date should be after 2008, AND also before 2001. No record is
returned, in those cases! If that is your situation, reverse the limits, or
the inequations ( > becoming < and vice-versa).


Vanderghast, Access MVP

Michel, hold that thought. I deleted the 2nd part of the from statement
after AND and info populates. Thanks for your help. I will try to figure
it
from here. Time to learn.

The example was about the table, its fields, and data in its four records.
You would have to fill the data in the table, manually. After all, it is
only four records (one per period of time). In fact, the records are about
the offset to be added to the actual Date, as in Date() + LowerLimit
and Date() + UpperLimit becoming, as example, for the second period:
Date() + -30 and Date() + -60, or, if you prefer, between 30 and 60
days
from now (in the past).

That was how about the data would be in that new table: you fill that
small
table (small, but useful) manually, as your requirements for the periods
are.

About how it is read? Well, just have to add it in the FROM clause.

if WorkOrders has the records:
DateOpen
X-12
X-67

and
If we were using a Cartesian join:

SELECT WorkOrders.DateOpen, TimeLimits.LowerLimit, TimeLimits.UpperLimit
FROM WorkOrders, TimeLimits

where X == today, then
we could have got:

DateOpen LowerLimit UpperLimit
X-12 0 -30
X-12 -30 -60
X-12 -60 -90
X-12 -90 -10000
X-67 0 -30
X-67 -30 -60
X-67 -60 -90
X-67 -90 -10000

Now, adding the inner join, only the combinations satisfying the ON clause
would be kept:

ON DateOpen >= Date() + LowerLimit
AND DateOpen < Date()+UpperLimit

DateOpen LowerLimit UpperLimit ON clause
X-12 0 -30 true
<
keep it
X-12 -30 -60 false
X-12 -60 -90 false
X-12 -90 -10000 false
X-67 0 -30 false
X-67 -30 -60 false
X-67 -60 -90 true
<
keep it
X-67 -90 -10000 false

so, the initial records are now in the correct period of time, and we can
use the description we made for that period of time as representative to
the period of time ( or, in SQL general speaking, of "group")

Vanderghast, Access MVP

I'm confused. I understand creating the table with the field names but
the

[quoted text clipped - 51 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));

 

[ablatnik via AccessMonster.com]

That resolved it. I switched the the operator signs around and moved by date
clock forward to test it. Now as stated in the first message is the group,
which you answered and I've already applied that and it works.

What I have to do with these numbers is compile them into a report broken
down by their respective department. What work orders are still active and
under 30, over 30, 60 and 90 days.

The question I have that originated this thread is creating a crosstab and
compiling those numbers into a report, and that report will be compiled into
a master report. Currently I have a macro that runs 36 times just to create
36 individual numbers for this report. The cross tab will eleveate this.

I have not implemented this procedure yet. I am working on a copy of the
actual to ensure all is correct first.

... check if the LowerLimit was not higher than the UpperLimit (if the
number were right). Remember that -60 is LOWER than -30. In other words,
you won't have any records, in the result, if your test ends up with
something like:

... ON someDate > #01/01/2008# AND someDate < #01/01/2001#

ie, the date should be after 2008, AND also before 2001. No record is
returned, in those cases! If that is your situation, reverse the limits, or
the inequations ( > becoming < and vice-versa).

Vanderghast, Access MVP

Michel, hold that thought. I deleted the 2nd part of the from statement
after AND and info populates. Thanks for your help. I will try to figure

[quoted text clipped - 72 lines]

(Status.Status)
="Active - Damaged") AND (([Work Orders].[Date Opened])<Date()-30));