Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

To give some background about the database, it contains hardware and
software information for servers and their locations. Each rack they reside
in has a number and there are numbers associated with the space they are in
the rack. For example, server A is in rack number 1, taking up units 4 and
5. I want to be able to run a report that will find any empty space in a
rack specified. How can I accomplish this?

Thanks!
 
I would think you would need to build a table with all the possible "slots"
and then link it to your server table. You could then run a query to locate
any postions without a matching entry.

There may be some other ways.
 
I am fairly new to doing queries so i'm not sure how I would do what you are
suggesting. Creating the table I understand, but how would I build the query?
 
Let me know if this is what you are looking for...The main table has a lot of
fields, but for what I am looking for there is a field for the Rack Number, a
field for the Beginning Unit and a field for the Ending Unit.
 
I don't think that structure will suit you well for what you are trying to
do. Let me suggest that you need to think of a Rack as a thing (an entity)
and of a Unit as another thing (another entity). Each entity that you have
should have a different table. Every field in the Rack table should describe
some attribute of a Rack. Every field in the Units table should describe
some attribute of a Unit. With that in mind, you might end up with a table
structure like this:

tblRacks
RackID (PK)
RackNumber
--Other fields that are related to a Rack

tblUnits
UnitID (PK)
RackID (FK to tblRacks)
UnitNumber
--Other fields that are related to a Unit.

With that in place you can then enter multiple Units for each Rack and write
a query like this to find out which Units are available.

SELECT tblRacks.RackNumber, tblUnits.UnitNumber
FROM tblRacks Join tblRacks.RackID = tblUnits.RackID
WHERE tblUnits.UnitNumber IS NULL;

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Back
Top