Calculated Field? (1 Viewer)

ChrisC · Sep 16, 2019

Good afternoon all,

I am working on my DB and have 2 tables that I want to work with but I just cant work out the correct syntax for an expression…

Table1: TransactionLogISSUE – this contains the following fields (Amongst others that are not relevant for this question):
· PartID
· PartDescription
· Issuedfrom (which relates to warehouse location – WHLocatID)
· TransactionQty

Table2: Inventory – this contains the following fields (Amongst others that are not relevant for this question):
· PartID
· PartDescription
· WHLocatID (warehouse location)
· OpeningStockQuantity (the initial stock level when the part was entered in the system)
· LocationQuantity (the current stocking level: which is the opening stock level, minus any “issues”)

The log is simply an itemised history of “issues” generated from a form which all works perfectly; however I need to have the LocationQuantity field in the Inventory table calculated based on the OpeningStockQuantity Minus the sum total of all TransactionQty.

For point of clarity – “Issue” is when an item is removed from the warehouse.

For Example…
· Part A “opening” stock = 11 in WHLocatID BB1 and 3 from CF3 (Total stock therefore is 14)
· An Issue of 2 x Part A is made from location BB1
· Therefore we should be left with 9 in BB1 and 3 in CF3 – these are the figures that the LocationQuantity field should show in the Inventory table.

I just cant work out what the syntax should be for this calculation.

As always – any help gratefully received!

Chris

CJ_London · Sep 16, 2019

sounds like you are trying to do this as a calculated field in a table.

Aside from the fact that storing calculated values in tables is a bad idea, the calculated field has limited functionality in that it can only reference field values from the same record and the number of vba functions you can use is very restricted. So if you are trying to reference the issues table from the inventory table, it is not possible using a calculated field.

If this is not the case, show the whole sql for the query you are using at the moment so we can see where you are going wrong

ChrisC · Sep 16, 2019

Hi CJ,

You're quite right - I am trying to use the Calculated field function in a table.

Out of interest - why is it a bad idea to store calculated fields in a table?

many thanks
Chris

theDBguy · Sep 16, 2019

ChrisC said:
Hi CJ,

You're quite right - I am trying to use the Calculated field function in a table.

Out of interest - why is it a bad idea to store calculated fields in a table?

many thanks
Chris

Hi Chris. I don’t think using calculated fields is necessarily a bad idea; however, I think storing calculated values is. For example, if you store a person’s age, that information will be wrong as soon as the person have another birthday. So, in short, storing calculated values risks your data integrity.

ChrisC · Sep 16, 2019

Oh I see, that makes sense.

In my case then, I would imagine it would be "OK"?

The opening stock will never change and the field value that I want calculated needs to be dynamic and ever changing.

The information I need storing is stored in the transaction Log and will never be altered; only potentially deleted: In which case, I would need the "Location Quantity" to be updated accordingly.

Thanks,

The_Doc_Man · Sep 16, 2019

In general a calculated field belongs in a query. Your design that uses the initial quantity is almost "right" (acknowledging that "right" is a relative term.) Here is a different way (but only slightly different) of getting to the same place.

Add one more "transaction issue" for which the transaction type is "initial." Remove the initial quantity from the inventory table. Then when you sum up your issues, the effect is that your initial quantity was 0 and you just added inventory. If the problem is that your quantities don't add up because some of them must be negative, you include that in the summation by having a "sign" (+1 or -1) somehow included, and then sum the quantity times the sign field. That lets you do initial stock, restock, adding inventory, shrinkage, and ordinary stock issue.

If that approach appeals to you, come back and ask.

arnelgp · Sep 16, 2019

firstly, always use form when entering data to each tables.
second, remove PartDescription from TransactionLogISSUE.
create a Query that will retrieve the PartDescription from Inventory table.
third, add TransactionDate to your transactions (so you know when an issuance occurred).

see this example.

ChrisC · Sep 16, 2019

The_Doc_Man said:
In general a calculated field belongs in a query. Your design that uses the initial quantity is almost "right" (acknowledging that "right" is a relative term.) Here is a different way (but only slightly different) of getting to the same place.

Add one more "transaction issue" for which the transaction type is "initial." Remove the initial quantity from the inventory table. Then when you sum up your issues, the effect is that your initial quantity was 0 and you just added inventory. If the problem is that your quantities don't add up because some of them must be negative, you include that in the summation by having a "sign" (+1 or -1) somehow included, and then sum the quantity times the sign field. That lets you do initial stock, restock, adding inventory, shrinkage, and ordinary stock issue.

If that approach appeals to you, come back and ask.

Oh right, ok - well I'm open and happy to any suggestions so any more info gratefully appreciated. You will have to excuse my ignorance on some parts as I am still very much learning Access. Thanks.

ChrisC · Sep 16, 2019

arnelgp said:
firstly, always use form when entering data to each tables.
second, remove PartDescription from TransactionLogISSUE.
create a Query that will retrieve the PartDescription from Inventory table.
third, add TransactionDate to your transactions (so you know when an issuance occurred).

see this example.

Hi ArnelGP,

thanks for that example, that's just what I was looking for. I will be able to use that... i'll let you know how I get on.

many thanks
Chris

arnelgp · Sep 16, 2019

I already added the Delete sub there.
when you delete, it returns back the quantity you issued to the correct PartID and WHLocation.

theDBguy · Sep 16, 2019

ChrisC said:
Oh I see, that makes sense.

In my case then, I would imagine it would be "OK"?

The opening stock will never change and the field value that I want calculated needs to be dynamic and ever changing.

The information I need storing is stored in the transaction Log and will never be altered; only potentially deleted: In which case, I would need the "Location Quantity" to be updated accordingly.

Thanks,

Hi Chris. Glad to hear you have a solution now. Calculated fields are very limited, as CJ mentioned earlier, so using a calculated field to do what you wanted was probably not possible anyway. Good luck with your project.

ChrisC · Sep 17, 2019

Thanks DB Guy!

arnelgp · Sep 17, 2019

I added another table PartMaster.
this holds distinct part number.

now on tables Inventory and TransactionLogISSUE, the PartID field is now changed to number (long). this points to the ID field of Partmaster.

use form always.
form frmInventory is used on entering initial inventory and quantities.
opening stock and location qty are same initially.
on this form also you add new Part by typing the Part Description on the combo.

frmTransactions is for recording of issuance.
there is a code to update inventory table (see code in Form_AfterUpdate event).

quantity is returned to inventory when you delete a transaction (see form_delete and form_afterdelconfirm event code).

The_Doc_Man · Sep 17, 2019

You will have to excuse my ignorance on some parts as I am still very much learning Access.

To be perfectly honest, I've been using Access since Ac 2.0 (we are now above 16.0) and I am STILL learning Access. Don't feel bad, even a little bit, at not knowing something. There was an old saying when I was a kid, it went something like this: I really didn't start to learn things until AFTER I thought I knew it all.

ChrisC · Oct 7, 2019

Hi all,

Firstly really sorry for not being able to get back to you Arnelgp, I have been away and without computer access for a couple of weeks. Thank you for your latest example however.

Now I am trying to pick up where I left off and I must admit I am having trouble working out how I can implement it in to my DB.

In my attached DB, we have frmIssue, which has a text box called "txtLocatStock".

When a user selects a part number from the combobox, the available locations appear in the subform. Then the user can select which warehouse location they want to "issue" the goods from (using Issued From combo box called cboWarehouseLocat).

now, what I would like to happen here is that once the user has selected which location they want to "issue" from, that the stock count for that particular part in that particular location is displayed in the txtLocatStock box.

I cant see if I could implement your code with this, Anelgp; and I have also tried various DLookup strings but to no avail.

any and all help more than gratefully received as always!

thanks
Chris

ChrisC · Oct 7, 2019

The_Doc_Man said:
To be perfectly honest, I've been using Access since Ac 2.0 (we are now above 16.0) and I am STILL learning Access. Don't feel bad, even a little bit, at not knowing something. There was an old saying when I was a kid, it went something like this: I really didn't start to learn things until AFTER I thought I knew it all.

Thanks Doc. You're of course correct - every day we learn something new. Whether that is how to make a DB work, or something as simple as learning to leave home 1 minute earlier in the mornings to avoid "that" person who always seems to hold you up in the morning ha ha.

mike60smart · Oct 7, 2019

Hi Chris

See if this helps

View attachment Main 1.80.accdb.zip

ChrisC · Oct 7, 2019

mike60smart said:
Hi Chris

See if this helps

View attachment 76831

Hi Mike,

Thanks very much for that.

It helps... ish

The txtQty box that you have added seems to only show the quantity from the first entry in the displayed subform.

For example, if I select part number "S14170" I get a list 6 different locations, each with a different quantity "in stock". However no matter which location I choose in cboWarehouseLocat combo box (for "Issued From"), only the first quantity in the list appears (in this case - 44).

Sorry I didn't make it clear what I needed!

many thanks
Chris

ChrisC · Oct 7, 2019

as a quick update Mike, your system works IF the user first selects the part number on the sub form.

For example they would need to select the part number from the initial combo box, then select it from the sub form, then select it from cboWarehouseLocat; this will then show the correct location quantity.

I need the subform to essentially be read only - purely for information only, and not "involved" with the use of the form as such.

thanks again
Chris

Pat Hartman · Oct 7, 2019

arnel suggested removing description from the log table since it is redundant. Issued from is also redundant since the location is stored in the inventory table. I would however add an IssuedDate field and possibly an IssuedTo field.

Calculated Field? (1 Viewer)

Registered User.

Super Moderator

Registered User.

I’m here to help

Registered User.

Immoderate Moderator

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

I’m here to help

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Immoderate Moderator

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread