→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper
I have the following report.

Select Top 1000000 tblAssets.AssetID,
tblIDEController.Caption As Caption1
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblVideoController
On tblAssets.AssetID = tblVideoController.AssetID
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tblIDEController On tblAssets.AssetID = tblIDEController.AssetID

As most of the machines have 2 network adaptors, 2 video adaptors and 3 or more IDE controllers I'm getting a line for each combination of those, so
net adaptor 1 with VGA adaptor 1 and IED adaptor 1
net adaptor 2 with VGA adaptor 1 and IDE adaptor 1
net adaptor 1 with VGA adaptor 2 and IDE adaptor 1

I understand that there is no way to completely flatten this to a single line but I'd like to understand if there is a way to more intelligently join these bits of data to reduce the total number of data output?
Champion Sweeper III
As you mentioned getting multiple rows is expected when there is more than one line of data for a field in a table. By simply linking the tables differently you will most likely not get any changes as you are still calling the same tables.

Howerver, There are ways to combine multiple rows into a single row. These do require having advanced knowledge of SQL. You can find information on how to do this online such as here and here.