Problem with repeating results

[lukee20]

Hi,

I am trying to write a report detailing all products keys for some Microsoft products. But for some reason the reports contains the exact same user/pc multiple times, creating one massive report! Can anyone help?

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Username, tblADusers.Displayname, tblSerialnumber.ProductKey,
tblSerialnumber.Product
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain Inner Join
tblSerialnumber On tblComputers.Computername = tblSerialnumber.Computername
Where (tblSerialnumber.Product Like 'Microsoft Office Project%') Or
(tblSerialnumber.Product Like 'Microsoft Office Visio%') Or
(tblSerialnumber.Product Like 'Microsoft Project%')

[AngelEye88NL]

I have the same problem. I made a report with the computer name, date of last seen, IP address, total amount of memory and the CPU, but I get a giant report with alot of double results.

Distinct is already added. The report:

Select Distinct Top 1000000 tblcomputers.ComputerUnique, tblcomputers.Computername, tblcomputers.LastknownIP, tblcomputers.Lastseen, web40CorrectMemory.Memory, web40ProcessorCapacity.CPU From tblcomputers, web40CorrectMemory, web40ProcessorCapacity Where tblcomputers.LastknownIP Like '192.168.30.%'

[Hemoco]

AngelEye88NL wrote:
I have the same problem. I made a report with the computer name, date of last seen, IP address, total amount of memory and the CPU, but I get a giant report with alot of double results.

Distinct is already added. The report:

Select Distinct Top 1000000 tblcomputers.ComputerUnique, tblcomputers.Computername, tblcomputers.LastknownIP, tblcomputers.Lastseen, web40CorrectMemory.Memory, web40ProcessorCapacity.CPU From tblcomputers, web40CorrectMemory, web40ProcessorCapacity Where tblcomputers.LastknownIP Like '192.168.30.%'

Please try the report below. You did not link your tables to tblComputers, which is necessary.

When linking tables to tblComputers you will be most commonly linking the “Computername” fields in both tables. You do this by “grabbing” (left-click and hold) the Computername field from one table and dragging it to the Computername name field of the other table. Let go of your left mouse button to see the connection appear. Right-click on a connection and choose Remove to delete it.

Select Distinct Top 1000000 tblComputers.ComputerUnique,
tblComputers.Computername, tblComputers.LastknownIP, tblComputers.Lastseen,
web40CorrectMemory.Memory, web40ProcessorCapacity.CPU
From tblComputers Inner Join
web40CorrectMemory On web40CorrectMemory.Computername =
tblComputers.Computername Inner Join
web40ProcessorCapacity On web40ProcessorCapacity.Computername =
tblComputers.Computername
Where tblComputers.LastknownIP Like '192.168.30.%'

[AngelEye88NL]

Worked! Thanks very much. 🙂

[Hemoco · Accepted solution]

Could you try the following report instead:

Select Distinct Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Username, tblADusers.Displayname, tblSerialnumber.ProductKey,
tblSerialnumber.Product
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
tblADusers On tblComputers.Username = tblADusers.Username And
tblADusers.Userdomain = tblComputers.Userdomain Inner Join
tblSerialnumber On tblComputers.Computername = tblSerialnumber.Computername
Where (tblSerialnumber.Product Like 'Microsoft Office Project%') Or
(tblSerialnumber.Product Like 'Microsoft Office Visio%') Or
(tblSerialnumber.Product Like 'Microsoft Project%')