Hello there,
I have been searching for some time now, and have been unable to find anything that fully answers the question I have regards a database I am designing.
This may mean that it is not possible, but I figured it was worth asking.
I work for a local college, and we want a database that will record a variety of information about all of our rooms (such as if they are available for outside hire, what security level they have etc.)
I have created two tables for this (that matter to the question) one called 'Building' and the other called 'Room'
'Building' contains the fields 'ID' (PK), 'BuildingName' (indexed, no repeats), 'BuildingShortCode' (indexed, no repeats), and some other fields such as notes etc.
'Room' contains the fields 'ID' (PK), 'RoomName' (indexed, no repeats), 'BuildingName', 'Floor', 'RoomNumber', 'RoomShortCode' (indexed, no repeats), and some other fields such as notes etc.
What I want to be able to do, now that the 'Building' table is complete and has been populated, is to move over to the 'Room' table and start entering data, however I need some of it to auto-fill.
I want 'RoomShortCode' to be a mix of other data. 'Room.BuildingName' needs to provide me with a drop down box of all the buildings in 'Building'. When I select a building, 'RoomShortCode' needs to autofill with 'BuildingShortCode'.
Then when I select 'Floor', if I pick, for example, first floor, 'RoomShortCode' needs to have the number 1 added after 'BuildingShortCode'.
Finally, when I fill in 'RoomNumber', this also has to be added to the end of 'RoomShortCode'
Thus if I have a building called 'Manor House', short code 'MH', when I then select room 10 on the second floor of Manor House, I should get a generated 'RoomShortCode' of MH210
Can this be done, or am I in a dream world?
Hope I explained it enough?
Thanks in advance for your help and suggestions.
Ed Rogers
I have been searching for some time now, and have been unable to find anything that fully answers the question I have regards a database I am designing.
This may mean that it is not possible, but I figured it was worth asking.
I work for a local college, and we want a database that will record a variety of information about all of our rooms (such as if they are available for outside hire, what security level they have etc.)
I have created two tables for this (that matter to the question) one called 'Building' and the other called 'Room'
'Building' contains the fields 'ID' (PK), 'BuildingName' (indexed, no repeats), 'BuildingShortCode' (indexed, no repeats), and some other fields such as notes etc.
'Room' contains the fields 'ID' (PK), 'RoomName' (indexed, no repeats), 'BuildingName', 'Floor', 'RoomNumber', 'RoomShortCode' (indexed, no repeats), and some other fields such as notes etc.
What I want to be able to do, now that the 'Building' table is complete and has been populated, is to move over to the 'Room' table and start entering data, however I need some of it to auto-fill.
I want 'RoomShortCode' to be a mix of other data. 'Room.BuildingName' needs to provide me with a drop down box of all the buildings in 'Building'. When I select a building, 'RoomShortCode' needs to autofill with 'BuildingShortCode'.
Then when I select 'Floor', if I pick, for example, first floor, 'RoomShortCode' needs to have the number 1 added after 'BuildingShortCode'.
Finally, when I fill in 'RoomNumber', this also has to be added to the end of 'RoomShortCode'
Thus if I have a building called 'Manor House', short code 'MH', when I then select room 10 on the second floor of Manor House, I should get a generated 'RoomShortCode' of MH210
Can this be done, or am I in a dream world?
Hope I explained it enough?
Thanks in advance for your help and suggestions.
Ed Rogers