Slowness of DLookup

#21

The Frog wrote:

Quote:

Okaey Dokey,

Maybe I am off the mark here, but I think this has been a little over-
complicated for what it really is. What you are looking for is
effective a form of conditional concatenation to produce your answers.
I believe it can be done directly in a single SQL select statement,
but it will be a doosy.

My thinking is that we can use either the SWITCH or CHOOSE in-built
functions to provide a series of responses appropriate to the desired
result based on the count of distinct surnames based at any individual
address. For example if we take the first desired result for obtaining
a suitable address we would have an 'Expression' field something like:
(psuedo code):
Address: CHOOSE((Select distinct count(member.surname) from Member
WHERE member.addid = [place_holder]), (Select ....details you want for
a single surname address), (SELECT...details you want for a dual
surname address), etc...)

the [place_holder] is simply a new name given to the address id field
for the sake of the expression. You would place it in a separate
column that never actually shows (or you could just rename the
member.addid field in the output to something else such as AddressID
and use that).

The limitation of this approach is that you would have to write the
SQL for each choosable option, so that you have the correct looking
output when there are two or more surnames for example. This is also
limited by how ever many choosable options you care to provide. In the
above psuedo code we have options for one and two surnames per
address, but not for a third surname. If a third were to be there the
CHOOSE function would return a null.

This is not elegant, but it should work. I do not know how quickly
this would run either. In theory the CHOOSE function will evaluate all
possible results (ie/ each select) and return the 'correct' one. It
may be possible to reduce the runtime by using an IIF statement to
encapsulate each SELECT. If the SELECT DISTINCT COUNT returned the
value as say 'MyCount' you could use the IIF to evaluate MyCount
before proceeding with the options SELECT. eg/ IIF(MyCount = 1, .....)
for the first possible response and so forth.

One lat thing: Access doesnt natively support the SELECT COUNT
DISTINCT function. To get around this I usually just use two columns,
one is for the id I am interested in (in this case addid) which I set
as a group by, and the second is what it is I am counting (in this
case unique surnames). In your case I think we are going to need to do
this in two queries / stages.

First query:
SELECT DISTINCT member.addid, member.surname FROM member

Second query: this is where the more complex COUNT and CHOOSE logic
will go.

I know its not perfect, but it should get the job done. I definitely
need more coffee this morning....I hope this wasnt too confusing.

The Frog

I suppose there may be a politically "correct" way to do it but with
disk space not being an issue I still think when a record is added or
updated in a form I'd calc the names then and insert or update into a
table and link that table into a query. No need for pivot tables, IIF()
statement abominations, hair pulling, speed issues, massive debugging,
etc.

#22

On 28/09/2010 13:26:41, John Spencer wrote:

Quote:

Ok. Did you try building each query and looking at the results?

I would especially check the second query and see what it is returning for
rank. If I got it right it should be returning records like the following
for MemAddID 412

412 Brooke 1
412 Edwards 2

If I got it wrong, then we need to adjust the second query.

Hi John

No, not getting those rsults for second query

I am getting

MemAddID Rank MemSurname
412 3 Brooke
412 3 Edwards

Thanks
Phil

Quote:

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

Phil wrote:
On 27/09/2010 17:12:54, John Spencer wrote:
To get a unique list of Surnames for a specific MemAddID you could use.

First Query:
SELECT DISTINCT MemAddID, Surname FROM Member

Now create a ranking query based on that
Second Query:
SELECT A.MemAddID
, A.Surname
, 1 + Count(B.Surname) as Rank
FROM FirstQuery as A LEFT JOIN FirstQuery as B
ON A.MemAddID = B.MemAddID
AND A.SurName < B.Surname
GROUP BY A.MemAddID, A.Surname

Now you can use that second query in a crosstab query
TRANSFORM First(SecondQuery.Surname)
SELECT AddressTable.Street, AddressTable.City, AddressTable.PostalCode
FROM AddressTable INNER JOIN SecondQuery
ON AddressTable.MemAddID = SecondQuery.MemAddID
GROUP BY AddressTable.Street, AddressTable.City, AddressTable.PostalCode
PIVOT "Name" & Rank in
("Name1","Name2","Name3","Name4","Name5","Name6"," Name7","Name8")

You might be able to cram all that into one query using subqueries in the
FROM clause.

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

Tried that John, not sure where it gets me
I end up with a CrossTab query where Name1 is always blank
Name2 has most of the surnames, but where Name2 is blank, a surname appears
in one of the other "Name" columns. Only 1 surname per address row - sample
below

MemAddID Name1 Name2 Name3 Name4 Name5 Name6 Name7 Name8
258 Parker
260 Jenkins
261 Berryman
262 Lange
263 Penfold
264 Albany
265 Wood
266 Moulton, MBE
269 Grubb
270 Evans
271 Neale
272 Burgoyne
275 Lavington
276 Cannon

MemAddID 258 should also have Parker
MemAddID 276 should also have Wright, Garneys and Tierney-Garneys

Any further thoughts pease

Phil
Phil wrote:
On 27/09/2010 08:57:59, The Frog wrote:
Hi Phil,

I am getting the impression here that your base data is fairly complex
when it need not be. In order to do a crosstab easily you need to make
sure that your data you feed it is 'clean'. Is it possible for you to
write a simple select query that retrieves one record (set of records
for a single address) and share that with us? The restriction done for
the single address in the WHERE clause? Maybe its just monday morning
and my head is still foggy but I am seeming to be missing something in
my understanding of your requirements and maybe this would help to
clear that up. The thinking in my head is that if we can use a simple
(and I mean by simple that there are no IIF statements or other such
functions) to start building the required data for the crosstab we
will have a good foundation to work from. We can add the necessary
conditional logic to the query afterwards as required. Is this
possible?

Cheers

The Frog

Hi

SQL of
SELECT Member.MemAddID, Member.MemberID, Member.MemSurName,
Member.MemFirstName, Member.MemPosInHome FROM Member
WHERE (((Member.MemAddID)=412));
gives

Mem Member Mem Mem Mem Mem
AddID ID Title SurNam FirstName PosInHome
412 81 Mrs. Brooke Anna 2
412 82 Miss Brooke Becci 6
412 83 Mr. Brooke Christopher 1
412 84 Mr. Brooke Jon 5
412 85 Mr. Brooke Tim 3
412 1057 Miss Edwards Elisabeth 4

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Slowness of DLookup

comp.databases.ms-access comp.databases.ms-access

Re: Slowness of DLookup - 09-28-2010 , 10:24 AM

Re: Slowness of DLookup - 09-28-2010 , 02:35 PM