Crosstab query....kind of.

[mbowler9]

Hello,

I have an Access situation that I haven't been in before, but I'm sure it is much easier than it seems. I have a table called "ips" (as in IP addresses). The table is very simple simplconsisting of a server name, domain, and IP. The problem is that each system can have up to 4 IP addresses and the table would show that like:

server1 domain IP1
server1 domain IP2
server1 domain IP3
server1 domain IP4

I have another existing table that I would like to add onto that would list the IPs like so:

server1 domain IP1 IP2 IP3 IP4

Is there a way to do this? Thanks

 

[mbowler9]

By the way, I do realize that this is bad table design. This table is only being created to export into excel, not for actual data storage. I would be just as happy to just export the query.

Thanks again.

 

[lespaul]

what happens when server1 has 25 IP addresses? Are you going to add to that table 21 more fields? Your first table structure conforms to 3NF, why would you want to make a flat, unnormalized table?

Leslie

 

[mbowler9]

It won't have 25 IP addresses, only 4. This isn't the main table that gets updated from our website. I am simply creating a "report" and have decided to do this in a table, or as I said above, I would be happy to do this through a query. I just want to export to excel.

Thanks

 

[lespaul]

Simultaneous posting!

I've been trying to think how you would do this without another field. If the table was:

Server Domain IPNumber IPAddress
Server1 domain 1 Address
Server1 domain 2 Address

then it would be fairly straight forward, but without an identifing field of some kind, I'm stuck!

I'll keep thinking and maybe a REAL guru will show up and take care of it!

leslie

 

[PHV]

Try something like this:
SELECT A.ServerName,A.Domain,Min(A.IP) As IP1,Min(B.IP) As IP2,Min(C.IP) As IP3,Min(D.IP) As IP4
FROM ips As A LEFT JOIN (ips As B LEFT JOIN (ips As C LEFT JOIN ips As D
ON (C.ServerName=D.ServerName And C.IP<D.IP))
ON (B.ServerName=C.ServerName And B.IP<C.IP))
ON (A.ServerName=B.ServerName And A.IP<B.IP)
GROUP BY A.ServerName,A.Domain;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

There is (was? Should be? a procedure on this site (Tek-Tips) which has a routine to do the "Transform" of columns to rows. I have (somewhere?) adapted it to a generic transform procedure, but cannot reference it at the moment (probably my poor keeboarding). The procedure -as posted- was somewhat more sprcific than I wanted, but was quite workable. I think (or hope?) that "Transform" would be sufficient in Advanced SEarch to find thje original.

A slightly different (and more obvious) soloution would be to output the table as is and use the Excel version of treansform, modifying the results if necessary.



MichaelRed
mlred@verizon.net

 

[JonFer]

You need to create a rank for each IP address assigned to a server and then you can use a crosstab query with the Rank as the Column Heading. I have tried to use a subquery to calculate the rank before but the crosstab query didn't like it so I had to do a self join like this:

Select t1.Server, t1.Domain, t1.IP, Count(t2.IP) as Rank
From ServerIPs as t1 Inner Join ServerIPs as t2
On t1.Server=t2.Server and t1.Domain=t2.Domain
Where t2.IP<=t1.IP
Group by t1.Server, t1.Domain, t1.IP

Note that you are counting the IPs from the second instance of the table.

 

[mbowler9]

JonFer,

I think that your solution looks the easiest for me to do. The only issue I see is that you have a "Count(t2.IP)" in it, but table 2 doesn't yet have any IPs in it. Can this be modified?

Thanks

 

[mbowler9]

I'm also not quite sure what the "ServerIPs" are referring to. Is this supposed to be one of my existing fields?

Thanks

 

[lespaul]

t2 is an alias for your existing table.

Select t1.Server, t1.Domain, t1.IP, Count(t2.IP) as Rank
From [COLOR=red]ServerIPs[/color] as t1 Inner Join [COLOR=red]ServerIPs[/color] as t2
     On t1.Server=t2.Server and t1.Domain=t2.Domain
Where t2.IP<=t1.IP
Group by t1.Server, t1.Domain, t1.IP

To get the above query to work, change the ServerIPs to YOUR tablename.

HTH

leslie

 

[PHV]

mbowler9, have you tried my suggestion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

PHV's solution produces results for 1-4 IPs with 1 query and 4 joins.

My solution produces results for 1-200+ IPs with 2 queries and 1 join.