create an index on the fly

[ragtopcaddy via AccessMonster.com]

I have records that I need to assign an index to.

This is what I want to end up with:

Store # KO# End Index
472 13663 31-Jan-11 1
472 13665 31-Jan-12 2
477 7020 31-Jan-10 1
477 7022 31-Jan-12 2
483 6789 31-Jan-06 1
483 3495 31-Jan-05 2
483 9067 31-Jan-07 3
483 10309 31-Jan-08 4

How do I create that Index field in my query, given the data I have?

I need a running count of each store's KO#'s, ordered by End date.

Thanks,

Bill

 

[ragtopcaddy via AccessMonster.com]

I found something that almost works, based on a post in this form:

SELECT [Store #], [KO#], End, DCount("*","qryKOsAll","[Store #]=" & [Store #]
& _
" AND End <=" & Format([End],"\#mm-dd-yyyy\#")) AS Rank
FROM tblKOs

Which returns:

Store # KO# End Rank
472 13663 1/31/2011 1
472 13665 1/31/2012 2
477 7020 1/31/2010 1
477 7022 1/31/2012 2
483 6789 1/31/2006 0
483 3495 1/31/2005 0
483 9067 1/31/2007 0
483 10309 1/31/2008 0

As you can see, it worked for the 1st 2 stores, but not the 3rd. What am I
doing wrong?

 

[John Spencer]

You could try the following

SELECT A.[Store #], A.[KO#], A.[End]
, Count(B.End] as Rank
FROM tblKOs as A INNER JOIN tblKOs as B
On A.[Store #] = B.[Store #]
AND A.[End] >= B.[End]
GROUP BY A.[Store #], A.[KO#], A.[End]


If you are going to use DCount then try this expression - Assumption is
that End is a datetime field.

DCount("*","tblKOs","[Store #]=" & [Store #] & " AND End<=" &
Format([End],"#yyyy-mm-dd#"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ragtopcaddy via AccessMonster.com]

Thanks, John,

I went with your suggestion and abandoned the DCount route.

The query runs just fine, but I can't view it in design view. How might I
edit it so I could view it in design?

Thanks,

Bill

You could try the following

SELECT A.[Store #], A.[KO#], A.[End]
, Count(B.End] as Rank
FROM tblKOs as A INNER JOIN tblKOs as B
On A.[Store #] = B.[Store #]
AND A.[End] >= B.[End]
GROUP BY A.[Store #], A.[KO#], A.[End]

If you are going to use DCount then try this expression - Assumption is
that End is a datetime field.

DCount("*","tblKOs","[Store #]=" & [Store #] & " AND End<=" &
Format([End],"#yyyy-mm-dd#"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have records that I need to assign an index to.

[quoted text clipped - 17 lines]

Bill

 

[ragtopcaddy via AccessMonster.com]

John,

I have a query that is a subset of the data in tblKOs.

When I change the FROM clause of your query to reflect that query, "qryKOsLE",
I get whacky numbers. Some Store #'s that might have 4 entries in tblKOs, but
only 2 that satisfy the criteria of qryKOsLE, return 4 for both entries,
where I expect to see 1 and 2.

Any idea why that might be?

Do I need to dump the results of qryKOsLE into a table and run the query
against that?

Thanks,

Bill

You could try the following

SELECT A.[Store #], A.[KO#], A.[End]
, Count(B.End] as Rank
FROM tblKOs as A INNER JOIN tblKOs as B
On A.[Store #] = B.[Store #]
AND A.[End] >= B.[End]
GROUP BY A.[Store #], A.[KO#], A.[End]

If you are going to use DCount then try this expression - Assumption is
that End is a datetime field.

DCount("*","tblKOs","[Store #]=" & [Store #] & " AND End<=" &
Format([End],"#yyyy-mm-dd#"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have records that I need to assign an index to.

[quoted text clipped - 17 lines]

Bill

 

[John Spencer]

You can't view non-equi join queries in design view. The graphical
editor is incapable of handling this type of join.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks, John,

I went with your suggestion and abandoned the DCount route.

The query runs just fine, but I can't view it in design view. How might I
edit it so I could view it in design?

Thanks,

Bill

You could try the following

SELECT A.[Store #], A.[KO#], A.[End]
, Count(B.End] as Rank
FROM tblKOs as A INNER JOIN tblKOs as B
On A.[Store #] = B.[Store #]
AND A.[End] >= B.[End]
GROUP BY A.[Store #], A.[KO#], A.[End]

If you are going to use DCount then try this expression - Assumption is
that End is a datetime field.

DCount("*","tblKOs","[Store #]=" & [Store #] & " AND End<=" &
Format([End],"#yyyy-mm-dd#"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have records that I need to assign an index to.

[quoted text clipped - 17 lines]

Bill

 

[John Spencer]

As a guess, you need to replace both instances of the table with the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ragtopcaddy via AccessMonster.com]

John,

It turns out that sometimes a record may have the same Store#, a different
KO#, and the same End date. That's what causes some of the ranks to be 2 or 4
for all the KO#s. I will have to clean up my records.

Bill

You could try the following

SELECT A.[Store #], A.[KO#], A.[End]
, Count(B.End] as Rank
FROM tblKOs as A INNER JOIN tblKOs as B
On A.[Store #] = B.[Store #]
AND A.[End] >= B.[End]
GROUP BY A.[Store #], A.[KO#], A.[End]

If you are going to use DCount then try this expression - Assumption is
that End is a datetime field.

DCount("*","tblKOs","[Store #]=" & [Store #] & " AND End<=" &
Format([End],"#yyyy-mm-dd#"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have records that I need to assign an index to.

[quoted text clipped - 17 lines]

Bill

 

[ragtopcaddy via AccessMonster.com]

John,

BTW, would you know offhand why, although I have referenced DAO 3.6 in the
VBE, when I dim variables using DAO, they come out dao instead. I've always
seen them to convert themselves to upper case.

Thanks,

Bill

As a guess, you need to replace both instances of the table with the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

[quoted text clipped - 11 lines]

Thanks,

 

[ragtopcaddy via AccessMonster.com]

Nevermind, they're all back to normal.

As a guess, you need to replace both instances of the table with the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

[quoted text clipped - 11 lines]

Thanks,