How to create a feedback loop for tables (1 Viewer)

[cheberdy]

Hello,
first question:
I have the two tables Storage bin and Item. Item has the attributes bin location and Storage bin has attribute ID, date, amount and status(free or occupied). Through the attributes ID and bin both are in a relationship. I want to achieve, if someone creates an item in table item with a new bin, then the status of this bin in the table Storage bin should change from free to occupied. Thank you.
second question:
I would like to build an inventory history.
That is, if someone changes the quantity of an item, this would be recorded and noted with the respective date. How could something like this be implemented?
Thank you very much.

 

[Galaxiom]

Firstly the bin status is a derived value so should not be recorded at all but calculated as required in a query by checking if the any Item is allocated to the bin.

The quantity of the item should not be recorded. Record the addition or subtraction of the amounts and calculate the quantity on the fly by summing them.

 

[cheberdy]

Firstly the bin status is a derived value so should not be recorded at all but calculated as required in a query by checking if the any Item is allocated to the bin.

The quantity of the item should not be recorded. Record the addition of subtraction of the amounts and calculate the quantity on the fly by summing them.

How exactly do you check if something has been assigned?
Sorry, I am new to Access

 

[The_Doc_Man]

The problem is that you are looking at the trees and not the forest. With a relational database, you look at multiple things at once that will correlate to the information you need.

For instance, if you create an item and note in the item table that it has been stored in a bin, then you what is called a parent/child relationship. The bin probably has an ID of some kind and for each item in that bin, you would mark the item's "bin of residence." That is this step here: "if someone creates an item in table item with a new bin,"

Once you have that, you can create a query against the items table that groups by bin ID. And THAT is how you know what is in each bin. BUT... your other question is, how do you know a new item was added? You have to include a "date added" to the item table, after which you can run a query that says "Show me only recent additions (date > dd-mmm-yyyy)". If a bin can ONLY hold one item, then the presence of an entry in the item table with that given bin ID says the item has been placed. If the item has no bin ID, it hasn't been placed.

Then, there is "That is, if someone changes the quantity of an item, this would be recorded and noted with the respective date. How could something like this be implemented?" The question then becomes, "Is there a capacity for the bins?" Your earlier comment made it sound like putting one item in the bin meant it was occupied. Clarify your ACTUAL business flow because what you describe seems possible - but not until you lay down the rules.

The MOST IMPORTANT FACTOR in any Access (or any other database) project is to define the rules and requirements FIRST. Without that, you are like a ship at sea without a compass.

 

[cheberdy]

The problem is that you are looking at the trees and not the forest. With a relational database, you look at multiple things at once that will correlate to the information you need.

For instance, if you create an item and note in the item table that it has been stored in a bin, then you what is called a parent/child relationship. The bin probably has an ID of some kind and for each item in that bin, you would mark the item's "bin of residence." That is this step here: "if someone creates an item in table item with a new bin,"

Once you have that, you can create a query against the items table that groups by bin ID. And THAT is how you know what is in each bin. BUT... your other question is, how do you know a new item was added? You have to include a "date added" to the item table, after which you can run a query that says "Show me only recent additions (date > dd-mmm-yyyy)". If a bin can ONLY hold one item, then the presence of an entry in the item table with that given bin ID says the item has been placed. If the item has no bin ID, it hasn't been placed.

Then, there is "That is, if someone changes the quantity of an item, this would be recorded and noted with the respective date. How could something like this be implemented?" The question then becomes, "Is there a capacity for the bins?" Your earlier comment made it sound like putting one item in the bin meant it was occupied. Clarify your ACTUAL business flow because what you describe seems possible - but not until you lay down the rules.

The MOST IMPORTANT FACTOR in any Access (or any other database) project is to define the rules and requirements FIRST. Without that, you are like a ship at sea without a compass.

Yes, that makes sense. But that's not really my problem. It's more about the implementation. For example, how do I use the bin ID in one table to find the bin in the other table. I'm sure there's a simple solution for that, I just don't have a clue.

 

[MajP]

1. if you had the following tables (names changed because Item, date, are bad names)
tblBin tblBin

BinID	CreatedDate
1​	3/20/2023​
2​	3/22/2023​
3​	3/23/2023​

and
tblBinItems tblBinItems

ItemID	BinID_FK	ItemName
1​	1​	Itm 1
2​	1​	Itm 2
3​	1​	Itm 3
4​	2​	Itm 4
5​	2​	Itm 5

You can make a query that calculates the amount and the Status.

Query1 Query1

BinID	CreatedDate	Amount	Status
1​	3/20/2023​	3	Occupied
2​	3/22/2023​	2	Occupied
3​	3/23/2023​	0	Free

To get that solution
1. make the first query

SELECT tblBinItems.BinID_FK, Count(tblBinItems.itemID) AS NumberOfItems
FROM tblBinItems
GROUP BY tblBinItems.BinID_FK;

which gives you
qryItemCount qryItemCount

BinID_FK	NumberOfItems
1​	3​
2​	2​

2. Use that in a second query

SELECT tblBin.BinID, tblBin.CreatedDate, Nz([NumberOfItems],0) AS Amount, IIf(IsNull([binID_FK]),"Free","Occupied") AS Status
FROM tblBin LEFT JOIN qryItemCount ON tblBin.BinID = qryItemCount.BinID_FK;

to get the final solution.

 

[cheberdy]

1. if you had the following tables (names changed because Item, date, are bad names)
tblBin tblBin

BinID	CreatedDate
1​	3/20/2023​
2​	3/22/2023​
3​	3/23/2023​

and
tblBinItems tblBinItems

ItemID	BinID_FK	ItemName
1​	1​	Itm 1
2​	1​	Itm 2
3​	1​	Itm 3
4​	2​	Itm 4
5​	2​	Itm 5

You can make a query that calculates the amount and the Status.

Query1 Query1

BinID	CreatedDate	Amount	Status
1​	3/20/2023​	3	Occupied
2​	3/22/2023​	2	Occupied
3​	3/23/2023​	0	Free

To get that solution
1. make the first query

SELECT tblBinItems.BinID_FK, Count(tblBinItems.itemID) AS NumberOfItems
FROM tblBinItems
GROUP BY tblBinItems.BinID_FK;

which gives you
qryItemCount qryItemCount

BinID_FK	NumberOfItems
1​	3​
2​	2​

2. Use that in a second query

SELECT tblBin.BinID, tblBin.CreatedDate, Nz([NumberOfItems],0) AS Amount, IIf(IsNull([binID_FK]),"Free","Occupied") AS Status
FROM tblBin LEFT JOIN qryItemCount ON tblBin.BinID = qryItemCount.BinID_FK;

to get the final solution.

Thank you. How would you solve my second question. Would you create a table with data and just store it there?

 

[The_Doc_Man]

Your second question requires you to have a "transaction" setup in which your items have ID numbers and you make a table of history that shows item ID, transaction type, and transaction date. (Plus any other details needed for the history.)

This is where things get kind of tricky because the layout of your table governs what gets logged. Here is my question: Are the items uniquely identifiable or are the "a bunch of identical things in a pile."

This is a tricky part of analysis and the process of normalization is CRUCIAL to an efficient structure. If you have not already done so, I would very strongly recommend that you look up "Normalization." In this forum, which IS a database forum, you will find a few articles. IF you choose to go to the general web, be sure to search for "Database Normalization" because other disciplines also use the word "Normalization." Like math, international diplomacy, chemistry, social sciences,... the list goes on and on.

When you learn normalization, there is a rule that will be part of at least some of the articles - purity of purpose. If you have a table of items, you can have data fields representing attributes of the item, BUT you use what is called a "Foreign Key" to "point" to the record in another table - for instance, use the Bin ID as a foreign key to point to the bin where you can look up stuff about the bins. And the Bin ID is the primary key of the bin table. So the relationship between the items and the bins is represented by having the ID of the bin noted in the item as a foreign key.

Now, here is where the nature of items comes into play. IF an item is one of a gazillion duplicates, indistinguishable and totally exchangeable, then you would keep one type of transaction. <<item ID, date in/out, transaction type (in/out), bin if IN but 0 if OUT, etc.>> But if the items have serial numbers, then the item table itself is your history because you would need individual item records WITH SERIAL NUMBERS in that case, and the data flow suddenly becomes a bit more complex because of tracking extra data.

Tell us more about your requirements.

 

[MajP]

Would you create a table with data and just store it there?

Yes, this would be very similar to creating a change log / audit table. You can google to get examples of how to set one up.
Here is one.

A simple solution for tracking changes to Access data | TechRepublic

When you have a lot of users changing data, it's easy for things to go wrong. But if you keep an audit trail of data input changes, you'll be able to determine which operators are consistently making mistakes (or not). You can also quickly fix problems before erroneous data wreaks havoc. This...

www.techrepublic.com

The audit log examples are normally designed to log any change in any control. You instead would have to modify this to log an entry only when an item is added or deleted. But you can then use an insert query to log into your audit table "Bin ID, Date Changed, Amount of Items, Previous Amount, Add or Delete".

This would happen at the form level. So it will not work if you are adding items directly into a query or table without using a form to trap the event.

 

[Galaxiom]

How would you solve my second question. Would you create a table with data and just store it there?

No. As I said in my first response, the quantity should not be stored at all, let alone having user input change it.
The stock movements should be recorded. These records are the log. The current stock is calculated by summing the records of the movements.

Typically the movements are things like purchases, sales, adjustments. Add the Sum of purchases, subtract the sum of sales and sum the adjustments.

 

[Galaxiom]

You might also consider the possibility of multiple bins being used for the same items. In this case the item table would not have a bin field.

The structure would use a many to many relationship where a third table has Item and Bin fields. There will be multiple records where an item is stored in more than one bin. If you want to know the stock level in each bin then your stock movement transactions would need to record the bin too.

 

[cheberdy]

You might also consider the possibility of multiple bins being used for the same items. In this case the item table would not have a bin field.

The structure would use a many to many relationship where a third table has Item and Bin fields. There will be multiple records where an item is stored in more than one bin. If you want to know the stock level in each bin then your stock movement transactions would need to record the bin too.

Your second question requires you to have a "transaction" setup in which your items have ID numbers and you make a table of history that shows item ID, transaction type, and transaction date. (Plus any other details needed for the history.)

This is where things get kind of tricky because the layout of your table governs what gets logged. Here is my question: Are the items uniquely identifiable or are the "a bunch of identical things in a pile."

This is a tricky part of analysis and the process of normalization is CRUCIAL to an efficient structure. If you have not already done so, I would very strongly recommend that you look up "Normalization." In this forum, which IS a database forum, you will find a few articles. IF you choose to go to the general web, be sure to search for "Database Normalization" because other disciplines also use the word "Normalization." Like math, international diplomacy, chemistry, social sciences,... the list goes on and on.

When you learn normalization, there is a rule that will be part of at least some of the articles - purity of purpose. If you have a table of items, you can have data fields representing attributes of the item, BUT you use what is called a "Foreign Key" to "point" to the record in another table - for instance, use the Bin ID as a foreign key to point to the bin where you can look up stuff about the bins. And the Bin ID is the primary key of the bin table. So the relationship between the items and the bins is represented by having the ID of the bin noted in the item as a foreign key.

Now, here is where the nature of items comes into play. IF an item is one of a gazillion duplicates, indistinguishable and totally exchangeable, then you would keep one type of transaction. <<item ID, date in/out, transaction type (in/out), bin if IN but 0 if OUT, etc.>> But if the items have serial numbers, then the item table itself is your history because you would need individual item records WITH SERIAL NUMBERS in that case, and the data flow suddenly becomes a bit more complex because of tracking extra data.

Tell us more about your requirements.

Here is some more information. It is planned that a storage bin has one or more items. But one item has only one bin. Yes the item is literally a bunch of identical objects all with the same ID.

 

[cheberdy]

1. if you had the following tables (names changed because Item, date, are bad names)
tblBin tblBin

BinID	CreatedDate
1​	3/20/2023​
2​	3/22/2023​
3​	3/23/2023​

and
tblBinItems tblBinItems

ItemID	BinID_FK	ItemName
1​	1​	Itm 1
2​	1​	Itm 2
3​	1​	Itm 3
4​	2​	Itm 4
5​	2​	Itm 5

You can make a query that calculates the amount and the Status.

Query1 Query1

BinID	CreatedDate	Amount	Status
1​	3/20/2023​	3	Occupied
2​	3/22/2023​	2	Occupied
3​	3/23/2023​	0	Free

To get that solution
1. make the first query

SELECT tblBinItems.BinID_FK, Count(tblBinItems.itemID) AS NumberOfItems
FROM tblBinItems
GROUP BY tblBinItems.BinID_FK;

which gives you
qryItemCount qryItemCount

BinID_FK	NumberOfItems
1​	3​
2​	2​

2. Use that in a second query

SELECT tblBin.BinID, tblBin.CreatedDate, Nz([NumberOfItems],0) AS Amount, IIf(IsNull([binID_FK]),"Free","Occupied") AS Status
FROM tblBin LEFT JOIN qryItemCount ON tblBin.BinID = qryItemCount.BinID_FK;

to get the final solution.

Could you explain the VBA code( SELECT tblBin.BinID, tblBin.CreatedDate, Nz([NumberOfItems],0) AS Amount, IIf(IsNull([binID_FK]), "Free", "Occupied") AS Status

FROM tblBin LEFT JOIN qryItemCount ON tblBin.BinID = qryItemCount.BinID_FK). What exactly does Nz([NumberOfItems],0). Why is NumberOfItems not specified with the table like the others?
What does this section mean:
FROM tblBin LEFT JOIN qryItemCount ON tblBin.BinID = qryItemCount.BinID_FK? What does LEFT JOIN mean?

 

[MajP]

Could you explain the VBA code( SELECT tblBin.BinID, tblBin.CreatedDate, Nz([NumberOfItems],0) AS Amount, IIf(IsNull([binID_FK]), "Free", "Occupied") AS Status

None of that is VBA code. That is SQL. If you switch in the query builder from design view to SQL view you will see the SQL code generated after using the user interface to build a query.

What exactly does Nz([NumberOfItems],0)

Null to zero changes a Null value to some other value. An blank or empty cell in access is usually a Null. So if a Bin has no items it returns a null or blank value. This changes that to a zero.

If you are new to databases I strongly recommend you spend some time going through all of these lessons. The time spent will be well rewarded.
Here is the lesson on joins

SQL Joins

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

But start in the left side of the list and work through the lessons.