Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    46

    Unanswered: Complex Query/Report

    Help I have created a big database that stores IP information for Pc's, Printers, Switches, UPS

    TableSwitch
    IP
    Name
    EquipID (Always 2)

    TablePrinter
    IP
    Name
    EquipID (Always 1)
    PrinterQueue

    TableReservation
    IP
    EquipID
    MAC
    Switch
    Port

    Example a reservation contains the IP and the device ID, switch name and port number.

    I have reservations for switches as well as these can be daisy chained together.

    The boss has asked me to create a report it a switch is knocked out, that be is simple but, the tricky part is to get the report or query to display the data the first switch in the daisy chain is knocked out.

    Example
    Switch1
    Printer 1 and 2 connected
    switch 2 connected

    Switch 2
    Printer 3 and 4 connected
    switch 3 connected

    Switch 3
    Printer 3 connected

    So far the query can tell me which devices are connected, but if I selected switch 1 I would it to tell me that everything is effected

    Can any one help with this, I think its going to involve a lot of subforms

  2. #2
    Join Date
    Apr 2007
    Location
    Connecticut
    Posts
    1

    You may have to change your table structure

    Greetings!

    Probably not what you want to hear but you may have to make a change in your table structure. Somewhere you need to establish a Parent/Child relationship between your connected devices such that the first device in the chain doesn't have a value in the parent field but all of the subsequent devices do. Once you've established this you can work backwards from the device you are interested in ( or forward if you are starting with a parent device ) to get a listing of all the devices that are 'related' to the one you are questioning. I have used this a lot with employee tables where you have to track who reports to who, along with their titles and department information, all the way up to the top of the organization.

    v

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •