question of defining relationship

[herby]

Hi!

I am using a database for registering ip adresses.

now I want to know which ip adresses are still free

so I want to check a subnet 2 - 254 if there are any unused numbers in it.


My try was the following:

I created a table with only 1 field and entered the number 2 to 254

then I tried to make a query with my ip-field (last field of the ipadress) but the output shows only when both fields match.

But it should look like this:

ip-range in-use
.
.
.
45           45
46           46
47           47
48        
49           49
50           50
51
52
53           53
.
.
.

So I can see that 48, 51, 52 are free.

please help me

 

[Ally]

You need to change the relationship in the query between the two tables so that the table with the IPRange in Shows all records and only those related in the table that contains those InUse. That should do the trick.

 

[herby]

i tried all three types of relationships with no effect - everytime the same output. Do I have to change it somewhere else also ?

 

[Ally]

That's weird. You did change the relationships within the query design itself? Are you able to post your db?

 

[herby]

I have attached a screenshot what I have set - and where.

Maybe you can see something wrong there - if not I can send you the db per mail (without data).

 

[Ally]

I have looked at the db and found it a bit confusing, mainly cos I don't speak any German (apart from Eine flasche wasser bitte!) - a colleague was able to confirm it was German.

I added a few records in the IP table, then ran the query and it appeared to be doing what you need. I've attached a snapshot of it. Can you explain again what it is that you need.

(Can you or anyone else explain how to add a snapshot? Copied and pasted into Paint, then saved it as a bmp, but it was too big, so had to play around with it and save it as a jpg. Must be an easier way).

 

[herby]

I have looked at the db and found it a bit confusing, mainly cos I don't speak any German (apart from Eine flasche wasser bitte!) - a colleague was able to confirm it was German.

I added a few records in the IP table, then ran the query and it appeared to be doing what you need. I've attached a snapshot of it. Can you explain again what it is that you need.

thats already close to that what i need.

The only thing whats "wrong" is that in ip-felder the 2 exists 2 times (but in a subnet I only have the possibility to have one number unique).

The rest looks already as I want it to have.

BTW: Eine Flasche Wein bitte! is indeed more important than Wasser
and hey - I know this db is confusing - its written by me

 

[Ally]

The no 2 doesn't actually appear twice in the IP-Felder table, but it does in the query as it has been used in the IP table twice. What I think you need to do is ensure that you have no duplicates the seg-2 field - then go into your design of the IP table and change the seg-2 field's property to Indexed (No Duplicates).

See how that works.

 

[herby]

Ok I made now a filter to test it with only 1 subnet that there are no duplicates possible.

Attached is the query

 

[herby]

And here the result

...

 

[Ally]

I'm sorry - I'm a bit lost again! Is it doing what you need?

 

[herby]

hm no

because between 238 and 240 there is no line showing



239 "empty" "empty"

 

[Ally]

Am scraping around in the dark now I'm afraid. Would it be something to with the criteria for IP-Seg1?