Query that concatenates and counts (1 Viewer)

[LJEsposito]

We have a marketing system that monitors campaign links clicked:

Contact | Link
===========
Larry | Link D
Nancy | Link A
John | Link B
Larry | Link A
Larry | Link A
Larry | Link C
Nancy | Link D
Nancy | Link D

Ideally I'd like a query that summarizes as follows:

Contact | Links clicked
=================
Larry | Link A 2 time(s); Link C 1 time(s); Link D 1 time(s)
John | Link B 1 time(s)
Nancy | Link A 1 time(s); Link D 2 time(s)

My hunch is that I have to use VBA but was hoping I was overlooing something because I'm not a programmer although I have used VBA before.

Thanks,
Larry Esposito

 

[pbaldy]

Does this query get you what you want (it will be vertical, not horizontal):

SELECT Contact, Link, Count(*) AS HowMany
FROM TableName
GROUP BY Contact, Link

You can probably get the horizontal look with a crosstab query.

 

[LJEsposito]

Thanks for the quick Repy but that didn't work - it provided the following:

Contact | Link | How Many
====================
John | Link B | 1
Larry | Link A | 2
Larry | Link C | 1
Larry | Link D | 1
Nancy | Link A | 1
Nancy | Link D | 2

But I did find another thread that may suit my needs:
http://www.access-programmers.co.uk/forums/showthread.php?t=64611

I'll check it out.

Thanks again,
Larry Esposito

 

[pbaldy]

Well, that produces the correct numbers, but apparently not the format you want. You might try the crosstab query wizard as I mentioned, which I suspect will produce the format you want. Or my query above combined with code like in your link or this:

http://www.mvps.org/access/modules/mdl0004.htm

 

[LJEsposito]

The code fix in the link I had provided above did work for me so I documented my efforts there.

Thanks for your advice Paul! While I liked the CrossTab query (if it would avoid VBA) I needed far more fields than I was listing in my example that would render it very cumbersome if useful at all. So I followed my gut and went with the VBA which fortunately didn't require quite as much pain as I expected.

Thanks again,
Larry

 

[pbaldy]

Glad you found a working solution.