Having one field that will need multiple sub fields

[Babbage]

I am creating a database of all buildings on a site and the use of rooms. As such I have the building number against which I need all room numbers. This will create duplicate numbers i.e. rooms 1, 2, 3, 4, etc against each building. Against each of the rooms will be further information i.e. user(s), nos desks, type CIS, etc. How can I set up the tables to reflect one building and multiple rooms against which a lot of data can be put

[MozgafD1]

how many buildings and how many rooms against them?

you could have something like this...

Buildings Rooms Users more details....
Bd1 Rm1 Usr1,User2
Bd1 Rm2 Usr1,User5
Bd1 Rm3 Usr1,User2
Bd1 Rm4 Usr1,User6
Bd2 Rm1 Usr4,User6
Bd2 Rm2 Usr1,User2
Bd2 Rm3 Usr2,User3
Bd2 Rm4 Usr1,User5
Bd2 Rm5 Usr3,User4


is this what you are talking about?

[Babbage]

Hi

Very many thanks for getting back.

Yes I am looking for something like this. I have created a table for Bldg information and am not sure where to put / link the rooms. Should I create a separate table? Apologies - I have basic knowledge of Access. Regards

[Babbage]

Sorry, you also asked about nos of bldgs. / rooms. There are approx. 90 buildings which contain 1 to 159 rooms. Does the example you give put them all in one table? Regards

[MozgafD1]

Hey,
depending on what and how you use the information, you have two options.
option 1 have a table for each building. This will allow you to make the room number as primary key. to make sure there is no duplicates.

option 2 have everything in one table with all data. There would be 15000 records roughly which access can handle with ease but not other applications. you would also need add another column for ID to be a primary key for no duplicates.

[Babbage]

HI

Many thanks. Option 1 sounds the neatest option - would you recommend this? Thanks so much for the advice. Regards

[NeoPa]

Let me jump in here before you make a big mistake.

Option #1 is a disaster waiting to happen. Don't go there. You would surely regret it (as many before you have).

I strongly recommend reading Database Normalisation and Table Structures. It's the top most read article on the site for a very good reason - It's so fundamental to database design.

For your particular requirement you would need :
A Building table with an appropriate PK (Primary Key) set.
A Room table with an FK (Foreign Key) that points to the relevant Building record. This may or may not be incorporated into the PK depending on your preference. Typically it is simply an FK and your PK is made unique in another way by using an AutoNumber generated value that will be unique across the whole table of rooms.

Reading the article through will give you a much better understanding of how to set up your data structure. This is, by far, the most important part of a database's design. So many people do it at the point where they are the least experienced and this leads to all sorts of problems.

[Babbage]

Many thanks - I will read the article before I go further. Regards

[zmbd]

sigh... let me apologize for the length of this post in advance. Not only am I going to point you to a resource, I'm going to provide a working example.

+ First and Foremost, one should rarely if ever use the primary key for anything meaningful in the human world. Numbering schemes can, and often do, change - see below for an example.

+ You should read Database Normalization and Table Structures

one method is a pedigree table setup - Allen Browne's Self Join example for horse pedigree

I have one such database where I track documents (several thousandsssssss ssssss (ok, not that many :) just seems like that when I go to purge uggh).... anyway... locations are:
Bulding>Room>Co ntainer(ie File Cabinet, banker's box, etc>Folder(or other)>document ID

Thus when we recently moved about 15 banker's boxes from building one to building two, room six (names changed) I didn't have to go in and do this for each document in those boxes (YIKES!!!), instead, I went in to my locations query and altered the record that pointed at the building and room.

So this whole scheme is based on: Allen Browne's Self Join example for horse pedigree

I have the following tables setup... in this example, in all cases, [PK] is an AutoNumber field, [FK_*] is a numeric(long) referring to the [PK] in the named table and a relationship has been established between the tables, all other fields tend to be text(255)).
[tbl_locations]
[tbl_documentloc ation]![pk]
[tbl_documentloc ation]![FK_Locationtype]
[tbl_documentloc ation]![LocationName]

[tbl_locationtyp e]
[tbl_locationtyp e]![pk]
[tbl_locationtyp e]![LocationTypeNam e]

[tbl_document]
[tbl_document]![pk]
[tbl_document]![DocumentName]
[... a few other fields here...]

I will mention this table because we had multiple versions/revisions of the same document in the system... I needed a way to tack their locations and purge/replace. Pulling that information out in to its own table was the only way I saw to track these documents. You may not need this table and one could easily alter the following tables to use the [tbl_document] instead.
[tbl_documentver sion]
[tbl_documentver sion]![pk]
[tbl_documentver sion]![fk_document]
[tbl_documentver sion]![VersionID]
(there are a few other fields here too)

[tbl_LastKnownLo cations]
[tbl_LastKnownLo cations]![pk]
[tbl_LastKnownLo cations]![fk_documentvers ion]
[tbl_LastKnownLo cations]![fk_pedigree]
[tbl_LastKnownLo cations]![SerialNumber]

(note, I have not restricted [fk_documentvers ion][fk_pedigree] as a compound index. If I have multiple copies of a document in the given location, I have duplicate (triplicate, etc...) entries, one for each copy. The [SerialNumber] is the field I use to track the copy. At one time we used YYYYMM_##### now we use a totally different scheme; thus, why I do not use the [PK] value. Aggregate query will return my counts for copies!

OK the magic! Looking at AB self links example, I noted [Sire] and [Dams]. However, for me, each location only sources from one prior location (SJK = self joined key:
[tbl_pedigree]![PK]
[tbl_pedigree]![FK_Locations]
[tbl_pedigree]![SJK_FeedsFrom]

to make the self joins I had to follow our document flow
Campus>Building >Room>Container >SubContainer1> SubContainer2

So how does this work for a document or two, say one document 2 versions; three locations
Document15; Rev2K1005C;
SN:201507_0001 at C1>B2>R304>FC2> D1>HF15
SN:201507_0015 at C1>B2>R304>FC2> D1>HF15

Document15; Rev2K1507A;
SN:201507_0001 at C1>B3>R100
(Room 100 is an personal office. So this copy is checked out to the occupant of that office and I have no idea where the document is physically located, that's the responsibility of that individual... and if lost... the bus that runs over them is HUGE!)

So say I have
[tbl_documentver sion]
[255][15][Rev2K1005C]
[....]
[456][15][Rev2K1507A]

[tbl_locations] (I'll abbreviate this at tad, location types are omitted, I use these for filtering...)
[1][Campus 1]
[... entries for each campus... these are at top here; however, order of entry doesn't matter...]
[202][Bldg2]
[203][Bldg3]
[... same with buildings...]
[3005][room 100]
[3006][room 304]
[... you should get the idea here]

These are just names... I do not enter room 100 eight times for the eight buildings... just once. One entry per name.
Have 500 hanging folders... 250 of them are named "January" then one entry "January" and the location type would point to "hanging folder"
There may be a better way, such as an expansion table; however, I've not found a better way... yet!


so let's build our two locations... [blank] is no entry
C1>B3>R100
C1>B2>R304>FC2> D1>HF15

I'll build each in order using the above example data:
C1>B3>R100
becomes: 1>203>3005

and might be
C1>B2>R304>FC2> D1>HF15
1>202>3006>4000 2>500001>600000 15

[tbl_pedigree]
[PK][FK_Locations][SJK_FeedsFrom]
[1][1][blank]
[2][203][1] << see how this back feeds to [PK]=1
[3][3005][2] << and then again... C1>B3>R100 done
[4][202][1]
[5][3006][4]
[6][40002][5]
[7][500001][6]
[8][60000015][7] <<< "C1>B2>R304>FC2 >D1>HF15" done


Finally to the document:
[tbl_LastKnownLo cations]
[tbl_LastKnownLo cations]![pk]
[tbl_LastKnownLo cations]![fk_documentvers ion]
[tbl_LastKnownLo cations]![fk_pedigree]
[tbl_LastKnownLo cations]![SerialNumber]
so the entries would be
[1][255][8][201507_0001]
[2][255][8][201507_0015]
[3][456][3][201507_0001]

Moveing a file cabinate (FC2) from building 2 to building 3 room 100 is simply changing the pedigree records [pk]=6 so that the feed points to [pk]=3 instead of to 5

NOW the pedigree query looks complex in AB example, and it is; however, once setup, it's easy to use. Unfortunately my version is at work and I have family calling me right now; thus, I'll try to update this with a version of it... Monday

I also have a form that uses cascading combo boxes based on the query to filter down the pedigree list so that I can change locations more easily.

[Babbage]

Very many thanks for your time and help.