SQL Coding Help.... please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Coding Help.... please

Author

Topic

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 15:45:26

Hi,
I am trying to code sql that will take the table here:

Site No Name Num_Gr Artifacts Skelly No
1 Apple 3 Y 1
1 Apple 3 Y 2
1 Apple 3 Y 3
2 Bear 2 Y 4
2 Bear 2 Y 5
3 Cat 1 Y 6
4 Dog 1 Y 7
5 Egg 1 Y 8
6 Foot 2 Y 9
6 Foot 2 Y 10

and remove only the duplicate NUM_GR so that it will look like this:

column. It should look like this.

Site No Name Num_Gr Artifacts Skelly No
1 Apple 3 Y 1
Y 2
Y 3
2 Bear 2 Y 4
Y 5
3 Cat 1 Y 6
4 Dog 1 Y 7
5 Egg 1 Y 8
6 Foot 2 Y 9
Y 10

See how the duplicates for NUM_GR are gone but the rest of the information in the Row stays?

Is there a way to code some SQL that will do this for me?

Thanks for anything you can tell me,

Barb

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 15:55:58

SORRY... IT DIDN'T FORMAT CORRECTLY WHEN I PASTED...

I am trying to remove duplicates but keep the other values within the row.. I'll try again

site_no name gr ar number

1 apple 3 y 1
1 apple 3 y 2
1 apple 3 y 3
2 bear 2 y 4
2 bear 2 y 5

I'd like it to look like this:

site_no name gr ar number

1 apple 3 y 1
y 2
y 3
2 bear 2 y 4
y 5

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 16:04:43

Use code tags around your data to retain formatting. Like this:

[ code ]
Formatted code or data goes here.
[ /code ]

Just remove the spaces from the code tags. I had to add them so that they were visible in this post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 16:18:51

is it formatted now?

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 16:20:11

[code]
I am trying to remove duplicates but keep the other values within the row.. I'll try again

site_no name gr ar number

1 apple 3 y 1
1 apple 3 y 2
1 apple 3 y 3
2 bear 2 y 4
2 bear 2 y 5

I'd like it to look like this:

site_no name gr ar number

1 apple 3 y 1
y 2
y 3
2 bear 2 y 4
y 5

[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 16:32:36

What you have there should be handled in the application and not inside SQL. Your problem is a formatting one and not a duplicate data query issue. If this were a report, you'd just add groups to do this. But I'm not a developer, so I can't help with the specifics of the application code that is needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 16:35:05

I'm extremely new to SQL so please don't yell, do you mean it should be handled when creating the Table? and now it's too late?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 16:40:08

Who is yelling? I'm just letting you know that this is a formatting problem and not a SQL one.

You don't handle this when creating the table. This will be handled inside your application and not inside the database. Hopefully someone who knows Access will be able to point you into the right direction to get the grouping correct in your code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

barb0802
Starting Member

7 Posts

Posted - 2007-10-03 : 16:45:21

I didn't mean you were yelling.. sorry... I was just hoping that because I was going to appear very, very, dumb, that it wouldn't lead to yelling... ha ha I was trying to let you know that I may be a little stupid and bother you with many questions... :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-03 : 16:54:45

In the Access reporting tool, you can either create group header to do what you want, or check off "hide duplicates" on the site_no, name and gr fields on the report.

- Jeff
http://weblogs.sqlteam.com/JeffS

barb0802
Starting Member

7 Posts

Posted - 2007-10-04 : 09:06:38

Hi There,

I tried using the Hide Duplicates, but then it hides all the 1's, that aren't really duplicates but separate rows... Doesn't seem like I can do it at a column level...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources