One Row, Ten Territories

[TVBFIXIT]

I can do this via DAO or ADO, but it would be annoying, My instincts tell me it can be done via SQL. Table like this:

ID Name Territory
1 A USA
1 A Canada
2 B USA
2 B Canada
2 B Mexico

SQL to make it

ID Name Territory
1 A USA, Canada
2 B USA, Canada, Mexico

Yes, I know...don't ask, I didn't design it. Thanks in advance - TVB

 

[dhookom]

You could create a temporary table of just the unique ID and Name fields with a third field that is sufficiently large and blank. Make sure the ID and Name fields are the primary key of this new field. Then create an update query that updates the third field with the value of the third field & Territory.

For instance, if this was the Northwind and you wanted to concatenate all of the product names for each category into a table of [CategoryID] and [AllProducts]

UPDATE Products INNER JOIN tblCategoryProducts ON Products.CategoryID = tblCategoryProducts.CategoryID 
SET tblCategoryProducts.AllProducts = [AllProducts] & [ProductName] & ", ";

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cmmrfrds]

In Access SQL.
1. create a query that ranks the data. This can be done with a self join on the table. Use a differentiator that will be different in each of the records where the join keys are equal. For example, ID and Name are equal and Territory is <=.

Here is the general idea, but not checked.

select a.id, a.name, count(*) as row
from yourtable a,
yourtable b
where a.id = b.id
and a.name = b.name
and a.territory <= b.territory
Group by id, name

Save this query and then join yourtable to the saved query and use the row # to build multiple columns of territory.

select a.id, a.name,
iif(row=1,a.territory,'') as t1,
iif(row=2,a.territory,'') as t2,
iif(row=3,a.territory,'') as t3,
etc....
from yourtable a
savedquery b
Group by id, name

You should be able to find examples on this site. Search for ranking or row numbering.

 

[lespaul]

you can copy and paste the function found in FAQ701-4233.....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dhookom]

<soapbox>
I wish I knew why OPs rarely if ever provide table names. Also, if you suggest they don't use names like "name" they assure you this was just an alias they gave to make it easier for us.
</soapbox>
This SQL might work if you can handle multiple columns:

TRANSFORM First(tblWhatItsName.Territory) AS FirstOfTerritory
SELECT tblWhatItsName.ID, tblWhatItsName.Name
FROM tblWhatItsName
GROUP BY tblWhatItsName.ID, tblWhatItsName.Name
PIVOT "Column" & DCount("*","tblWhatItsName","ID=" & [ID] & " AND [Name] = '" & [Name] & "' AND Territory <='" & [Territory] & "'");

[tt][blue]
ID Name Column1 Column2 Column3
1 A Canada USA
2 B Canada Mexico USA
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]