Solved

Access Count query - also need to concatenate fields

Posted on 2004-08-17
46
2,452 Views
Last Modified: 2010-05-18

I'm trying to do an Access query, using a table with the following fields:

DocID
P1N1_SurName
P1N1_FirstName
P1N1_Initial
P1N2_SurName
P1N2_FirstName
P1N2_Initial
P2N1_SurName
P2N1_FirstName
P2N1_Initia       
P2N2_SurName
P2N2_FirstName
P2N2_Initial

Each document record consists of DocID, and fields for the party names.  There are two vendors and 2 buyers for each document.  I need to concatenate the first name, initial and last name for each of the 4 parties involved.

My query needs to show how many records contain each full name. For example, there are 27 documents with John P. Murphy as vendor (either name 1 or name 2, doesn't matter), and there are 32 records with John P. Murphy as buyer (either name 1 or name 2).

I want the results to look something like this:

John P. Murphy      Party1   27
John P. Murphy      Party2   32
James G. Boggan   Party1   41
James G. Boggan   Party2   40
Mary C. Smith       Party1   19
Mary C. Smith       Party2   23

Do I have to use a count function???

All you experts out there, please help me - I need to have this done before Wednesday evening!

thanks!
rodarim
0
Comment
Question by:rodarim
  • 26
  • 9
  • 8
  • +1
46 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11822956
To get the full name from your table:

Select DocID, P1N1_FirstName + ' ' + P1N1_Initial + ' ' + P1N1_Surname as P1N1_FullName FROM MyTable

For the counting part of it, it sounds like you may have a design issue to contend with in order to make this easy.  Perhaps there is some normalization we can implement on your tables?  Either that, or code the routine through VB.  Explain a little more about your project, if you would...
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11823015
Messy, messy, messy....

You need to look at sorting your table out first.
I'd create one table:

ID    FirstName   SurName    Initial

Then append in your results.
Querying this table will be mcuh, much easier.
0
 

Author Comment

by:rodarim
ID: 11823053

Thanks!  I'm going to have a look at your suggestions now...

But I'm curious about the design issues - what's wrong with the design?  Just wondering what I can do to improve it.

thanks,
rodarim
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11823119
Ideally, you should have one table for vendors and one for buyers.
So you have (Vendor)

VendorID     VendorName   VendorDetails etc...

and (Buyer)

BuyerID    BuyerName    BuyerDetails   etc...

Then have a sales table:

OrderID   BuyerID   VendorID   Details

Relate through BuyerID and VendorID back to your other tables, allowing duplicates.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 11823122
This is called "Normalising" and enables your database to be smaller and faster
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11823133
In general, if you have to label fields with numbers to indicate order, there is something wrong with your design.

I do not know alot about your project right now, but my first guess would be to arrange your tables like this:

Main Info Table
-----------------
ID - PK/autonumber/whatever
FirstName
LastName
Initial
MorePersonalInfoFields

Docs Table
------------
DocID - PK/autonumber/whatever
VendorPrimary
VendorSeconday
BuyerPrimary
BuyerSecondary

Those last four fields should be FKs related to the ID field of the main table.  This way you will information on everyone you can POTENTIALLY use in a document in one table, and who is being used in which documents in an easy-to-use format.  With this design, you could easily do something like this for your count:

SELECT MainInfo.ID, MainInfo.FirstName + ' ' + MainInfo.Initial + ' ' + MainInfo.LastName as FullName, 'Vendor' as PartyType, count(Docs.*) as MyCount from MainInfo, Docs
WHERE MainInfo.ID = Docs.VendorPrimary or MainInfo.ID = Docs.VendorSecondary
UNION
SELECT MainInfo.ID, MainInfo.FirstName + ' ' + MainInfo.Initial + ' ' + MainInfo.LastName as FullName, 'Buyer' as PartyType count(Docs.*) as MyCount from MainInfo, Docs
WHERE MainInfo.ID = Docs.BuyerPrimary or MainInfo.ID = Docs.BuyerSecondary

I haven't tested this....just off the top of my head.  But it should give you a good idea on where to go with it.  If you would like more specifics, we'll be here.
0
 

Author Comment

by:rodarim
ID: 11823304
Ok, I don't think I explained the design very well to begin with.  We have 2 tables - one for properties and one for documents.  Each property record has one or more related document records in the document table.  There are 4 party names associated with each document and we need to enter them into the document record they relate to.  To have separate tables for vendors and buyers would defeat the purpose - in fact, the party names are not always vendors and buyers.  Sometimes they are mortgagors and mortgagees, sometimes they are power of attorneys - it all depends on the document type.

We call them Parties 1 and 2, Names 1 - 2, to keep it generic.  This is just extra information that we collect about each document.

The tables actually are normalized - we had them reviewed by a database expert at a local university.

The reason I only mentioned the document table, not the corresponding property table, is that for the purpose of this query, we only need use the document records.  I tried to simplify my initial description to make it easier to understand and explain.

Does that make more sense?

thanks,
rodarim
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11824270
While I agree with not having separate tables for vendors and buyers, I do not agree that your design is optimized for your project.  As you stated, vendors are not necessarily ALWAYS vendors.  Someone who sells a house can just as easily buy one.  In fact, someone who sells a house will probably need to buy one somewhere along the way.  :)  Because of that, you do not need information on vendors and buyers.  You need contact information for individuals/companies who can then be designated inside a document as a vendor/buyer/financeer/etc.

A big indicator that your data is not normalized is the fact that the documents table repeats data.  As it is right now, if you have a buyer, you submit all their information into a particular document.  If that buyer turns around and becomes a vendor, you again have to enter all their information into a second document.  Normalization would have you entering all of a particular contact's information into a central repository (say, a contact information table), and assigning those records to documents as needed.  Since the relative function of each person is described by how they are placed in the document, it is unnecessary to differentiate between buyers or vendors anywhere else, and it is much easier to submit a unique ID pointing to all the personal information than to type it all in again for a new document.  

From another view, it is MUCH easier to find number 'x' in the documents table than it is to find a concatenation of name fields.  This also removes the possibility of mistyping a name.  For example, in your current system, one document records an individual name as James P Smith.  Another document may record it as James P. Smith.  The two are NOT identical (the period on the initial), and they will be picked up as separate records for the purposes of your count.  You might say that strict data entry oversight can handle that problem.  I would respond that depending on users to do the right thing, even with the clearest and most concise of instructions, is begging for migraines.  Not to mention that trying to code a routine that handles and accounts for all the possible variations of a person's name is not just an unacceptable approach...it's damn near impossible.  On the other hand, a client ID of 42 will always equal client ID 42, and differences in abbreviations or input methods are not going to matter much when comparing on the ID field.

To sum up, what you want is possible with your current design, but I would not depend on SQL to do it.  I would use VB to pull all the names, then cycle through the records one at a time to count how many times that particular name appears.  With the slight structure change I've described to you, you could use SQL for the count query very easily.
0
 

Author Comment

by:rodarim
ID: 11830126

Thanks for the advice, routinet - I really appreciate it.  The thing is - while I agree in theory, I'm not convinced that what you suggested would be ideal for our purposes. The sole reason for including the party names is to display them when somebody views a report on the property. Users cannot search by party name, and they are not used for any other purpose other than to display them on the report.  What you suggested might work, but I don't know that it's necessary. But you're definitely more knowledgeable than me, so perhaps I'm wrong. :-)

Anyway, my boss wants this query done so he can pull out the names of the party names that occur most often, for marketing purposes - he wants to target them as potential users of our application - that's the sole reason for doing this query, and it's definitely an afterthought.

I really can't re-design the database at this point in the game - we've developed our entire data entry application and front-end website aroudn the database, and it's running quite smoothly and efficiently right now.  So I'm going to have to stick with what we've got right now - maybe sometime down the road. :)

Now, it would probably be easier if I did one query to concatenate all the names and put them in a separate table, then run the count query on that table, wouldn't it?  I'll try it.

I'll keep you posted. :)

thanks again for all your advice - I appreciate it!

rodarim
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11830691
yupyup!  That's one way you can get it done.  :)  Remember that your counts will not be absolute...you'll likely have 2 or more versions of the same name spread throughout the data.  If pinpoint accuracy is not an issue, though, it will work just dandy.  

I know the kind of hassles caused by a shift in design, especially when the app is already running 'as advertised' and the clients are happy.  When you do have the opportunity, I would still recommend making that change.  Once it is in place you will be able to see the advantages.  It will probably be painful, considering the queries and reports you will have to alter, but it would be worth it, IMO.
0
 

Author Comment

by:rodarim
ID: 11830798
Thanks routinet - and you're right, pinpoint accuracy isn't really an issue.  the documents themselves aren't always consistent even - in some documents, a person's name might be give with the initial, sometimes without, sometimes they're spelled differently.

another thing about the design you mentioned before - there might be 42 John Murphys, but most likely there aren't all the same person, so it would be hard to know who's who to have a party names table. :-)  but anyway, youve amde some great points!

I'm going to try this now! :-)

rodarim
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11832209
Good luck with it!  I apologize that we were unable to find a better solution for you.

Please drive through to the next window, and please come again! :)
0
 

Author Comment

by:rodarim
ID: 11833058
okay, I used this query to concatenate the full names...

SELECT DocID, DocType, P1N1_FirstName & ' '+P1N1_Initial & ' '+P1N1_Surname AS P1_FullName
FROM tblRegistryInfo
ORDER BY DocID;

And then I used this query to do the count...

SELECT P1_FullName, Count(*)
FROM qryPartyNameConcat
GROUP BY P1_FullName;

Works like a charm!!!!  But that was a simplified version and now I need to add in a few more things.  I need to add the second party name into the first concatenation query.  I tried the following but it doesn't work. Any ideas?? I want the concatenated fields to go into the same field regardless of whether it's party 1 or party 2.  thanks!

SELECT DocID, DocType, P1N1_FirstName & ' '+P1N1_Initial & ' '+P1N1_Surname AS P1_FullName,  P1N2_FirstName & ' '+P1N2_Initial & ' '+P1N2_Surname AS P1_FullName
FROM tblRegistryInfo
ORDER BY DocID;

0
 

Author Comment

by:rodarim
ID: 11833072
Just to clarify, I know why it doesn't work - it thinks I'm trying to name both new fields the same thing, which is P1_FullName.  WHat I really want to do is put the data from both into the same new field!

thanks!
rodarim
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11834522
I think this is what you are aiming at:

SELECT DocID, DocType, P1N1_FirstName + ' ' + P1N1_Initial + ' ' + P1N1_Surname AS FullName FROM tblRegistryInfo
UNION
SELECT DocID, DocType, P1N2_FirstName + ' ' + P1N2_Initial + ' ' + P1N2_Surname AS FullName FROM tblRegistryInfo

This does the same query twice.  First it pulls Name 1, then it pulls Name 2, and finally it merges both recordsets into one large recordset for returning the data.

0
 

Author Comment

by:rodarim
ID: 11840656
excellent, thanks so much!  it's all working great now.

just one more thing I need to do - when I use the group by statement, it gives me all the party names and the number of times they appear as a party name.  excellent.

now what I need is to get only the ones that appear 5 or more times as a party name - is there an easy way to do this???

thanks!!!!!
rodarim
:-)
0
 

Author Comment

by:rodarim
ID: 11840906
What I'm trying to say is select * WHERE count is > 5
But I can't do it exactly like that.
Hmmm......

0
 

Author Comment

by:rodarim
ID: 11841358
I also want to be able to include just the party names and frequency of occurence in the first query (and only those with frequency of 5 or more), then in a 2nd query, break it down and include the frequency by doctype as well.  For example, it would look like this:

John Smith   Conveyance   45
John Smith   Mortgage   51
John Smith   Power Attorney   77
Mary Jones   Conveyance   43
Mary Jones   Mortgage   19
Mary Jones   Power Attorney   12
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11842201
Taking this example from one of your previous posts, here is how you would filter for anyone showing up 5 times or more:

SELECT P1_FullName, Count(*)
FROM qryPartyNameConcat
GROUP BY P1_FullName
HAVING count(*) >= 5

The HAVING clause is essentially the WHERE clause, as implemented specifically for GROUPing.  You should not include both (HAVING and WHERE) in a single SQL.  Perhaps another expert can explain any other technical differences between the two, but as far as I've seen in my work they can be substitute for each other without any issues.
0
 

Author Comment

by:rodarim
ID: 11842449
routinet, you are a GOD - or a goddess, I'm not sure haha! THANK YOU!  I had it working with a Top 100 type thing, but I had to know in advance how many were greater than 5. Worked, but required some manual intervention. :-)  Your way works much better - thanks!

I have one more question - here is my query right now.

SELECT trim(P1_FullName), Count(*) AS freq
FROM qryPartyNameConcat
GROUP BY trim(P1_FullName)
HAVING Count(*)>4
ORDER BY Count(*) DESC;

And here is my initial query where the concatenation of the names is done:

SELECT DocID, DocType, P1N1_FirstName & ' '+P1N1_Initial & ' '+P1N1_Surname AS P1_FullName FROM tblRegistryInfo
UNION SELECT DocID, DocType, P1N2_FirstName & ' ' & P1N2_Initial & ' ' & P1N2_Surname AS FullName FROM tblRegistryInfo
ORDER BY DocID;

Now, what I want to do is group by doc type as well.  I wanted to get all the frequencies > 5 (which is the first query above) and of those, group by doc type.  But I couldn't include the doc type in that query, because if i put DocType in the select statement, I also had to include it in the GROUP BY clause, right?  I want to include it in the GROUP BY only after the frequencies > 5 have been selected.

Any ideas?  Once this is figured out, I'm DONE.

Thanks soooooo much!!!

rodarim
0
 

Author Comment

by:rodarim
ID: 11842513
If I include the DocType in the query here, it will miss certain data.  If Bob Jones appears 2 times in conveyance docs and 2 times in mortgage docs and 2 times in other docs, he won't ever make the cut of >5, even though in total he does appear mroe than 5 times.  make sense?  

I want to FIRST get all the names that appear in the documents more than 5 times, THEN of those I want to break it down by doctype.

I hope I'm explaining this well. :-)

thanks
rodarim
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 50 total points
ID: 11843074
What you are looking for might be best described by a crosstab query.  I've never used them, and only have a passing acquaintence with how they work.  I will direct some other experts to this question to help with the syntax if necessary, but here's my first try:

TRANSFORM Count(a.DocID)
SELECT a.P1_FullName FROM qryPartyNameConcat a
WHERE EXISTS
    (SELECT P1_FullName, COUNT(*) FROM qryPartyNameConcat b
     GROUP BY P1_FullName
     WHERE COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
GROUP BY a.P1_FullName ORDER BY a.P1_FullName
PIVOT a.DocType

If that doesn't work, I'll call in the calvalry.
0
 

Author Comment

by:rodarim
ID: 11843179
oh LORDIE that looks complicated haha!  thanks so much - i'm off to try it!!!!!

rodarim
:-)
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:rodarim
ID: 11843194
I got a syntax error in this part, so I'm going to try and figure out what it is. :-)

 EXISTS
    (SELECT P1_FullName, COUNT(*) FROM qryPartyNameConcat b
     GROUP BY P1_FullName
     WHERE COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
0
 

Author Comment

by:rodarim
ID: 11843326
ok, I changed the WHERE to a HAVING and it let me save it with no syntax errors anyway!!  but when I try to run it, it tells me Access doesn't recognize a.P1_FullName as a valid field or query.

TRANSFORM Count(a.DocID)
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE EXISTS
    (SELECT P1_FullName, COUNT(*) FROM qryPartyNameConcat b
     GROUP BY P1_FullName
    HAVING COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT a.DocType;
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11843503
Try this small change:

TRANSFORM Count(DocID)
SELECT P1_FullName
FROM qryPartyNameConcat AS a
WHERE EXISTS
    (SELECT P1_FullName, COUNT(*) FROM qryPartyNameConcat AS b
     GROUP BY P1_FullName
    HAVING COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
GROUP BY P1_FullName
ORDER BY P1_FullName
PIVOT DocType;
0
 

Author Comment

by:rodarim
ID: 11843588
Hmmm, I still get the same error - does not recognize a.P1_FullName as a valid field or object. :(
I'm still playing around with it though.....

THANKS!!!!
rodarim
0
 

Author Comment

by:rodarim
ID: 11843640
could it be because P1_FullName is a field made by a query??
0
 

Author Comment

by:rodarim
ID: 11843868
I cannot figure out for the life of me what could be wrong with this query!!

0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11843920
I think you need to include qryPartyNameConcat in your subquery, my syntax might need some work though:

TRANSFORM Count(DocID)
SELECT P1_FullName
FROM qryPartyNameConcat AS a
WHERE EXISTS
    (SELECT P1_FullName, COUNT(*) FROM qryPartyNameConcat AS b, qryPartyNameConcat AS c
     GROUP BY c.P1_FullName
    HAVING COUNT(*)>4 and c.P1_FullName = b.P1_FullName)
GROUP BY P1_FullName
ORDER BY P1_FullName
PIVOT DocType;
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11843985
slight change:

TRANSFORM Count(DocID)
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE a.P1_FullName IN
    (SELECT c.P1_FullName, COUNT(*) FROM qryPartyNameConcat AS b, qryPartyNameConcat AS c
     GROUP BY c.P1_FullName
    HAVING COUNT(*)>4 and c.P1_FullName = b.P1_FullName)
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT DocType;
0
 

Author Comment

by:rodarim
ID: 11844049
Okay, I used that code and got an error saying P1_FullName in the FROM clause could refer to more than 1 table - so I changed the code a bit to look like this...

WHERE EXISTS
    (SELECT b.P1_FullName, COUNT(*) FROM qryPartyNameConcat AS b, qryPartyNameConcat

and then i got...

"you tried to execute a query that does not include the specified expression "P1_FullName" as part of an aggregate function"

If memory serves me correctly, that's because I had "P1_FullName" in my SELECT statement, but not anywhere else, right?

THANKS!!!!!!!!!!!
rodarim
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11844116
Whoops!  I though there was 2 queries in use here.  Let me wipe that egg off my face...now try this:

TRANSFORM Count(DocID)
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE a.P1_FullName IN
    (SELECT b.P1_FullName, COUNT(*) FROM qryPartyNameConcat AS b
     GROUP BY b.P1_FullName
    HAVING COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT DocType;
0
 

Author Comment

by:rodarim
ID: 11844130
Mourdekai, thanks for your help!  when Iput in your revised code, I got this error:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

i'm going to research that error now...

thank you!!!!!!
rodarim
0
 

Author Comment

by:rodarim
ID: 11844162
ok, trying your 2nd revised code now. :-)

thanks!
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11844169
Just take out the second field in the subquery:

TRANSFORM Count(DocID)
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE a.P1_FullName IN
    (SELECT b.P1_FullName FROM qryPartyNameConcat AS b
     GROUP BY b.P1_FullName
    HAVING COUNT(*)>4 and a.P1_FullName = b.P1_FullName)
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT DocType;
0
 

Author Comment

by:rodarim
ID: 11844186

Here's my current error....

The Microsoft Jet engine does not recognize a.P1_FullName as a valid field or expression.

You guys are certainly earning your points today - I wish I had more to allocate!!!!!!!!!!!!

rodarim
0
 

Author Comment

by:rodarim
ID: 11844205

Here is what qryPartyNameConcat looks like...

SELECT DocID, DocType, P1N1_FirstName & ' ' & P1N1_Initial & ' ' & P1N1_Surname AS P1_FullName
FROM tblRegistryInfo
UNION SELECT DocID, DocType, P1N2_FirstName & ' ' & P1N2_Initial & ' ' & P1N2_Surname AS P1_FullName FROM tblRegistryInfo
ORDER BY DocID;

It should be fine, right?  THere's definitely a P1_FullName created there!!

0
 
LVL 8

Accepted Solution

by:
Mourdekai earned 90 total points
ID: 11844464
Try this out:

TRANSFORM Count(a.DocID) AS CountOfDocID
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE (((a.P1_FullName) In (SELECT b.P1_FullName FROM qryPartyNameConcat AS b
     GROUP BY b.P1_FullName
    HAVING COUNT(*)>4 and P1_FullName = b.P1_FullName)))
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT a.DocType;
0
 

Author Comment

by:rodarim
ID: 11844588

ok, it's running, but VERY slowly - 4 minutes so far and only halfway done.  should it take that long?  all the others only took a few seconds so I'm wondering what it's doing haha! :-)

thanks so much, Mourdekai!

rodarim
0
 

Author Comment

by:rodarim
ID: 11844599

are crosstab queries generally this slow?

rodarim
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11844670
It depends on how many records, and the complexity of the query.  This query isn't as simple to compute as some, but it's not extremely complex either.

How many records are in the qryPartyNameConcat?
0
 

Author Comment

by:rodarim
ID: 11844700
there are 67,000 records in qryPartyNameConcat...
i'll leave it running and see what happens.
thanks so much for all your help!!!!!!!!!!!

rodarim
:-)
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11845021
I can see why that's taking a bit to run.  Since the query is using a correlated or dynamic subquery, it has to run once for each row.  So the subquery is being run 67,000 times.

We can take out the dynamic part of the subquery and still end up with the same result.  How it affects performance, I can't tell since I'm only testing on ~50 records.  Give this a try and see if it's quicker:

TRANSFORM Count(a.DocID) AS CountOfDocID
SELECT a.P1_FullName
FROM qryPartyNameConcat AS a
WHERE (a.P1_FullName IN (SELECT b.P1_FullName FROM qryPartyNameConcat AS b
     GROUP BY b.P1_FullName
    HAVING COUNT(*)>4 ))
GROUP BY a.P1_FullName
ORDER BY a.P1_FullName
PIVOT a.DocType;
0
 

Author Comment

by:rodarim
ID: 11854122
THANKS, Mourdekai - it WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  I'm not sure how long it took as I left it running overnight, but it did take over 3 hours to export it to Excel this morning!  I'll keep your revised query in mind for when I run it again next week.

Thanks to everyone who helped me, especially Mourdekai and routinet. I'm splitting the points because you both helped me a lot! :-)

Thanks again!
rodarim
0
 
LVL 8

Expert Comment

by:Mourdekai
ID: 11854214
Glad you got it working rodarim!  Sounds like it ran for quite a bit.  I'd be interested in knowing if the revised query works faster/better.  If you can post with results when you do it again, that would be great!

Also, one more thing, if the data isn't already local to your machine, you could try inserting your qryPartyNameConcat to a temporary table on your local machine, then run the crosstab query on that local table.  That might speed things up a bit further.

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now