0

For reasons I cannot understand, the people who supply my tables do not have any form of single field unique identification for the 20k+ entries in their tables.

I have two fields, BuildingCode and RoomCode, which together make up a unique room. For example:

BuildingCode    RoomCode    OtherInfo
001             100A        This is room 100 A in building 1
001             101         Different room in the same building
002             100A        This is still unique, even though two 100As exist

I know I can tell access to index both of those fields under one "MainIndex" and ensure no duplicates. What I don't know how to do is allow another table to reference one of these rooms as its parent (one to many relationship).

In my Computers table, I wish to reference one specific room. That is to say, I wish to ensure that the RoomRef is a reference to one unique room. How might I do that?

ComputerSN     RoomRef
ABC123         001-100A
ABC124         002-100A

I cannot change the layout of the given Room tables due to their way of implementing data updates (in the rooms tables).

Any help is obviously appreciated!

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37
  • Is there any reason why you do not wish to use a query? Is there any reason why the look-up should not be done in a form? Look-up fields in tables are just not a good idea. – Fionnuala May 25 '12 at 14:30
  • I've thought about this, but how would I ensure I can reference the correct room if I need to, say "Select all rooms which have less than 2 computers"? – StuckAtWork May 25 '12 at 14:32
  • Let me rephrase the question... – StuckAtWork May 25 '12 at 14:45

1 Answers1

0

Room ref should consist of two fields to match the data. It is then simple enough to join in a query. For example

 SELECT DataTable.BuildingCode, DataTable.RoomCode, Count(DataTable.RoomCode)
 FROM DataTable
 INNER JOIN LookUpTable
 ON DataTable.BuildingCode = LookUpTable.BuildingCode
 AND DataTable.RoomCode= LookUpTable.RoomCode
 GROUP BY DataTable.BuildingCode, DataTable.RoomCode

Or there abouts.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • IF (big if) I followed this right, it means that you want me to use a relations table? Or are you saying I should have two fields in my computers table; one for the building, and one for the room, then use the queries to select "computers which reside in room X and building Y"? – StuckAtWork May 25 '12 at 14:58
  • 1
    You should have two fields in your computers table and any other table that needs to match the data. There are advantages in having two fields. You can have a unique index on your computer table of Building Code + Room Code + Computer ID to relate to the unique Building Code + Room Code index for the data table. – Fionnuala May 25 '12 at 15:31
  • Thank you. I think this will be sufficient. – StuckAtWork May 25 '12 at 15:48