Report grouping question

Chirs Medico

New member
Local time
Today, 06:59
Joined
Sep 21, 2004
Messages
5
Hello,

I'm working on generating a report that will summarize our network IPs.

I'm trying to make groups in the report but keep the IP addresses sequential. Also I want to show the first and last IP of a common group and not have a large report taken up by "unassigned" or "DHCP" IP addresses.

I've included some data and the jist of what I'm trying to accomplish with the report. I hope it makes sense. :)

Any help would be greatly appreciated!

Chris

The data looks like this:

Key Description IP
1 Network ID 10.143.72.0
2 Do not use 10.143.72.1
3 Router 10.143.72.2
4 Firewall 10.143.72.3
5 HSRP 10.143.72.4
6 MSFC1 10.143.72.5
7 MSFC2 10.143.72.6
8 Medico's PC 10.143.72.7
9 Medico's Webcam 10.143.72.8
10 Unassigned 10.143.72.9
11 Unassigned 10.143.72.10
12 Unassigned 10.143.72.11
13 Unassigned 10.143.72.12
14 Unassigned 10.143.72.13
15 Unassigned 10.143.72.14
16 Unassigned 10.143.72.15
17 Unassigned 10.143.72.16
18 Unassigned 10.143.72.17
19 Unassigned 10.143.72.18
20 Unassigned 10.143.72.19
21 Unassigned 10.143.72.20
22 Unassigned 10.143.72.21
23 Unassigned 10.143.72.22
24 Unassigned 10.143.72.23
25 Unassigned 10.143.72.24
26 Unassigned 10.143.72.25
27 Unassigned 10.143.72.26
28 Unassigned 10.143.72.27
29 Unassigned 10.143.72.28
30 Unassigned 10.143.72.29
31 Unassigned 10.143.72.30
32 Ted's PC 10.143.72.31
33 Unassigned 10.143.72.32
34 Unassigned 10.143.72.33
35 Unassigned 10.143.72.34
36 Unassigned 10.143.72.35
37 Unassigned 10.143.72.36
38 Unassigned 10.143.72.37
39 Unassigned 10.143.72.38
40 Unassigned 10.143.72.39
41 Unassigned 10.143.72.40
42 Unassigned 10.143.72.41
43 Unassigned 10.143.72.42
44 Unassigned 10.143.72.43
45 Unassigned 10.143.72.44
46 DHCP 10.143.72.45
47 DHCP 10.143.72.46
48 DHCP 10.143.72.47
49 DHCP 10.143.72.48
50 DHCP 10.143.72.49
51 Unassigned 10.143.72.50
52 Unassigned 10.143.72.51
53 Unassigned 10.143.72.52
54 Unassigned 10.143.72.53
55 DHCP 10.143.72.54
56 DHCP 10.143.72.55

I want the report to look like this:
Description IP
Network ID 10.143.72.0
Do not use 10.143.72.1
Router 10.143.72.2
Firewall 10.143.72.3
HSRP 10.143.72.4
MSFC1 10.143.72.5
MSFC2 10.143.72.6
Medico's PC 10.143.72.7
Medico's Webcam 10.143.72.8
Unassigned 10.143.72.9 - 10.143.72.30
Ted's PC 10.143.72.31
Unassigned 10.143.72.32 - 10.143.72.44
DHCP 10.143.72.45 - 10.143.72.49
Unassigned 10.143.72.50 - 10.143.72.53
DHCP 10.143.72.54 - 10.143.72.55
 
Chris,

Would a missing IP address within a group be a concern?
Can you post a DB with some dummy data (easier to work with)?

Could it be done with a complex single-query or chained queries?

Is VBA OK? May be required if there are gaps.

Wayne
 
The table gets populated with data when its created. All the IPs will be contiguous. There will be no missing IP addresses. If there are any not being used they will have "unassigned" in the description field.

VBA is just fine. I've been working on building a new temporary table using VBA but I was hoping it could be done through queries. The problem so far is grouping the descriptions. I'm trying to get the data sorted by the index and then any like descriptions grouped together.

The attached file has a data table and the query that I'm working with.

Thanks for taking the time to look.

Chris
 

Attachments

Chris,

This works, but is INCREDIBLY slow ...

Code:
SELECT Distinct(A.Description),
      (Select Min(Format(B.Octet1, "000.") & Format(B.Octet2, "000.") & Format(B.Octet3, "000.") & Format(B.Octet4, "000"))
       From tblVLAN41 B
       Where A.Description = B.Description),
      (Select Max(Format(C.Octet1, "000.") & Format(C.Octet2, "000.") & Format(C.Octet3, "000.") & Format(C.Octet4, "000"))
       From tblVLAN41 C
       Where A.Description = C.Description)
FROM tblVLAN41 A
Order by A.Description;

Access is notoriously slow with nested queries.

OK, this is a quick stab at something a little better.
Look at the query NewQuery.

Wayne
 

Attachments

Thanks Wayne,

I took a look and its pretty close. I currently have it working using VBA to cycle through a recordsource. I've had to do it that way because of how queries are handeled when you use the "Group by" setup..

I may be stuck with doing it this way.. :(

Chris
 
Chris,

Using the NewQuery in the attached DB, the Access Report Writer can put
in stuff like the "-" between fields.

Wayne
 
Yea, That part is just fine.

The problem comes in when you have multiple blocks of "unassigned" or other like described IP addresses. Access groups all of them together even if they are not contiguous. The report needs to be sorted by IP address instead of description. In this situation you'll have more than one "first" record with the same description (unassigned or DHCP, etc) so I haven't found a way to do it with a query.

Take a look at the attached database and you can see what I'm trying to get to. The code in the module will create a table based on the data in the main table. I'm sure the code could be made much more efficient but it does work.

The output of the table isn't perfect but it is the closest I've come so far.

I really do appreciate the help!

Chris
 

Attachments

?Suggestion?

It might be easier to report on, if your (temporary) table was structured a little differently... I would suggest adding an EndIPRange field, then update that field, instead of adding a new record, when the end of a contiguous block is encountered.
 
I'm planning on doing that very thing. This was more of a first draft to make sure it was working. :)

Chris
 

Users who are viewing this thread

Back
Top Bottom