Calculated fields (1 Viewer)

[Insomnai]

Good evening all, I hope you can help i've literally pulled my hair out on this one.

Please refer to the attached picture which is the best representation of how I want to use the database I have.

I have a form (frmMaterials) based on the table (tblMaterials) with various linked tables and comboboxes for selecting specific items, for instance, I have a combobox for 'material'. in this case SS316

On this same form I have two subforms.

On the left is a subform (sfrmMaterialsSetValues) based on a table (stblMaterialSetValues) linked to tblMaterials by way of the hidden ID of the material in the cmbMaterial combobox. tblMaterials has minimum and maximum values allowed for specific chemical and mechanical values of said material.

On the right is a subform (sfrmMaterialsData) based on a table (stblMaterialsData), linked to frmMaterials by way of the ID of the actual record.

As you can see this is at the moment little more than an idea on what the values 'should' be versus what you enter for any given record and this is what I would like to change.

What I am after, is when I select an Analysis in the sfrmMaterialsData, i.e. C, or CR, I would like the minimum and maximum values to appear (like in a calculated field) without the sfrmMaterialsSetValues subform on the left even having to be present. Ideally, I would also like a visual warning so that when a Value is outside of the set minimum and maximum where the Analysis selected matches an entry in the tblMaterialsSetValues where the MaterialID matches the cmbMaterial combobox on the main form.

I truly hope I haven't convoluted this explanation, but if anyone can assist it would be fantastic.

Kindest regards

inso

 

[Uncle Gizmo]

I've got no idea of what you are trying to do?

 

[Insomnai]

Two extra fields on right hand side which automatically populate with the min and max values based on the chosen material on the main form, where the analysis field in the tblMaterialData matches the analysis field in the tblMaterialSetValue.

Apologies I thought my explanation was at least clear enough to express what I was after.

 

[The_Doc_Man]

First things first. Your analysis tables and materials tables are nice but... we don't understand from your description what your data flow resembles.

Having said that, here are some random thoughts. (Don't stand near the dart board when I toss these thoughts out - my aim isn't always so good...)

You never need the sub-form if all you wanted to do was to check conformance to a set of content ranges. But you would need to understand VBA and how to loop through a dynamic recordset.

The part of your description that is confusing is how the data elements relate to each other. I could see a given material that is specified as, say, 27.5% to 33.5% Fe, 5.0% to 8.0% Cr, 19.5% to 22.5% C, etc. - and another material with the same components but different validity ranges. If that is what you meant, then you need a table with the material ID and a "valid ranges" child table with the material ID as both a foreign key and a partial prime key; and the element symbol as the other part of the prime key - i.e. a compound key.

Then you would have an analysis record showing the sample ID, proposed material ID, and measured content for each element. Here, the sample ID would be prime. Material ID would be a foreign key only. The element symbol and actual amount would be in a table for which the sample ID and element symbol are compound prime keys and the amount is just data.

You would then bring up your sample and run VBA code underneath to do things with a couple of recordsets. You would open the sample recordset first and get the material ID.
Then you would open the sample analysis as a second recordset, selecting for the sample number and ordering by the element symbol. You would open the standards recordset selecting for the material ID and ordering by the element symbol. (Alternative: Step through the analysis record for elements but do DLookups for the min and max allowed, but this would skip something important.)

The goal is to step through both recordsets, comparing levels in the analysis to allowed levels in the sample and noting those cases where a component is present that should not be or that is not present but should be. (The DLookup method wouldn't allow both tests in one loop.)

How you would want to show this is up to you, but you would have to store a new recordset (of a temporary nature, perhaps?) or do some sort of marking of the analysis set that allows you to add records. For each record, you would store one possible result from this list:

- sample component in range for element X (y%)
- sample component out of range for element X (y%) (high or low)
- sample lacks element X but should have it
- sample has element X but should not have it

(and I'm sure you can figure out more possibilities if you look hard enough).

All of this would be done behind the scenes when you select the sample. Then you could bring up one subform that selects for sample ID and lists all records (of the above form) including the ones you added that say "lacks element but should have it"

What I have just suggested is to break down the problem into multiple parts (see also Caesar's Gallic Wars) - divide and conquer. Do the comparisons first, then display that result.

 

[Insomnai]

I wonder if this screenshot will help show how the data flows together. Please note, I am already aware that there are two tables with the same data in for materialcategory and materialcategorynew. I am in the middle of testing an update to the products list and didn't want to muck up the original table.

With this updated visual, the tables I used in my example are named differently. In this case the tables of interest are:

tbMillCert
the main table which stores information on the actual purchase of the material

tbMillCertData
this is the subform information on the right linked to tbMillCert by CertificateID), this table stores the analysis values of a material received into stock.

stbl_MaterialsCategory_MaterialSpec (subform on the left that is linked to tbMillCert by way of MatCatID, this table stores the standard values that tell us what the material should conform to.

Does this shed any light?

Kindest regards

inso

 

[plog]

I didn't read your initial issue, I just looked at your relationship pic. You have a few issues there that you should address before tackling anything else.

1. Tables with only 1 real field. A table with just an ID field and another field isn't necessary. TblAnalysysCategory, tblSuppliers, etc shouldn't be tables. Instead, just store the actual value in the foreign table, not the ID value.

2. Multiple paths among tables. There should be only 1 way to get from one table to another. For example, I count 3 ways to get from tbSuppliers to tbCreateCert. There should only be 1 way, most likely the direct path.

3. Tables with identical structures. TbMaterialCategory and tbMaterialCategoryNEW should have their data all in one table, to differentiate new from regular records, you would add a new field so you could mark which ones are "NEW" ( whatever that means).

You should first fix these issues before moving on to others in your database. And just to be clear, this doesn't mean fixing things in the relationship view, you need to make my suggestions in the tables themselves.

 

[Insomnai]

Good afternoon Plog, thank you for your input, very kind. A couple of things if you would.

I spent a bit of time last night restructuring, and will again tonight look at the information again to take into account what you posted today.

If you don't mind me asking a couple of questions regarding your points:

1) I appreciate your point about having two fields when it comes to a table with a single set of unique values. There isn't a point in having an Autonumber Unique Value to store as a key when the actual information could simply be the key itself.
However, tblAnalysisCategory and tblSuppliers shouldn't be tables? What is a table vs a foreign tables and where would I store this information otherwise?

2) Paths between tables. I get what you mean, but can you please explain how you get from tbSuppliers to tbCreateCert? tbSuppliers is linked to tbMillCert whereas tbCustomers is linked to tbCreateCert, it is very likely I have missed something or not taken something into consideration but any help appreciated.

3) I already mentioned in the post you responded to that tbMaterialCategory and tbMaterialCategoryNEW were identical but I was testing a variation in the data in the fields (more for user visual ease than anything else), this has since been changed with tbMaterialCategory being retired and tbMaterialCategoryNEW being renamed to tbMaterialCategory and being the only table available.

Thanks again for your help if you could assist it would be splendfab.

Kind regards

inso

 

[plog]

1. For suppliers you just have a name--so you should have a text field in tbMillCert for their name.

2. I don't know what the correct path is. I do know there should only be one. You know what your data represents better than anyone, so its going to be up to you to explain the relationship between all those tables. My advice would be to start with a blank relationship screen, determine what your main table in your database is and then add other tables one at a time. As you add tables you need to make sure no duplicate paths are created. If you come to a table that you can't find out how to fit in without creating a duplicate path; post back her explaining the issue.

 

[Insomnai]

Thanks for coming back so quickly.

1) Dare I presume that in tblMillCert I should have a SupplierID field which performs a lookup linked to tblSuppliers and that tblSuppliers should have a single field with unique names and this field should be its own PK? If so, I have already made this change.

I this is not what you meant then how do I get around repeating the typing in of repetitious company names in tblMillCert?

2) tblSuppliers is only ever linked to tblMillCert but MatCatID is used on multiple tables as they have requirement to have that information on them.

Could you please explain further what you mean?

 

[plog]

1. You do not need a table for suppliers if the only real field is the name. You create a text field in tblMillCert for the supplier name. In design view of the table you can make a list of valid supplier values so that you can choose one instead of typing it out every time.

2. My real point is that you have a direct connection from tbMillCert to tbCreateCert and then 2 indirect ones. You don't need all 3. tbMaterialCategort and tbMarerialDescription probably shouldn't be linked to tbMillCert. And since tbMaterialDescription only has one real field of data (autonumbers aren't real data--they exist solely for database pyrposes), you don't need a tbMaterialDescription at all.

 

[Insomnai]

OK, lots of rebuilding done. I think this is the direction I needed to take, got rid of a few tables, brought naming conventions in line and also make sense to the table contents.

points of note as requested:

tblMaterialsCategory is the base underlying information for all materials we receive. The field 'MaterialsCategory' serves as a drop down list that is selectable on tblMillCert.

tblMaterialsAnalysis is still under construction, since AnalysisID is a field full of unique values which serves as a drop down list selectable on tblMillCertData and tblMaterialsCategorySpecification. AnalysisID and is now the new PK, the old ID (now renamed to analysis) is going to be deleted once the 80,000 plus records in all tables referring to this one are updated.

tblMaterialsAnalysisTested serves as a drop down list that is selectable on both tblMillCertData and tblMaterialsCategorySpecification.

Please take a look at the attached and see if this is more in line with what you were thinking.

A question, you advised on the removal of any table with only one field, so I did this on tblMaterialsDescription which is now simply a field in tblMillCert which has a list of selectable values.

How would I refer to the full list of possible options if I wanted to create a combobox which I would use to perform some kind of lookup on a form, without having to retype them all again?

Kindest regards

inso

 

[plog]

I don't see where supplier is in tbMaterialCaregory or tbMaterialDescription.

Yes, that's what I mean by one path. With one path you can get whatever information you need. Multiple paths just confuse the issue.

Give your relationships another go, then post the screenshot.

 

[Insomnai]

Supplier isn't in tblMaterialsCategory. Supplier would only ever show up on one of our purchases which would be stored against tblMillCerts.
tblMaterialDescription ended up being one field so I deleted the table and created a selection list within the tblMillCerts table instead.

There is going to be an adjustment on the database while we're on the subject of Suppliers and Customers.

The Supplier and Customer tables have been deleted with favour of importing Sage Accounts data via ODBC. I was going to create a link to them and perform queries but linking is hideously hideously slow. These tables will be imported into this database and the required fields will be unique selectables on tblMillCerts and tblCreateCert31

There isn't much more I can do on the relationship screenshot as I have no idea how to create a link to an odbc imported table via vba and I don't think any further adjustments can be made unless you can see something I can't?

 

[plog]

You can update your relationships to show what you currently have. Did you make the 3 changes I initially posted about?

 

[Insomnai]

Hi Plog, i've already posted the most up to date relationships screenshot that I have, did you look at that?

Also, I did mention in previous post that I had made all the changes I could, and have asked questions about other changes. Would you be kind enough to have a look at those for me?

Currently, 'Supplier' is stored in tblMillCert and 'CustomerID' is stored in tblCreateCert31.

I might scrap those two fields entirely and use dlookup to return the supplier name via tblMillCert OurPONo and return the customer name via tblCreateCert31 VsealJobRef from Sage Accounts.

I would only ever have to refer to the order number and the vsealjobref in order to get the customer and supplier information I need at that point.

 

[plog]

Sorry I hadn't seen the new screenshot, I have now and you still have duplicate paths.

1. There's a lower and upper route between tblMaterialsCategory and tblMillCert.

2. That upper route has a duplicate path as well.

3. TblMaterials has no real fields, what's its purpose?

4. tblMaterialsCategoryENspec has only one real field, making the table unnecessary.

 

[spikepl]

Looking at your relationships I can see you haven't ticked the Enforce Referential Integrity box. I'd suggest that you study what the three options are and what they do - google. The last one, concerning cascading deletes, should be used with great care only.

 

[Insomnai]

SpikePL, thank you for the pointer. I will look those up.

Plog, perhaps I have a misunderstanding on the paths. I was under the impression they were needed if I was going to create a lookup field using the contents of another table. It happens that where there is more than one path, those tables refer to the same table for lookup values.

The enspec table is not one real field. I got rid of the autonumber pk field as i mentioned in a previous post, the actual values in the new EnspecID field are unique Also.

 

[wiklendt]

1. Tables with only 1 real field. A table with just an ID field and another field isn't necessary. TblAnalysysCategory, tblSuppliers, etc shouldn't be tables. Instead, just store the actual value in the foreign table, not the ID value.

My first reaction here is to disagree with this advice. However, let's say you're right - in which case, how do you prevent data entry errors? Say, someone typing "AMD Int." and another writing "AMD International" and someone else writing "AMD Int. P/L"....? Queries on Supplier would then yield three different suppliers, while there is only one.

2. Multiple paths among tables. There should be only 1 way to get from one table to another. For example, I count 3 ways to get from tbSuppliers to tbCreateCert. There should only be 1 way, most likely the direct path.

I agree with this, and i think the penny hasn't quite dropped with the OP what you mean, so I will try another approach at explaining this:

Insomnai, imagine this scenario - let's say you have a table for Customers and a table for purchases. Now, for whatever reason you need to have the gender of each customer so you have a lookup table with the values "Male" "Female" ...etc/whatever connected to your Customer table.

Now, you also have some good reason to need to know the gender of the customer when you view your purchasing information - let's say something is useless for females but is often a necessity for males, like a particular hormone. You don't need to place a gender field into your purchasing table at all. Your purchasing table is already linked to your Customer table which laready has this information so all you need to do to view this information in two places is a QUERY, not an extra field in the table.

Forms can use either a table or a query as their source.

Does this explanation help?
Agnieszka.

 

[Insomnai]

Plog - i'm very sorry buddy but you're confusing me. I don't even have a table for Suppliers called tblSuppliers.

wiklendt - thank you for your explanation, however the only reason I have created a link between fields that are identical is simply because one of the tables is the source of a lookup for a field in the other. For example:

tblMillCert MaterialsCategoryID is a dropdown selection of materials which comes from tblMaterialsCategory MaterialsCategory. So I've simply created a relationship link via MaterialsCategoryID.

tblMaterialsCategory MaterialsCategory is a list of materials which have a specification defined by British Standards. These minimum and maximum values are stored in tblMaterialsCategorySpecification.

AnalysisID and AnalysisTestedID are both lookup fields which get their information from tblMaterialsAnalysis and tblMaterialsAnalysisTested. So that is why there are links there.

tblMillCertData is a child table of tblMillCert linked by MillCertID, these are values that are individual to the product and not the same as the values defined in tblMaterialsCategorySpecification, as you can see these also have AnalysisID and AnalysisTestedID as drop down lookup fields from tblMaterialsAnalysis and tblMaterialsAnalysisTested.

I hope this sheds some light on why I have the relationships window looking like this...