Horizontal Query Results?

Profector

Registered User.
Local time
Today, 15:03
Joined
Mar 15, 2006
Messages
33
I'm working getting the countries we've shipped to listed by item. It's was pretty easy up to the point where I was asked to list all of the countries for that item on the same line.

The way it's listed now is

Item1 Description Country
Item1 Description AnotherCountry
Item1 Description AndAnotherCountry

What i'm trying to get is

Item1 Description Country, AnotherCountry, AndAnotherCountry

Crosstab and Pivot queries aren't getting the job done.

Any ideas?
 
So create a function and call it in the sql query. I can do that ...I think :)

Thanks for the links.
 
No problem; post back if you get stuck.
 
I thought I had it using the function from the first link. It looks like the data populates correctly on the screen when it's ran, but it just sat there and never seemed to finish. I couldn't select anything and query process bar at the bottom wasn't there. I trying to export it to excel now. The progress bar for that is ticking along slowly, so hopefully that gives me what I need.

SELECT Items.Customer, Items.CustPart, Items.ItemPart, Items.Description, concatrelated("Countries","[Countries]","Countries.[ItemPart] = """ & Items.[ItemPart] & """")
FROM Items;
 
It runs for every record returned by the query, so if the tables are large it can certainly slow things down.
 
Why doesn't cross tab work?

Another question, how many different countries. Mike Alexander has a trick I make your own cross tab in a select query but it's kind of a pain if you have too many countries.
 
There's about a dozen or so countries and a little less then 1000 items. The data has the items listed more than 100,000 and the country is list once for each of the 8000 orders, using round numbers.

The way I put together the cross tab query didn't display the data the way I needed it. If you know of a way I'm more than happy to try. I've already got the data formatted for this report, but I'm going to have to do it many times over the next few months so a faster query would be nice to have. :)
 
Update:

The final query was based on other queries. I exported the data I needed to new tables and ran the final query against those tables; now it finishes within seconds. I just need to add a few lines to clear the table and update it before the final query runs and I'm golden. That won't be any problem.

Thanks for the help.
 

Users who are viewing this thread

Back
Top Bottom