Problem with a Subform missing Product Code Description

[david0121]

I’ve created two tables to enable me to list component items required to make up certain products, my current database already has a “Inventory” table, and in addition I have created two tables as follows-:
First Table “PEXP”
PEXPID (PK)
ProdCode

Second Table “Component”

ComponentID (PK) “Hidden Field”
PEXPID (FK) “Hidden Field”
ProdCode
CompQty

The Prodcode field in the above tables comes from the “Inventory” table. In addition to the above I would like to see the “Description” for the “prodcode” on both tables, this will help ensure that when listing the components for a product the correct component codes are being entered along with the necessary component quantity’s.

Can someone tell me where I’m going wrong, and how I can get the “Description” field into the above two tables.

David

 

[jzwp22]

The Prodcode field in the above tables comes from the “Inventory” table. ..In addition to the above I would like to see the “Description” for the “prodcode” on both tables

You absolutely would not repeat information that is in one table in other tables; doing so would violate normalization rules and good database practices. The only piece of data that is "repeated" should be in the key (FK) fields that join related tables.

Further, your users should not be inputting data directly in tables. All user interaction with data should be done through forms. When you use forms, you can use combo boxes which can show whatever product information you want.

 

[david0121]

JZWP22, thankyou for the reply,


What I failed to mention in my previous post, is that I have created the necessary forms and also a subform for entering the necessary data.


I am to access, so far I have been able to resolve any problems by reading hours and hours of forum posts,


The form relating to the first table also includes a subform for the components and quantity.


Looking back at my previous post, are there any recommendations that you would suggest. In order to bring over the product description from the stockfile.


I look forward to your reply

 

[jzwp22]

First, I need to understand your table structure a little better. You provided this

PEXP
-PEXPID (PK)
-ProdCode

Component
-ComponentID (PK)
-PEXPID (FK) foreign key to PEXP table
-ProdCode
-CompQty

What type of information is in these two tables?

Then you said you had an inventory table. Could you provide the structure of that table as well.

 

[david0121]

Further to your latest post, please find below a list of all tables

Tables

Component ID (PK)
PEXPID (FK)
Procode

Inventory
LogID (PK)
Item
Description
Category
Location
Cost
Reorder
TargetStock
Attachments
Discontinued
Comments
Sizincm

Inventory Transactions
TransID (PK)
Transaction Item
Employee
Transaction Type
Quantity
Created Date
POnumber
Comments

Logs
KeyID (PK)
Date
Time
Notes
Temperature
PHRange
Amonia
Nitrate
Nitrite
AmendStock (SEE NOTE)
TransactionID (I need to record the transaction id here) Not sure how to

(AmendStock) Notes
Option Compare Database
Private Sub AmendStock_AfterUpdate()
DoCmd.Close acForm, "OtherForm"
If Me.AmendStock = -1 Then
DoCmd.OpenForm "Inventory Transactions Form", , , , acFormAdd
DoCmd.Close acForm, "Inventory Transactions"
End If
End Sub

PEXP
PEXPID (PK)
ProdCode
CompQTY

Transaction Types
ID (PK)
Description
Add/Remove

The main purpose of this database is to maintain the log of all activity relating to our tropical fish tank. Looking at the log table I have created a form from the log table, this form is the main data input form, the temperature, PH range, ammonia, nitrate, nitrite, are all numerical fields.

I have recently added an amend stock yes or no field to the LogForm to enable me to make changes two Stockfile. When a tick is placed in the amend stock field, the inventory transaction form opens and allows me to make any changes to the stock.

Once I have amended the stock I make a note of the transaction number and enter it into the transaction ID on the LogForm, I know that this is not ideal but I have yet to find out how to write the transaction ID back to the log form.

Now back to the PEXP and components table, all of the product codes and components are items in the inventory Table/Form. PEXP holds product code and description, and the components subform holds PEXPID foreign Key, along with a list of components and quantity’s required for the main product. For example-:

WC0001 is the product. 25% WATER CHANGE is the description of this product.

002001 is the component code WATER PER LITRE is the companion description 60 is the component quantity.
001006 is the component code AQUA SAFE PER ML is the component description 30 is the components quantity
Z88049 is the component code WOOL FILTER CHANGE is the companion description 1 is the components quantity.

So from the above list, we can see that a 25% the water change requires three individual components, reducing stock of those components by 91 Pcs.

With reference to my earlier notes the amend stock feature only allows me to update one component per log id, which up until now has been fine,

Ideally, what I require in this example, is that the product code WC001 25% water change increases in quantity by one, (which technically the receipt of the product) and all of the components reduce by the quantity required, in this case three individual components reducing the stock quantity by 91 pieces. (this technically is the usage of components) and then finally decrease the product code WC 001 by one for (which is technically the usage of the main product) this part is just as important as it maintains the stock quantity for WC 001 at zero.

Please let me know if you require any further information, or explanation as to how the kit/parts explosion works.

David.

 

[jzwp22]

Just focusing on your tables...

All products should be in your inventory table; let's rename that table to tblProducts

tblProducts
-ProductID (PK)
-Item
-Description
-Category
-Location
-Cost
-Reorder
-TargetStock
-Attachments
-Discontinued
-Comments
-Sizincm (I'm not sure what this field is for; if an item/product comes in a certain lot size indicate it with a number and then have a separate foreign key field to refer to the unit of measure.)
-LotSize
-fkUOMID foreign key to a tblUnitsOfMeasure that holds all the possible units of measure you might use in your application

tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUOM

If a group of products is used to create another product then you need a table to relate them

tblProductBOM (BOM-bill of materials)
-pkProdBOMID primary key, autonumber
-fkPProductID foreign key to tblProducts (the product you are making)
-fkSProductID foreign key to tblProducts (a product used to make the above product)
-QTY quantity needed
-fkUOMID foreign key to tblUnitOfMeasure (since you will measure things differently for each quantity of product)

If you need 3 products (A, B, C) to make product D, you would have 3 records in the above table.

For the transactions related to the products...
Since a transaction may involve many products (one-to-many relationship), I think you may need a structure like this

tblTransactions
-pkTransID primary key, autonumber
-dteTrans (date of transaction)
-fkEmployeeID foreign key to your employee table
-fkTransTypeID foreign key to a table that holds transaction types (in or out of inventory)
-txtReferenceNo (PO for purchasing(adding to inventory)/ Invoice for selling(moving out of inventory) or some other reference number if desired)

tblTransactionDetail
-pkTransDetailID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkProductID foreign key to tblProducts
-Qty


Now as to the logs table

Logs
-pkLogID (PK)
-dteLog (have 1 field with both date and time)
-fkEmployeeID foreign key to employee table since someone will be entering info into the log
-Notes

Note: the words date and time are reserved words in Access and therefore should not be used as table or field names.

Now as to the tests since a log entry has many tests (pH, Temperature, Ammonia, Nitrate, Nitrite) that is a one-to-many relationship which requires the tests to be records not fields in a related table. Further, a test will apply to many log entries so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (logs & tests in your case), you have a many-to-many relationship which requires a junction table.

First, a table to hold the various tests; each test will be a record in this table

tblTests
-pkTestID primary key, autonumber
-txtTestName

Now the junction table to relate the tests to the logs

tblLogTests
-pkLogTestID primary key, autonumber
-fkLogID foreign key to Logs table
-fkTestID foreign key to tblTest
-spTestResult (single precision number datatype field to hold the result of the test for the particular log).

If you want to record the actual date/time of when each test was conducted then you can include another date/time field in this table as well. You can also include a reference to an employee if the employee conducting the test is different from the person who created the log entry.


This is just a first go at the table structure; it will probably need to be tweaked as you provide more feedback.

 

[david0121]

Thank you for the reply,

The Sizeincm field, I have removed it from the table, for the time being, We used this field to keep track of the maximum size the fish would grow to.

I’m not sure that I fully understand what the (FK) is.

My understanding is that a foreign Key, is the primary key number from another table. In order to get the foreign Key from another table, I have to open the ProductBOM table in design view and select the lookup tab, and create a lookup to the product table.

I’ve created the Lookup as follows-:

Display Control -: Combo Box
Row Source Type -: Table/Query
Row Source-: SELECT Inventory.LogID, Inventory.Item, Inventory.Description FROM Inventory;
Bound Column -: 1
Column Count -: 2

Now to the form, I tab to the ProductID(fk) and enter the product that I require, and then tab down to enter the SproductID(FK),

When I tab to the Productfk and enter the product that I require, which in this case is 01002, when i tab to the SproductFK, the Productfk changes to 1002. Now I’m completely confused

Please confirm whether or not my understanding of foreign key is correct, the moment I’m not sure where I am going wrong.

I look forward to your reply.

David

 

[jzwp22]

My understanding is that a foreign Key, is the primary key number from another table.

You describe the foreign key correctly

In order to get the foreign Key from another table, I have to open the ProductBOM table in design view and select the lookup tab, and create a lookup to the product table.

Please do not create any lookups at the table level. Lookups (combo/list boxes) should be used in forms. This site explains the problems having lookups in tables can cause.

 

[david0121]

I have created the form and subform for the ProductBom


the property sheet control source is productID(fk)
Row Source : SELECT Products.LogID, Products.Item, Products.Description FROM Products;

Row Source Type : Type/Query
Bound Column : 1


When I select the combo box for PproductID (FK) all i'm getting is a list of numbers nothing else, don't know what product relates to what number.


What am I doing wrong????

 

[JANR]

Make sure that the property ColumCount (on the format tab) is 3

set the column width property to:

0;0;5cm

to hide the keys.

JR

 

[jzwp22]

If you look at the Column Widths property of the combo box which is found under the Format tab, you will see a series of numbers like this: 1";1";1.5" etc. These numbers correspond to the fields in the row souce (SELECT Products.LogID, Products.Item, Products.Description FROM Products)

For those fields that you do not want to see in the combo box, set the column width to zero. So to hide the key field value Products.LogID set it's width to 0, so now the column widths property should look like this:

0";1";1.5"

I see JANR got in before me.

JANR,
Thanks for catching the column count property--I forgot about that.

 

[david0121]

At last found out where I was going wrong. When making the sub form, I was not bringing the PProduct(ID) into the form. All of the videos I have watched all forgot to mention that in my case the PProduct would be hidden on the form... Oh Boy Oh Boy. TWO DAYS, it has taken me to resolve. But we got there in the end, little blighter, anyway, migraine has finally gone.
Please accept my sincere apologies for being so thick,
I’m now starting to think that this project is a bit ambitious,
I’m not going to quit, personally with computers I feel the best way of learning, is to physically have a go, although at times, very, very, painful.
Thank you all for the help, It’s greatly appreciated.
I’ve still got a couple of adjustments to make on the forms,
For some reason I am still unable to display the product description, at the moment once I enter the product code I just tab over to the Qty, it would be great if I could enter the product and then when I press tab display the product description to the product entered before entering the quantity.
just looking at the log form, what would you suggest is the best way to log any stock transactions, so far this is the only item I have found that has no link directly back to the log form.
Hopefully over the weekend I will get a little more time to enter test data.
I hope you other great weekend,
I look forward to your reply,
David.

 

[jzwp22]

For some reason I am still unable to display the product description

You can add a textbox control to the form and for the control source just reference the column from the combo box that contains the description.

=comboboxname.column(x)

where x=the column corresponding to the product description

You said earlier that this was the row source for the combo box:

SELECT Products.LogID, Products.Item, Products.Description FROM Products;

Access starts counting the column at zero, so to display the description you will need to replace the x with a 2.

=comboboxname.column(2)

just looking at the log form, what would you suggest is the best way to log any stock transactions, so far this is the only item I have found that has no link directly back to the log form.

I cannot answer that, only you can. Based on what you have described the log is a way for you to capture the results of tests. What else does a person do relative to the log? Are stock transactions always related to a log event or can stock transactions be independent of a log event?

In other words, can you describe the relationship between the log and the stock transactions?

 

[david0121]

RE -: The relationship between the log and stock transactions,
Technically, any log event, except for the temperature test, should create a transaction for the product used in order to carry out such an event. The temperature tests is the only log event that I can think of that has no effect on stock levels.

So I think for the purpose of this database, all transactions should relate to a log event.

Now looking at the process of the transaction itself, earlier in these posts, we created three new tables. -: Transactions, Transaction details, and Product BOM.

I’m not sure what you have in mind with regards the best way of dealing with transactions. Or on how we can check on what products are part of the extra process relating to the product BOM.

Hopefully this answers any questions you have.

There are look forward to your reply.

David

 

[jzwp22]

Perhaps the log table and the transaction table I suggested serve essentially the same function and the transaction detail table will take care of what products are used.

So the overall structure might look like this

tblProducts
-ProductID (PK)
-Item
-Description
-Category
-Location
-Cost
-Reorder
-TargetStock
-Attachments
-Discontinued
-Comments
-LotSize
-fkUOMID foreign key to a tblUnitsOfMeasure that holds all the possible units of measure you might use in your application

tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUOM


tblProductBOM (BOM-bill of materials: products that are made from the combination of other products)
-pkProdBOMID primary key, autonumber
-fkPProductID foreign key to tblProducts (the product you are making)
-fkSProductID foreign key to tblProducts (a product used to make the above product)
-QTY quantity needed
-fkUOMID foreign key to tblUnitOfMeasure (since you will measure things differently for each quantity of product)



tblLog
-pkLogID primary key, autonumber
-dteLog (date of log event)
-fkEmployeeID foreign key to your employee table


tblLogProductDetail
-pkLogDetailID primary key, autonumber
-fkLogID foreign key to tblLog
-fkProductID foreign key to tblProducts
-Qty


tblTests
-pkTestID primary key, autonumber
-txtTestName



tblLogTests
-pkLogTestID primary key, autonumber
-fkLogID foreign key to tblLog
-fkTestID foreign key to tblTest
-spTestResult (single precision number datatype field to hold the result of the test for the particular log).

 

[david0121]

Okay, I have spent the last couple of days creating some test data, and creating some of the reports.

The next task for me to complete relates to the ProductBOM table, so far I have managed to create most of the BOM Files along with Components and Qty’s that I require.

Is there a way of calculating the true cost of the materials, per BOM record, The total cost of these components needs to be written to the main product code. This way, if the price of the components changes, we could enter the new prices of the product and re-run the program or procedure to update the costs of all BOM records.

I’ve I searched this forum and the web for a sample BOM database, to see how a BOM database handles the stock, but so far have been unsuccessful.

 

[jzwp22]

The total cost of these components needs to be written to the main product

Since the total cost would be a calculated value and is dependent on the cost of other components, it is not something that should be stored in the table but rather calculated on the fly when you need it.

Is there a way of calculating the true cost of the materials, per BOM record,

As long as you have the unit price and the quantity, you can do the calculation. It would be best to use a query to do that calculation.

if the price of the components changes, we could enter the new prices of the product and re-run the program or procedure to update the costs of all BOM records

You would update the prices and just rerun the query to get the new total cost.


Now, do you have a need to maintain both the current price as well as previous prices for a product?

If so, that would require at leasat 1 additional table to track prices changes for products over time.

 

[david0121]

I think for this purpose, it would be great to maintain both the current price as well as previous prices for a product.

With regards to the additional table to track price changes for products what would you suggest the new table contains, and what if any relationships to the current tables?

 

[jzwp22]

A product can have many prices (over time) so that is a one-to-many relationship. You also need some way of telling when the new price goes into effect, so a date field to handle that.

tblProductPrices
-pkProductPriceID primary key, autonumber
-fkProductID foreign key to tblProducts
-dteEffective (effective date of the price)
-currPrice

You would, of course, remove the price/cost field from tblProducts