Help in Counting Records
Annette Massie
have a referral date. The referral date determines where clients are on the
waiting list (oldest referral date is 1 on the waiting list, newest referral
date is the last one on the waiting list).
I couldn't think of a way to assign numbers to the clients other than
creating a table with the clients that meet the criteria with a make table
query, adding a field -through code, to the table (auto number). This
autonumber field starts at one and goes to the last record, therefore
assigning numbers to the clients as they would be on the waiting list.
My problem occurs when in a multi-user environment and someone has the
report open (print/preview) and another user wants to run the report. I get
Error 3262; Couldn't lock table "tblname"; currently in use by <username> on
machine <machine name>.
Is there another way for me to assign sequential numbers, starting with one
for the earliest referral to whatever for the last referral, incrementing by
one that I will not get a locking error?
I know there has to be an easier way!!!!
Thanks in advance for your help!!!
Marshall Barton
For just numbering the detail records in a report, the usual way is to
use Sorting and Grouping to sort on your referral date. Then set the
number textbox's ControlSource to =1 and RunningSum to OverAll.
At least, I think that's what you're looking for,
Marsh
Annette Massie wrote in message ...
>In Access 97, I have a waiting list database. Clients on the waiting
list
>have a referral date. The referral date determines where clients are
on the
>waiting list (oldest referral date is 1 on the waiting list, newest
referral
>date is the last one on the waiting list).
>
>[snip roundabout method]
Annette Massie
Thanks for the response. This will work if I am sorting in a report where
the sort is based on the referral, however I have a report that is to be
sorted on the client's name, including the referral date and then lists the
number the client is in the waiting list. Therefore, if the client's name is
Anderson and their referral date is the most current, I do not want it to be
1, based on the sorting. That's why I was adding a counter to the table and
not allowing the report to do the running sum.
Any other suggestions?
"Marshall Barton" <marsh...@fMindspring.com> wrote in message
news:8nje91$cnh$1...@nntp9.atl.mindspring.net...
Jon Ley
Hmmm. Top of my head, I would say you're going to have to do something
clever like create the new table from code (rather than using a make-table
query) and give the table a variable name like
"tmpReferrals" & format(now, "hhnnss")
Hopefully you can be reasonably sure that two people will not try and create
this report at exactly the same time (to the second). You will then have to
change the record source for the report. I think Access will let you do
this, maybe in the Open event of the report - you may have to store the
temporary table name on a form or as a global variable for the report's code
to be able to get at it. Don't forget to delete the temporary table after
you close the report.
--
Jon Ley
Reply address is anti-spammed.
Please reply to the newsgroup for the benefit of others,
and to encourage further discussion.
Marshall Barton
I think you can come close by adding a field to your report's
RecordSource query. Something like:
Rank: (Select Count(*) From tblWaitList As Tmp
Where Tmp.referral<=tblWaitList.referral)
The only problem with this (aside from speed) that I'm aware of is
that ties have the same Rank value.
HTH,
Neil Sunderland
>In Access 97, I have a waiting list database. Clients on the waiting list
>have a referral date. The referral date determines where clients are on the
>waiting list (oldest referral date is 1 on the waiting list, newest referral
>date is the last one on the waiting list).
>
>creating a table with the clients that meet the criteria with a make table
>query, adding a field -through code, to the table (auto number). This
>autonumber field starts at one and goes to the last record, therefore
>assigning numbers to the clients as they would be on the waiting list.
>
>My problem occurs when in a multi-user environment and someone has the
>report open (print/preview) and another user wants to run the report. I get
>Error 3262; Couldn't lock table "tblname"; currently in use by <username> on
>machine <machine name>.
>
>Is there another way for me to assign sequential numbers, starting with one
>for the earliest referral to whatever for the last referral, incrementing by
>one that I will not get a locking error?
>
You don't need to make a table; you can get a query to calculate
the rankings for you - and it will be *much* faster:
Select C1.ClientID, C1.ClientName, C1.ReferralDate,
(SELECT Count(ClientID) FROM tblClients AS C2
WHERE C2.ReferralDate <= C1.ReferralDate) AS Rank
FROM tblClients AS C1
The inner SELECT clause counts how many Clients have referral
dates that are less than or equal to the current Client. The
oldest referral date will be ranked 1.
HTH,
Neil Sunderland
--
Barnstaple, Devon
Spammers can use the From: address, 'cos I won't read it.
Everyone else can use neilsunderland(at)freeuk(dot)com, which I
will
read.
Sent via Deja.com http://www.deja.com/
Before you buy.
Neil Sunderland
--
Barnstaple, home of the traffic jam.
Spammers can use the From: address, 'cos I won't read it.
Everybody else can use neilsunderland(at)freeuk(dot)com, which I will read.
Annette Massie
than the make table thing!!! Any insight on how to handle duplicate
referral dates for different people? Let's say the person who comes first in
the alphabet will get services first in the case clients with the same
referral date.
Any clues?
"Neil Sunderland" <neilsun...@my-deja.com> wrote in message
news:399d95d3...@news.freeuk.net...
Neil Sunderland
> Thanks for the info. I tried it and you are right. I works *much*
> faster than the make table thing!!! Any insight on how to handle
> duplicate referral dates for different people? Let's say the person
> who comes first in the alphabet will get services first in the case
> clients with the same referral date.
This must be the American version of our NHS 'Postcode Lottery' ;O)
I'm sure it is possible (btw I read your e-mail), but I haven't really
had time to think about it. (Translation: my brain hurts!)
I'd probably go for the brute-force method first: something like
another query (based on the first) to get everyone whose ranking is
equal, then ranking those records on their name, then adding that
ranking to the original ranking in the first query. I might have chance
later today to have a play around with this.
Of course, if anyone else knows of an easier way...
Neil Sunderland
initial ranking (with duplicates), one to rank the duplicates amongst
themselves, and a third to show the final ranking.
If you have two people with the same names and same referral dates you
will still get duplicates; if you want to weed them out using their Zip
code, or shoe size, or something else, then you're on your own!
It's probably possible to combine this into two queries (or maybe even
one) but this way it's a bit easier to follow!
Anyway, start with this table (tblClients):
ClientID FirstName LastName ReferralDate
1 Fred Bloggs 01/01/00
2 Joe Davis 02/03/00
3 Charlie Drake 04/02/00
4 David Davidson 04/02/00
5 Edward Jones 13/03/00
6 Tom Smith 04/02/00
-------------------------------------------------------------------
Below is more-or-less the original query; I've added an extra field to
combine the first and last names.
qryClientRank:
Select C1.ClientID, C1.FirstName, C1.LastName,
C1.LastName & ' ' & C1.FirstName AS WholeName
C1.ReferralDate,
(SELECT Count(ClientID) FROM tblClients AS C2
WHERE C2.ReferralDate <= C1.ReferralDate) AS Rank
FROM tblClients AS C1
will produce this resultset:
ClientID FirstName LastName WholeName ReferralDate Rank
1 Fred Bloggs Bloggs Fred 01/01/00 1
2 Joe Davis Davis Joe 02/03/00 5
3 Charlie Drake Drake Charlie 04/02/00 2
4 David Davidson David sonDavid 04/02/00 2
5 Edward Jones Jones Edward 13/03/00 6
6 Tom Smith Smith Tom 04/02/00 2
-------------------------------------------------------------------
This next query works out the ranking within each ranking; it counts
the number of clients whose name is greater than the current client and
whose ranking is the same.
qryAddRank
SELECT C1.*, (SELECT COUNT(C2.WholeName) FROM qryClientRank AS C2
WHERE C2.WholeName < C1.WholeName AND C1.Rank = C2.Rank)
AS AddRank
FROM qryClientRank AS C1;
will produce this resultset:
ClientID FirstName LastName WholeName ReferralDate Rank AddRank
1 Fred Bloggs Bloggs Fred 01/01/00 1 0
2 Joe Davis Davis Joe 02/03/00 5 0
3 Charlie Drake Drake Charlie 04/02/00 2 1
4 David Davidson Davidson David 04/02/00 2 0
5 Edward Jones Jones Edward 13/03/00 6 0
6 Tom Smith Smith Tom 04/02/00 2 2
-------------------------------------------------------------------
And finally, you just need to add the Rank to the AddRank and sort it
to get a proper consecutive ranking list.
qryFinalRanking:
SELECT ClientID, FirstName, LastName,
ReferralDate, [Rank]+[AddRank] AS Ranking
FROM qryAddRank
ORDER BY [Rank]+[AddRank]
which will produce this resultset:
ClientID FirstName LastName ReferralDate Ranking
1 Fred Bloggs 01/01/00 1
4 David Davidson 04/02/00 2
3 Charlie Drake 04/02/00 3
6 Tom Smith 04/02/00 4
2 Joe Davis 02/03/00 5
5 Edward Jones 13/03/00 6
How's that?
Neil Sunderland
--
Barnstaple, home of the traffic jam
Spammers can use the From: address, 'cos I won't read it.
Neil Sunderland
post in this thread was flawed - I'd cheated on the first query by
creating it as a table, and this stopped the second query working.
So here is the corrected version:
Starting with this table (tblClients):
ClientID FirstName LastName ReferralDate
1 Fred Bloggs 01/01/00
2 Joe Davis 02/03/00
3 Charlie Drake 04/02/00
4 David Davidson 04/02/00
5 Edward Jones 13/03/00
6 Tom Smith 04/02/00
qryClientRank:
SELECT C1.ClientID, C1.FirstName, C1.LastName,
C1.LastName & ' ' & C1.FirstName AS WholeName
C1.ReferralDate,
(SELECT COUNT(ClientID) FROM tblClients AS C2
WHERE C1.ReferralDate > C2.ReferralDate) AS Rank
FROM tblClients AS C1
will produce this resultset: (note the ranking starts at zero, and
we've added a new field to allow us to
sort the names later)
ClientID FirstName LastName WholeName ReferralDate Rank
1 Fred Bloggs Bloggs Fred 01/01/00 0
2 Joe Davis Davis Joe 02/03/00 4
3 Charlie Drake Drake Charlie 04/02/00 1
4 David Davidson Davidson David 04/02/00 1
5 Edward Jones Jones Edward 13/03/00 5
6 Tom Smith Smith Tom 04/02/00 1
Fred's ranking is 0 because no-one whose referral date is earlier than
his; Charlie, David and Tom each have only one other person (Fred)
whose referral date is less than theirs, Joe Davis has four people
(Fred, Charlie, David and Tom) with earlier referral dates, and Edward
has five.
qryAddRank:
SELECT R1.*, (SELECT COUNT(R2.WholeName) FROM qryClientRank AS R2
WHERE R2.WholeName < R1.WholeName AND R1.Rank = R2.Rank)
AS AddRank
FROM qryClientRank AS R1;
This time we also rank each name, by counting how many people whose
ranking is the same have "higher" names than the current record.
will produce this resultset: (the ranking is still zero-based)
ClientID FirstName LastName WholeName ReferralDate Rank AddRank
1 Fred Bloggs Bloggs Fred 01/01/00 0 0
2 Joe Davis Davis Joe 02/03/00 4 0
3 Charlie Drake Drake Charlie 04/02/00 1 1
4 David Davidson Davidson David 04/02/00 1 0
5 Edward Jones Jones Edward 13/03/00 5 0
6 Tom Smith Smith Tom 04/02/00 1 2
Out of the people who have a rank of 1, David has no-one with a "lower"
name, Charlie has 1 person (David) and Tom 2 (David and Charlie). Note
that if two people share the same ranking *and* the same name there
will still be a tie.
qryFinalRanking:
SELECT ClientID, FirstName, LastName, ReferralDate,
[Rank]+[AddRank]+1 AS Ranking
FROM qryAddRank
ORDER BY [Rank]+[AddRank]+1
will produce this resultset:
ClientID FirstName LastName ReferralDate Ranking
1 Fred Bloggs 01/01/00 1
4 David Davidson 04/02/00 2
3 Charlie Drake 04/02/00 3
6 Tom Smith 04/02/00 4
2 Joe Davis 02/03/00 5
5 Edward Jones 13/03/00 6
This adds the two rankings together, adds 1 to make it one-based, then
sorts the list into rank order.
Neil Sunderland
--
Barnstaple, home of the traffic jam (and bug-ridden Access databases)