We've got a PC database which has one yes/no field which stores if the PC is up to MS patching standards. When the next round hits, we clear it out and re-check till 100%
What we're trying to do now is have a seperate table with each field representing the next patch and tie that to the main table. So on our main form they click a button, and a new window pops up showing the status.
The problem is, I don't know access well enough to tie the two tables together, or if that's even the best way to go about it. I have no idea where the information would be stored. Lets say I want to store patch status on the last 13 updates; where does that information get stored? If I keep adding fields to the main table the DB would get very bloated would it not? Or is that the only way to go about it since each machine is going to be yes/no for each patch field anyway (wont be saving space).
Any design tips would be greatly appeciated, access was just thrown on me.
i can't really follow what you are doing with the patch status. however, it sounds like the best way to establish this database would be to create two new tables. i would create a patches table, which had the patch details as well as patch id. then i would create a computers_patched table. this table would be used to establsh the relationship between the patches and the PCs. it would only consist of the primary key, the patch id and the pc id (or any other details you want). i am by no means an ER Modeling expert, but i do think that splitting many to many relationships like into two one to many relationships can simplify development.
One for the PC's thier informaion and the like.
One for the patches with thier information.
One for the what had been patched wherwe the ID from pc table is entered and the Id of the patch table is entered alonng with a date or any other information you need.
I have attached a small DB with just the table structure and relatioships of the way I would do it.