Relationships dropdowns possible? DB refreshing possible?

[zigax20]

Hey,

I'm very unexperienced in Access. I'm working on a project at work and I would like to know couple of things:

http://shrani.si/f/1L/UI/2qwRn09E/relations.png

I work in a big company where we have lots of users and lots of new computers in storage that we will prepare in next months/years for these users.



So I have two entities - Users and Storage. And I have plans for a few years in advance. I would like to add in Plan2015 two things: user (from Users) and computer (from Storage). And when I do that, computer won't be in Storage anymore so I would like that it is automatically deleted there. Is that possible in Access 2010?

Explanation just in case: I have 10 users and 7 computers in storage. I am gonna give these 7 computers to 7 users and I want them removed from Storage as soon as I put them in Plan2015.

Second Q: When I am filling Plan2015 with user and computer, can there be a dropdown for both of them? So i dont write J o h n D o e, but can just scroll down to his name.

I am looking forward to your replies!

Best regards,

Ziga

[June7]

A table for each year is poor design. Should be one table with another field for year identifier.

Record will not automatically delete from Storage just because you entered a record in Plan. Deleting records should be a rare event. Instead of calling the table Storage call it Computers and this table will have info about computers in inventory. Then the Plan table will be a junction table between Users and Computers.

Use comboboxes to list users and computers.

[zigax20]

I updated my 'poor design' as you said.

I know it will not be automatically deleted but can it be? Or at least I add another field 'Available' and it goes from Yes to No as soon as I add computer in Plan, is that possible?

[June7]

Would require code.

Whether or not a computer is available can be determine by query. Maintaining a field is not necessary.

[zigax20]

How can it be determined by query? I have no info if that particular computer is still in storage or not when i have no 'yes/no' field.

[June7]

If the computer is assigned to a user, then it must not be in storage.

Do you care about history of computer assignments?

Since a computer can be assigned to only one user, if history is not important, usually just need to put UserID into Computer record. If there is a UserID then the computer is not in storage.

However, your Plan table does complicate. Exactly what are you planning - assigning computers to users? How can you know that years in advance?

[zigax20]

Yes, I agree. As soon as computer is assigned to a user, it should not be available anymore - I would prefer that it's erased to be honest.

I care about history of assignments. It's a huge company and everything must be 'by the book'.

My plan table basically consists of: UserID, ComputerID, year of planning and exact date of 'computer being assigned to a user'. Yes, I am only assigning computers to users. Main issue I still have (I'm not working on this project all the time ofc) is how to simplify everything - that is why I want computers removed from storage when assigned - they are not there anymore - or updated field 'availability' from yes to no.

Well, company buys 50 or more computers at a time so it takes time then to assign them to users. I have a field 'priority' next to every user and that field actually tells me which users have "an advantage" of getting new computer. So I simply put UserID and ComputerID and put 2015 (or maybe even 2015/1, so 1st half of 2015) in and someone from my department will prepare computer for that user. If you think of better solution than this, I welcome it.

It is really a small problem that should be easily solved (later I am taking all this to next level - with other tables, forms etc) but I am completely new in this area and I find it quit confusing (like I have some SQL knowledge but I can't even find where to write code in Access atm :/).

[orange]

zigax,

There is a post in the forum that deals with IT equipment -- some is held in inventory and some is at a specific location. Something at location X can be swapped with something in Inventory.

It's not a perfect fit, but your new computers could be considered Inventory, the locations could be assignments to people.
You may get some ideas from the thread and the sample database.

Good luck.

[June7]

The plan table is to prioritize users receiving computers when computers are acquired? Don't even have computers in hand yet? It isn't the computers that need to be 'deleted' from storage, it's the record from Plan when user finally gets their computer.

Actually, since you need history, possibly this 'Plan' table could serve that purpose. Instead of 'Plan', call the table ComputerAssignments and include a field for AssignmentDate. Future dates can be input. Any record with a date later than current date is a 'planned' assignment. Any record without a ComputerID is unfulfilled.

Point is, deleting records should rarely be necessary in a well-designed db. Especially since you say history is important.

[zigax20]

orange,

thank you, will check it out.

June7,

well we could call it that. Like if we get 50 new computers, 40 will be 'reserved' for users with highest priority. We have computers already in storage. Well if I simply remove a record from Plan, I will not know if that computer is still in storage or not. And I also would have no info for that particular user. I also want priority for user to be changed when they get new computer.

[orange]

zigax,

Whatever you decide, be sure to get all your facts and rules identified before getting to deep into Access. Get a data model that truly reflects your requirements. Test your model with some data and scenarios, and reconcile every issue that may arise.
There's more info here that may be useful.

[June7]

Data structure I suggest would not involve deleting records from any table.