Need assistance for my inventory database

[undee69]

good day again.



I want to ask for another assistance from your guys.

I want to create an inventory of all the items that were given to our employees and count all the given items.

I already have the tblemployee and the items that are given to each employee. These items are divided into three Categories (tableCategory): Maintenance, Safety and Administration.

I tried to normalize the tables and I got lost.

So now I have a table as follows:

tableEmployee
EmpID: Autonumber
FName:
MidName:
LastName:

tbleItems
ItemID: autonumber
CatID: Number <- FK for tableCategory
EmpID: Number <- FK for tableEmployee
Description: Text
Quantity: Number

tableCategory
CatID: autonumber
CatName: Text

On the employee Form, I want to show the Three Categories on separate subform and it will display all the items under Category. and beside that the quantity of the item that were given to them.

Attached is my blank database

[June7]

Need to include the EmpID in the GROUP BY queries the subforms are based on. The tblItems doesn't have any data in EmpID field.

[orange]

Based on your description of what you are trying to do, I have attached 2 jpgs.
First to show a junction table between Items and Employees. This is used to resolve the Many Employees are assigned Many Items to become This Employee was assigned this Item on this date. Note the composite unique index to prevent assigning duplicate items to the same employee on the same date.

Also, a possible data structure to meet your described needs.

Items can be grouped by Category. Employees are supplied/assigned Items. An Employee may be given an AssignedQuantity of a specific Item on a particular Date (AssignedDate).

I don't think this is Inventory Control/Management in the common use of the term.

Good luck with our project.

[undee69]

Thanks for the input guys

@Orange.. In the end, I would be tracking how many of the items were given for each category that's why I'm doing this database.

In the subforms of FrmItemsCounter, I would want the items to be shown as per category and input the given quantity to every employee.

I am not able to enter the values in the subforms of FrmItemsCounter. that is where I am lost.. ^_^

I would then be creating another form of the same type in which it will count all the items per Category..

[orange]

Does your table structure look like the one I proposed?

Work on forms and subforms after you have a design that seems to support your business needs.

If you have used a traditional form/subform where form is the 1 side of a 1 to Many, and subform is the Many side of a 1 to Many, YOU MUST CREATE A RECORD ON THE 1 SIDE(parent) BEFORE YOU CAN ASSIGN RECORDS TO THE MANY SIDE (Child).

You count things in queries, normally.

[undee69]

Thanks a lot orange ^_^ the structure works for me. I'm doing it yesterday already with some tweaking on the forms