Need help on a tough query. A challege.

[TB0NE]

I'm realatively new to Access so my knowledge of queries is restricted to the basics.

I'll start with an overview:

1. Running Access 2002.
2. I have a database with 34 tables, 0 Queries, 0 Forms.
3. The 'Computers' Table contains a list of computers on my network.
4. The 'computer_name' field is the primary key on the 'computers' table. It is the only primary key in the entire DB.
5. The 'computer_name' field is in all 34 tables in the DB and is indexed but is a primary field ONLY in the 'computers' table. The reason for this is that there may be multiple records for a single computer in another table.

Example: The 'logical_drives_table' contains 5 records for "computer5" > C: , D:, and three mapped drives.

I am trying to create a select query that will show information from multiple tables on a single line (record).SPECIFICALLY, I am trying to show all pertinent information about any single PC on a single line or record. The information is comming from multiple tables most of which have multiple records for a given PC. Only the 'computers' table contains a single record for a given PC.

Example Goal: List 'computer_name','memory', 'network_interface', 'Op_Sys','disk_capacity' on a single line.

I can successfully create a query that lists every computer with a summary of the hardware and OS AND the disk capacity of the 'C' drive but I cannot list the disk capacity of the 'D' drive on the same line. If I specify 'D' in the query I get duplicate records.

Here is a SQL view of my curreny query:

SELECT computers.computer_name, computers.operating_system_name, computers.operating_system_additional_info, computers.memory_physical, processors.processor_name, processors.processor_speed, network_adapters.adapter_name, logical_drives.logical_disk_total
FROM ((computers INNER JOIN processors ON computers.computer_name = processors.computer_name) INNER JOIN network_adapters ON computers.computer_name = network_adapters.computer_name) LEFT JOIN logical_drives ON computers.computer_name = logical_drives.computer_name
WHERE (((logical_drives.logical_disk_letter)="C&quot)
GROUP BY computers.computer_name, computers.operating_system_name, computers.operating_system_additional_info, computers.memory_physical, processors.processor_name, processors.processor_speed, network_adapters.adapter_name, logical_drives.logical_disk_total
HAVING (((network_adapters.adapter_name) Not Like "*Loopback*&quot);

This works fine if I only need the capacity of the C: drive but I cannot get the the D: drive to show up on the same line. This is because the 'logical_drives' table stores separate records for the C: and D: drive. Again, the 'computer_name' field is a primary key in the 'computers' table only. In all other tables it is simply indexed.

I tried adding a subquery in the criteria with the following:
(SELECT [logical_drives]![logical_disk_total] FROM [logical_drives] WHERE [logical_drives]![logical_disk_letter] = "D&quot

but I get the message: "At most one record can be returned by this Sub-Query"

This is how I've composed the query ..which does NOT work:

SELECT computers.computer_name, computers.operating_system_name, computers.operating_system_additional_info, computers.memory_physical, processors.processor_name, processors.processor_speed, network_adapters.adapter_name, logical_drives.logical_disk_total, logical_drives.logical_disk_total
FROM ((computers INNER JOIN processors ON computers.computer_name = processors.computer_name) INNER JOIN network_adapters ON computers.computer_name = network_adapters.computer_name) LEFT JOIN logical_drives ON computers.computer_name = logical_drives.computer_name
WHERE (((logical_drives.logical_disk_letter)="C&quot)
GROUP BY computers.computer_name, computers.operating_system_name, computers.operating_system_additional_info, computers.memory_physical, processors.processor_name, processors.processor_speed, network_adapters.adapter_name, logical_drives.logical_disk_total, logical_drives.logical_disk_total
HAVING (((network_adapters.adapter_name) Not Like "*Loopback*&quot AND ((logical_drives.logical_disk_total)=(SELECT [logical_drives]![logical_disk_total] FROM [logical_drives] WHERE [logical_drives]![logical_disk_letter] = "D&quot));


If you're still reading this then you've gotten farther than I would expect or even hope for. Which also means you may understand all of this. I surely don't. If you are brave, maybe you'll go a little further and lend an ailing rookie a hand.

Thanks very much!

 

[johnnymac43]

Take a look at thread181-511702 for a similar situation.

John

 

[TB0NE]

Thanks John!

The Cross-Tab query did the trick. My solution was actually more simple than the thread you refered me to but that definitely pointed me in the right direction.

Thanks for the tip!

For anybody that may be interested, I did the following:

I created a cross-tab query against the logical_drives table. Since any given PC has multiple logical drives (includes floppy, HDD, CD ROM, and mapped drives), I had hundreds of records for logical drives and 4 to 5 records for any given computer name. The cross-tab query cleaned all of this up and listed computer_name in the row and logical drive letters in the column. The values were listed below each drive letter. Best of all, I only have one record for each computer name.

I then took this query and added it to my Computer_Summary query, which resulted in nice overview of any given computer's hardware and OS configuration; one record (or line) per computer name.

Thanks again, John.