Computer rooms

[cp9]

Hi guys, I'm new in database design so please forgive me for my mistakes. What I trying to create is database with computer rooms.


Requirement are each room has specific amount of computers. Each computer has Pc name = room number and and pc number, serial number, model and manufacturer.

I know is not complicated I just wonder if that how it should look like. Thanks

[pbaldy]

To me, the second table is "Computers", since all the data in it relates to a computer, not a room.

[cp9]

Hi, thanks for posting in my thread. How I can make sure that I don't exceed number of computers from Rooms in RoomsDetails("Computers") table. Thanks

[pbaldy]

I'd probably use the before update event of the form you enter computers on. You can get the max with this method if you use a combo to select room:

http://www.baldyweb.com/Autofill.htm

and a DCount() to see how many computers already have that room:

DLookup Usage Samples

and cancel the update when the current computer would exceed capacity:

http://www.baldyweb.com/BeforeUpdate.htm

[pbaldy]

By the way, you could also use the before update event of the combo to select room.

[orange]

Further to Paul's advice, do you have a list of specifications/requirements?
It is often easier for readers to comment on your database design if we know the facts of the "business" the database is intended to support.
So a list of requirements, or a plain English description of the business would be useful.
Sometimes it is easier for you -the developer- to describe a day or week in the life of our business.

Good luck.

[cp9]

Well I have to use this with visual basic, the problem is can't somehow picture how to make this database. Alexandra College has many computers rooms, each computer rooms has certain amount of computers. Sometimes computers are damaged or faulty etc. Staff members have to report any faults to the system. Computer rooms should include:

The room names and the number of computers in each room, name = room number and computer number, model , serial, manufacturer.

[ArviLaanemets]

tblRooms: RoomID, RoomName, MaxComputers, ...;
tblComputers: ComputerID, Manufacturer, Model, SerialNumber, Status, ... (where Status will have values for InUse/Broken/InRepair/Scrapped);
tblComputerLocations: CLID, ComputerID, RoomID, AssignedDate, IsCurrent (where IsCurrent is true for records with Max AssignedDate for computer with AssignedDate < tomorrow's date at 00:00, and false for all others)

You need a single parent form based on tblRooms with continous subform based on tbComputerLocations with form filter set to IsCurrent = True. Both forms are linked through RoomID. For subform, you have to restrict adding new records into subform, when the current number of registered computers equals with MaxComputers for this room (you can use parent form's OnCurrent event for this).

[orange]

I'm not trying to be difficult; just trying to identify the scope of your project/application.

Things to be considered ---are they relevant to your project. Think about situations and make sure you ignore or discard them from a point of knowledge--not because you didn't think of it.

Could a computer be moved from 1 room to another?
How about removing a wall and changing a room?
How do you replace a faulty computer with another? Where would the other computer come from?

Is this a school assignment?

[cp9]

Hi Orange, please don't apologize you trying to help, I appreciate all help here. Thank you guys for your time and effort. Yes, it's a school assignment.
There is another part where you could add or remove computers or rooms.

[orange]

cp9,

Too often people want to "use Access to build" their database. Do your analysis; understand the problem and the scope--what's included and what isn't.
Here is a link to a thread from years back that discussed IT equipment in various locations.
You may get some ideas -perhaps things not to do - from the dialog in the thread.

Good luck with your project.

[cp9]

Is not that I wanted to use Access database is a requirement to use this type of database in this assignment. I appreciate link to this thread, thanks.