Re: Crosstab counting

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.


Michel Walsh wrote:
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));

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1

.

Relevant Pages

  • Re: Equivalent of Get Next Extended to retrieve only unique keys?
    ... The only real "extra" stuff is pertaining to SQL query optimization. ... Bill Bach wrote: ... Retaining the ability to filter on multiple>> items would probably ...
    (comp.databases.btrieve)
  • Re: Setting a Default Value
    ... the make the Sql call from within Application_Start in Global.asax. ... the query string value each time I needed to ... going to assign the default value through a database query. ... run the following SQL query: select * from table where x = 1 ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: MultiSelect Property
    ... > For Each vItm In Me!lbSelectContract.ItemsSelected ... > Dim stCriteria As String ... >> SQL query). ... >> the multiselect list box appears in the SQL query. ...
    (microsoft.public.access.devtoolkits)
  • Re: Report in VBA - FilterName or WhereCondition
    ... strWhere clause as you advise. ... If you use un-bound controls in your sql query, ... strWhere in the filter parameter of the report, the defense is that you can ...
    (microsoft.public.access.modulesdaovba)
  • InstantEnterprise.NET 1.0.1.1
    ... web-based business software. ... Built completely around Microsoft SQL ... 2005 and .NET technology, InstantEnterprise.NET allows you to focus on ...
    (comp.software.shareware.announce)