Form Guidence (1 Viewer)

[Ltmac70]

I have a stores data base for a cadet unit. I have a table where I want to track what specific uniform parts are issued to a cadet.

StoresIssued(table)
Cadet last name
cadet first name
ShirtID#
PantsID#
Boots ID#
+5 other items

Each type of part has its own table
ShirtTable
ShirtID (Primary Key)
Size
Status (Lookup)
Date Entered
Date Decommissioned
Notes

I will create a form to enter new inventory that is straight forward.

What I am not sure how to do is update the status of each shirt as it is issued.
My original thought was that I would make a form for StoresIssued and a subform for ShirtTable and simply update the status as I go. However, with 8 items I do not think that 8 subforms is the way to go. Not sure that is even possible at this point.

Is there a straightforward way to do this.
Access User Level 1/100 VBA User Lever 0/100

 

[plog]

You shouldn't store calculatable fields in your table. That means [Status] doesn't need to go into each item table. Instead you would query StoresIssued to see if is currently issued to someone.

Also, is there any way you can get all your items into 1 table? Does every table has the same structure as ShirtTable? Or do some tables have different fields?

 

[Ltmac70]

Good questions.
The Status has 5 possible statuses looked up from the StatusTable
issued
instock
Lost by Cadet
decommissioned
Authorized Destruction
I want the StoresIssued form to allow me to automatically switch from instock to Issued.

I have 2 types of Uniform parts those with an ID# like a shirt or boots and those that are just a stock pile like a Lanyards

This will ultimately lead to an inventory report the stock pile items I am sure I can count for my report on the number issued and the number instock so I do not have a table for those (yet) but I think I can put all of those items in one table.

As for putting all the items in one table. Yes the ID# items will all have similar fields.

I have been looking at comments and videos on line also and the one video showed that I can have multiple subforms on one master form.
Is this recomended?

 

[plog]

First you need to get your tables structured properly prior to working on forms. Actually, the next step would be reports, forms are last, so lets hold off on talking about those.

If all the item tables are the same, then you need to store all that data in one table:

Items
Item_ID, autonumber, primary key
Item_Type, text, will hold type of item (e.g. Shirt, Pants, etc.)
Item_Size, text, will hold size of item relevant to its type (e.g. Small, Medium, 32x30 etc.)
Item_Notes, text, will hold whatever notes you wanted to associate with it

You should also have a Cadet table, which just contains information about the cadet (e.g. FirstName, LastName, Rank, etc.) it will also have an autonumber primary key to uniqueyl identify the record. Let's call it Cadet_ID. This table will not store any item assignments. That will be done in a new table ItemAssignment. It will have this structure:

ItemAssignments
ia_ID, autonumber, primary key
ia_Type, text, this will hold what you are calling status (e.g. Issued, Lost, etc)
ia_Date, date, holds when the item was assigned
ID_Item, number, foreign key to Items table
ID_Cadet, number, foreign key to Cadet table

 

[Ltmac70]

If I kept all my separate tables for different Uniform Parts and added a field for issued to and put a Cadet ID number in and accessed all the tables through a tabbed form. Then I could do away with the StoresIssued or ItemsAssigned table all together. Then if I need to report on what is issued for a cadet then the ItemsIssued Report can look at all the tables to find the Cadet ID number and list all the items. Would that work?

 

[plog]

Would that work?

Maybe. Not adviseable, but maybe. You should really structure your tables properly. Then work on reports, then work on forms.

 

[Ltmac70]

I get that you need to start at the beginning but not being formally trained I lack the understanding that of how things will all come together in the end, and what is possible and not possible. So I am not necessarily building forms and reports and queries at this point but I am trying to figure out how my tables will interact with all these items.

In this case the stores part of the Data Base has never been built before for this organization but it is a small part of a larger overall database.

Is there something that I can watch or read that can teach me the do's and don'ts of table structure? I do understand that the table structure is the foundation that the entire data base rests on so getting it right to start makes everything after that much easier.

Most of my knowledge is from Excel spreadsheets and making them be the little brother of a data base.

You sound like you have lots of experience please show me the way.

 

[Frothingslosh]

You can look up tutorials on 'database normalization' or 'access normalization'; there are a number of good ones available on YouTube.

In a (very rough) nutshell, though, it's based on the idea that every table is about one 'thing', and only that 'thing'.

Thus, you need one table for cadets, one for items (that way it can handle shirts, caps, and any other gear you may need in the future), and a table for the actual assignments/transactions/activity. This last one would actually record every action done with an item - assignment, return, lost, set for repair, received back for repair, etc.

I would also move action types (Issued, Lost, etc) to a fourth table with an ID field and save that ID in the assignments table rather than plog's suggested ia_Type (making the new field long integer). Others here may disagree on that - some people very much dislike the use of two-field lookup tables. I personally always recommend them due to ease of updating and how they enforce data conformity.

Before anything else, though, go watch a tutorial or two on normalization.

So basically, plog's data structure.