0

I connected my Access DB to an Excel file and executed a few simple SQL queries and I want to do something that I don't know if it is possible.

I want to group values in columns when the values repeat.

For example, here is a simple query results:

ID | Name | Color 1 John Red 1 John Yellow 2 Bill Red 2 Bill Blue 2 Bill Black

I would like the results to be presented in that way:

ID | Name | Color 1 John Red Yellow 2 Bill Red Blue Black

Is that possible?

Appreciate your answers!

Thanks.

Gilad K
  • 17
  • 5
  • Using sql alone, it is not possible. What do you need it for? – j.kaspar Mar 31 '16 at 07:05
  • I need it for convenient data appearance, because the results of my query are much bigger than that simple query. – Gilad K Mar 31 '16 at 07:10
  • @ImranAli All the tags are relevant: Using Excel to connect my Access data source and executing SQL query. – Gilad K Mar 31 '16 at 07:11
  • @GiladK If you are generating a report in MS Access it is possible to produce the way you want it. You just need to follow the Access Report Wizard and group your data on the Name column – Imran Ali Mar 31 '16 at 07:24
  • @ImranAli I can't create the report - it fails everytime because of too many selected fields which I must have them all. – Gilad K Mar 31 '16 at 07:59
  • 1
    @GiladK Are you familiar with using VBA? it would be simple to move down columns `ID` and `Name` and if the value is equal to the value above then clear the cell. This method is easier once you have created the excel file. – LiamH Mar 31 '16 at 08:05

3 Answers3

1

This can not be a result of a SQL query, the query will display the values in 'ID' and 'Name' fields associated with 'Color' field, if they exist. moreover, in the query result there will be nothing associating Blue to Bill rather than Jhon.

If your goal is to display in Excel, you can have code run through Cols ID and NAME, bottom to top, and delete cells whom values equal to the cell above.

marlan
  • 1,485
  • 1
  • 12
  • 18
0

Here you go..Nothing is Impossible..

create table tbl_table
(Id int,
Name varchar(32),
Color varchar(16)
)
go

insert into tbl_table
values(1,'John','Red');
insert into tbl_table
values(1,'John','Yellow');
insert into tbl_table
values(2,'Bill','Red');
insert into tbl_table
values(2,'Bill','Blue');
insert into tbl_table
values(2,'Bill','Black');
go

select a.id,case when a.Id=b.Id and a.rowid<>b.rowid then NULL else a.Name end,a.Color
from
(
select ROW_NUMBER () OVER (Order by Id asc ) rowid,* from tbl_table
) a
left join
(
select ROW_NUMBER () OVER (Order by Id asc ) rowid,* from tbl_table
) b
on a.rowid-1=b.rowid

Thanks

Andre
  • 26,751
  • 7
  • 36
  • 80
Rajesh Ranjan
  • 537
  • 2
  • 12
0
create table tbl_table
(Id int,
Name varchar(32),
Color varchar(16)
)
go

insert into tbl_table
values(1,'John','Red');
insert into tbl_table
values(1,'John','Yellow');
insert into tbl_table
values(2,'Bill','Red');
insert into tbl_table
values(2,'Bill','Blue');
insert into tbl_table
values(2,'Bill','Black');
go

select a.id,case when a.Id=b.Id and a.rowid<>b.rowid then NULL else a.Name end,a.Color
from
(
select DCount("[ID]","[tbl_table]","[ID]<=" & [ID]) rowid,* from tbl_table
) a
left join
(
select DCount("[ID]","[tbl_table]","[ID]<=" & [ID]) rowid,* from tbl_table
) b
on a.rowid-1=b.rowid

Try This

Andre
  • 26,751
  • 7
  • 36
  • 80
Rajesh Ranjan
  • 537
  • 2
  • 12
  • @Andre Hi, It doesn't work for some reason. I get error message: "Syntax error (missing operator) in query expression 'case when a.Id=b.Id and a.rowid<>b.rowid then NULL else a.Name end'." and it marks the word "when" right after I click on OK. – Gilad K Apr 03 '16 at 08:38
  • @GiladK: I didn't write this code, I just formatted it. :) You need to use IIf() instead of Case When. – Andre Apr 03 '16 at 08:42
  • I replaced the case when with IIf() and now error indicates on DCount. I receive the same error as I got with the 'case when', with 'DCount' now.. – Gilad K Apr 03 '16 at 11:08