Inventory Database Design Help - Conditional Relationships?

[mcarvell]

I work for a university and I am setting up an Access Database to track locations of computers and other items. There are a few tables and I will explain what is contained within each one.

Main Inventory/Location Table contains [Inventory]:

Unique University TAG Number
Serial Number
Building Code
Room Number
Station Number
Item Description
etc...

Table which contains information on each computer [SPECS]:

Unique University Tag
MAC Address
Processor
Memory
etc...

Station I.P. Addresses [STATIONIP]

Building Code
Room
Station Number
I.P. Address

Question:

The goal is to have a query that will show each station with the items located at its station along with the computer's specs and the I.P. address.

Currently I have a relationship set up from [Inventory] to [SPECS] using the University TAG Number. Also I have a relationship which relates the [Inventory] to [STATIONIP] by using the Building Code, Room, Station Number.

The problem is that this obviously associates an IP address to items which are not computers, such as monitors and projectors. Brainstorming I realize that items should only have an IP Address if the University TAG for the item is on the [SPECS] table. Is there a way I can do some sort of conditional relationship so that it only relates the I.P. address to the [Inventory] if the item is contained on the [SPECS] table? I assume this would involve visual basic coding.



I posted in the section in case someone has an idea for how to change the structure of the database by changing tables or anything that would allow a better end result. The main idea is to have the location for each item be in only one table. Also the I.P. addresses are strictly set no matter what computer is at that station, this is why I have these in a separate table.

MAC Addresses are unique to each computer but could from time to time be changed if a network card fails, so I wanted to keep it in one table as well, however maybe something clever could be done with changes cascading. I have tried thinking up several different ways to setup these relationships to make this query work well. Struggling I come to you for help =]

Thank you in Advance!

[orange]

Get a data model set up to support your needs. Use some test conditions to validate your tables and relationships.
Get your tables designed before getting too deeply involved with forms and queries.

You may get some ideas from this site, but you have some specific issues beyond this model
http://www.databaseanswers.org/data_...sets/index.htm

I recommend you DO NOT USE spaces or special characters in field or object names. It will lead to syntax issues with time.

[June7]

Never heard of 'conditional' relationships and don't think is possible, not even with code.

Options appear to be a Tag field in IP addresses table or IP address in Inventory instead of relating on Building Code, Room, Station Number