Too many rows :)

[Sisel]

Hi i have 3 tables with relations...

SELECT dbo_v_Configuration.SEARCH_CODE, dbo_V_SERVICE.NAME
FROM (dbo_V_CONFIGURATION_ITEM_ON_SERVIC INNER JOIN dbo_v_Configuration ON
dbo_V_CONFIGURATION_ITEM_ON_SERVIC.CONFIGURATION_ITEM_SEARCH_CODE =
dbo_v_Configuration.SEARCH_CODE) INNER JOIN dbo_V_SERVICE ON
dbo_V_CONFIGURATION_ITEM_ON_SERVIC.SERVICE_NAME = dbo_V_SERVICE.NAME
WHERE (((dbo_v_Configuration.SEARCH_CODE) Like "PRG-SV*"))
ORDER BY dbo_v_Configuration.SEARCH_CODE;

And now i have problem, cause i have releated on some search_code, more than
one service.name. It will make me a new row with same search_code and
different service.name.

I want to known if its possible to get new column(s) with service.name
istead of new row with search_code for example...

This i got
SEARCH_CODE NAME
PRG-SV2010 SALE
PRG-SV2010 MADRID

And this i want
SEARCH_CODE NAME NAME
PRG-SV2010 SALE MADRID

I hope someone help me with this easy question

 

[Sisel]

No one help me, thats not good...

Sisel píše:

 

[Allen Browne]

There is not a simple way to get SQL to return the values like that.

You could create the query based just on the dbo_v_Configuration table, and
use a VBA function to get a concatenated list of the names associated with
it. The code to do that is here:
http://www.mvps.org/access/modules/mdl0004.htm

Alternatively, you could use a crosstab query, where dbo_V_Service.[NAME]
gives the Column Headings, and the intersection points indicate yes or no.
(Crosstab is no the Query menu in query design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi i have 3 tables with relations...

SELECT dbo_v_Configuration.SEARCH_CODE,
dbo_V_SERVICE.NAME
FROM (dbo_V_CONFIGURATION_ITEM_ON_SERVIC
INNER JOIN dbo_v_Configuration
ON dbo_V_CONFIGURATION_ITEM_ON_SERVIC.CONFIGURATION_ITEM_SEARCH_CODE
= dbo_v_Configuration.SEARCH_CODE)
INNER JOIN dbo_V_SERVICE
ON dbo_V_CONFIGURATION_ITEM_ON_SERVIC.SERVICE_NAME
= dbo_V_SERVICE.NAME
WHERE (((dbo_v_Configuration.SEARCH_CODE) Like "PRG-SV*"))
ORDER BY dbo_v_Configuration.SEARCH_CODE;

 

[Sisel]

Thanks for that. Now i can go to fight with that script, cause crosstab table
is not good option for me

Allen Browne píše:

There is not a simple way to get SQL to return the values like that.

You could create the query based just on the dbo_v_Configuration table, and
use a VBA function to get a concatenated list of the names associated with
it. The code to do that is here:
http://www.mvps.org/access/modules/mdl0004.htm

Alternatively, you could use a crosstab query, where dbo_V_Service.[NAME]
gives the Column Headings, and the intersection points indicate yes or no.
(Crosstab is no the Query menu in query design.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi i have 3 tables with relations...

SELECT dbo_v_Configuration.SEARCH_CODE,
dbo_V_SERVICE.NAME
FROM (dbo_V_CONFIGURATION_ITEM_ON_SERVIC
INNER JOIN dbo_v_Configuration
ON dbo_V_CONFIGURATION_ITEM_ON_SERVIC.CONFIGURATION_ITEM_SEARCH_CODE
= dbo_v_Configuration.SEARCH_CODE)
INNER JOIN dbo_V_SERVICE
ON dbo_V_CONFIGURATION_ITEM_ON_SERVIC.SERVICE_NAME
= dbo_V_SERVICE.NAME
WHERE (((dbo_v_Configuration.SEARCH_CODE) Like "PRG-SV*"))
ORDER BY dbo_v_Configuration.SEARCH_CODE;

And now i have problem, cause i have releated on some search_code, more
than
one service.name. It will make me a new row with same search_code and
different service.name.

I want to known if its possible to get new column(s) with service.name
istead of new row with search_code for example...

This i got
SEARCH_CODE NAME
PRG-SV2010 SALE
PRG-SV2010 MADRID

And this i want
SEARCH_CODE NAME NAME
PRG-SV2010 SALE MADRID

I hope someone help me with this easy question