Hi Sam,
Alrighty....You can probably do this using a GROUP BY clause in your SQL
squery to get those records and also add a DISTINCT to your select query as
follows:
SELECT DISTINCT * FROM <TableName> GROUP BY HarwareName
or by IP Address
SELECT DISTINCT * FROM <TableName> GROUP BY IPAddress
This will eliminate unnecessary duplicates from your result: However since
you one record per application installed it will still show you two rows.
Only thing I can think of is to use something else to do the reporting and
concatenate the SoftwareNames into a comma seperated string like your
desired result show. Maybe programmatically in a vb sub. or using a cursor
object in SQL Server to select everything then loop through the records
(which is the purpose of a cursor object) and say that a variable is :
@ConcatenatedApps = @ConcatenatedApps + SoftwareName
If you really need this done from the database side and not the Application
side of things, you could create a stored procedure to concatenate the
applications and call it from a main stored procedure that woudl create the
query into a temp table
then the stored procedure could loop each object contitionned on the IP
Address for example and when that changes create a noew row of result
data....
It's doable...but be warned that depending on the amount of data you're
dealing with, performances might be dramatically affected using this method.
In VB, once you have the resultset as shown in your example then you could
loop through them and concatenate them yourself...(Say your recordset object
is called AppsSet):
Dim IPAddress As String
Dim PCName As String
Dim Installed As String
Do while not AppSet.EOF
PC Name = AppSet.Fields('HardwareName')
IPAddress = AppSet.Fields('IPAddress')
Do While IPAddress = AppSet.Fields('IPAddress')
Installed = Installed & AppSet.Fields('SoftwareName') & ", "
AppSet.MoveNext
Loop
' When you exit this last loop, you have all the info for one PC
' Get rid of the last comma added with:
Installed = Left(Installed, Len(Installed) - 1)
' the Information is now ready to print or add to a temporary recordset
' for eventual showing or printing.
' Then you loop up and start over with the next PC (IP address)
Loop
Hope this helps
Stéphane Richard
Senior Software and Technology Supervisor
http://www.totalweb-inc.com
For all your hosting and related needs
"berthelot samuel" <sa**************@voila.fr> wrote in message
news:ca**************************@posting.google.c om...
Hi everyone,
I am currently trying to write a report based on a View of SQL Server.
Basically, I have 3 tables : Hardware, SoftwareInstalled and Software
with SoftwareInstalled that keeps track of all the software installed
on each piece of hardware by referencing the primary keys of each
table.
So now, I have a request that retrieve information from those 3 tables
giving a list of all the hardware with their details + the software
installed.
ex:
hardware name IPAddress Software name
---------------------------------------------
PC1 192.168.0.1 Winamp
PC1 192.168.0.1 Nero
PC2 192.168.0.2 Winamp
I can link this View in VB with a DataEnvironment object and then I
display the result with a DataReport. Ok. The problem is that PC1 is
displayed twice and this is a waste of place. I want PC1 being
displayed only once with the list of software like this :
Hardware Name : PC1
IPAddress : 192.168.0.1
Software : Winamp, Nero
I am really desesperate to get that working...
If someone knows how to do this, please let me know !
Thanx.
Sam
