comparing a single entry to field in other rows?

[Kay Harvey via AccessMonster.com]

Count select records depending on date value

I am a newbie with queries and haven?t been able to get this right. I have
the following table:

Region SaleYr InitYr
A 2005 2004
A 2003 2003
A 2003 2001
A 2002 2003
B 2004 2004
B NULL 2000
B 2002 2002
B 2004 2000

I need a query that will group by Region and SaleYr, and for each SaleYr
entry, generates a column, labeled ?InitCt? that counts the number of
InitYr entries (for that Region) that are less than or equal to that year?s
SaleYr entry. The resulting Query should show:

Region SaleYr InitCt
A 2002 1
A 2003 3
A 2005 4
B 2002 3
B 2004 4

I have tried using DCount a number of ways but can?t get it to count only
the records that meet the criteria while also not producing an error
because of the null value. Some explanation would be helpful. In general,
how do you write a query to compare all values in a field to one value of
that field or another field, and then do the same for the next record entry?
In my case, I need to compare each SaleYr entry to all InitYr entries for a
Region and Count only those that are a lesser value.

Thanks in advance.

 

[Marshall Barton]

Count select records depending on date value

I am a newbie with queries and haven?t been able to get this right. I have
the following table:

Region SaleYr InitYr
A 2005 2004
A 2003 2003
A 2003 2001
A 2002 2003
B 2004 2004
B NULL 2000
B 2002 2002
B 2004 2000

I need a query that will group by Region and SaleYr, and for each SaleYr
entry, generates a column, labeled ?InitCt? that counts the number of
InitYr entries (for that Region) that are less than or equal to that year?s
SaleYr entry. The resulting Query should show:

Region SaleYr InitCt
A 2002 1
A 2003 3
A 2005 4
B 2002 3
B 2004 4

I have tried using DCount a number of ways but can?t get it to count only
the records that meet the criteria while also not producing an error
because of the null value. Some explanation would be helpful. In general,
how do you write a query to compare all values in a field to one value of
that field or another field, and then do the same for the next record entry?
In my case, I need to compare each SaleYr entry to all InitYr entries for a
Region and Count only those that are a lesser value.

Try this (air code):

SELECT DISTINCT
T.Region,
T.SaleYr,
(SELECT Count(*)
FROM table As X
WHERE X.Region = T.Region
AND X.InitYr <= T.SaleYr
) As InitCt
FROM table As X
ORDER BY
T.Region,
T.SaleYr

 

[Kay Harvey via AccessMonster.com]

Thank you for the quick reply. I?m sure if I had more knowledge your
suggestion would have done it for me but I don?t quite have it working. I
ran the query and it returned a Syntax error for the first embedded SELECT
statement. I then assumed that since I didn?t give you the table name
(which is ?tbl?), that you wanted me to replace the word ?table? with my
table name, ?tbl?. Running the query with this change causes it to ask,
?Enter Parameter Value? for T.Region.

The SQL view for this query is:

SELECT DISTINCT
T.Region,
T.SaleYr,
(SELECT Count(*)
FROM tbl As X
WHERE X.Region = T.Region
AND X.InitYr <= T.SaleYr
) As InitCt
FROM tbl As X
ORDER BY
T.Region,
T.SaleYr

I see that X.Region is identified by setting the table ?AS X? but I don?t
quite understand how ?T.Region? is identified. Should one of the ?FROM?
lines name the table ?T? rather than "X" ? I tried changing the second ?AS
X? to AS T? but that changed the error to ?Data type mismatch in criteria?.
Any help would be appreciated.

 

[Kay Harvey via AccessMonster.com]

Almost have it. Thanks! I changed the table field to a number field (it
was text field) and the query works with only one slight problem remaining.
The Query result is as follows:

Region SaleYr InitCt
A 2002 1
A 2003 3
A 2005 4
B 0
B 2002 3
B 2004 4

But I didn't want to include a lineitem for the blank SaleYr. I'm sure I
can get this figured out. Thanks for the help.

 

[Kay Harvey via AccessMonster.com]

Note: correction to the previous message -the first line item for Region B
had a blank for SaleYr and a "0" for the InitCt. (the "0" should be moved
to the right and placed under the InitCt column.)

 

[Kay Harvey via AccessMonster.com]

I was able to get rid of the line associated with the Null value by adding
an IsNull statement as follows:

SELECT T.Region, T.SaleYr, (SELECT Count(*)
FROM tbl As X
WHERE X.Region = T.Region
AND X.InitYr <= T.SaleYr
) AS InitCt
FROM tbl AS T
WHERE Not IsNull(T.SaleYr)
GROUP BY T.Region, T.SaleYr
ORDER BY T.Region, T.SaleYr;

Thanks for helping me out with this - It is working perfectly!

 

[Marshall Barton]

I was able to get rid of the line associated with the Null value by adding
an IsNull statement as follows:

SELECT T.Region, T.SaleYr, (SELECT Count(*)
FROM tbl As X
WHERE X.Region = T.Region
AND X.InitYr <= T.SaleYr
) AS InitCt
FROM tbl AS T
WHERE Not IsNull(T.SaleYr)
GROUP BY T.Region, T.SaleYr
ORDER BY T.Region, T.SaleYr;

Thanks for helping me out with this - It is working perfectly!

Glad you got it going.

In a query, it's better to use:

WHERE T.SaleYr Is Not Null

instead of the IsNull function. No reason to have a
dependency on a VBA function here.

 

[Kay Harvey via AccessMonster.com]

Thanks for the advice. I'll make the change.