Formulation lookup (1 Viewer)

papa john · Nov 19, 2019

Hi, new to forum, access n00b.

Maybe in the wrong place, this may be achievable in excel.

I have raw data that is exported from a program in this format.

product raw material amount
Product 1 raw material 1 54
Product 1 raw material 2 41
Product 1 raw material 3 1
Product 1 raw material 4 45
Product 1 raw material 5 45
Product 2 raw material 2 45
Product 2 raw material 4 4
Product 2 raw material 7 53
Product 2 raw material 1 54
Product 2 raw material 3 5
Product 3 Product 1 5
Product 3 Product 2 57

it is recipes / formulations for products. as you can see each product is repeated for each raw material it contains.

I want to be able to search the data for products that contain particular raw materials, ie. which products contain raw material 1 and raw material 2 for example.

would i need to normalise the data?

also potentially i want to create a system whereby documents could be generated for a product describing its properties based on its recipe. so for example if it contained raw material 1 it would be described as flammable. if it contained raw material 2 it might be described as solid.

preferably these two systems would have some form to be completed that a non tech savvy person could use.

Please could you help guide me in the right direction?

theDBguy · Nov 19, 2019

Hi. Welcome to AWF! Definitely, if you plan on keeping this information in a database for future uses, it's essential that you normalize it first as you put them in the database. If you do that, it would be easy to query the data to answer your questions and analyze it.

papa john · Nov 19, 2019

theDBguy said:
it's essential that you normalize it first as you put them in the database.

is there a way to do this automatically? i want to be able to import from the excel spreadsheet that the program exports the data dump to and then normalise this data automatically. it exports maybe 50k+ lines. I guess one way of doing it would be to turn the field "raw material", a list of raw materials lined up next to repeat product entries, into multiple fields labelled "component 1, 2,3 etc".

i tried the analyse table wizard but its potato with the "Query is corrupt" error.

how might i go about doing this?

thanks for the help

theDBguy · Nov 19, 2019

Hi. Which version of Access are you using? They have a fix for that now for Access 2016.

papa john · Nov 19, 2019

theDBguy said:
Hi. Which version of Access are you using?

some version of office 365, will need to confirm which when im back in the office.

Micron · Nov 20, 2019

IMHO, "need to" no. Should you? Possibly.

Substitute "Product 1" and "Raw Material 1" with foreign key values from normalized tables along with Amount and you have
1 | 1 | 54
1 | 2 | 41
1 | 3 | 01
1 | 4 | 45
Is that so different? If you can't query the un-normalized data, how will you query this one? I would consider the source before deciding whether or not it is worth it. Oft times, it's incomplete at best and jumbled at worst and trying to make it fit a normalized schema might be asking for more trouble than it's worth.

Pat Hartman · Nov 20, 2019

If Access is not going to be the system of record for the data and all you are doing is analyzing it, then I would change it as little as possible when you import it. Having flattened data tables is wrong when you are designing an application to actually keep and manage data. Analyzing data exported from a different system and which you will not be keeping, just do your best with what you get.

I wouldn't even import the data. If it comes to you as a .csv, .txt, or .xlxs file, just link to it and run your queries.

The problem seems to be that you need some information which is not included in the downloaded data and which you want to persist over time rather than reenter it. You are not getting a PK in the download so you generating an autonumber for this purpose isn't going to be helpful at all since each time you run a new import, the data could be in a different order and so the same material could be assigned a different autonumber. You will need to manage this reference table by using the text strings which I assume will be unique for a given material.

Tell us a little more about what you will be doing with this data and whether you actually need to store it or if this process is simply to do some kind of reporting.

papa john · Nov 20, 2019

papa john said:
some version of office 365, will need to confirm which when im back in the office.

kind of spaced on this, it is a version of office 365

Micron said:
IMHO, "need to" no. Should you? Possibly.

Substitute "Product 1" and "Raw Material 1" with foreign key values from normalized tables along with Amount and you have
1 | 1 | 54
1 | 2 | 41
1 | 3 | 01
1 | 4 | 45
Is that so different? If you can't query the un-normalized data, how will you query this one? I would consider the source before deciding whether or not it is worth it. Oft times, it's incomplete at best and jumbled at worst and trying to make it fit a normalized schema might be asking for more trouble than it's worth.

Instead of substituting the values, i created a query that turned the list of products and raw materials in to non duplicate lists and created tables with these queries and so now the actual codes are keys.

papa john · Nov 20, 2019

Pat Hartman said:
I wouldn't even import the data. If it comes to you as a .csv, .txt, or .xlxs file, just link to it and run your queries.

nice, one of the next steps i was going to look at was instead of manually inserting the data into access i was going to look at importing it from the spread sheet. i didnt realise it was possible to reference the spread sheet directly, so i may look to incorporate this.

Pat Hartman said:
Tell us a little more about what you will be doing with this data and whether you actually need to store it or if this process is simply to do some kind of reporting.

storing it isnt necessary / desirable, the data is coming from a stock management system which is live and considered correct. data needs to manually dumped out of the program to an excel spreadsheet for the data to be manipulated. any data in the data base could only be considered correct at the point of exporting from the stock management system.

update on what i've done

so i have turned raw material and product codes into unique keys.

i then set up queries that search the raw materials for [rm1] and [rm2]

i removed any records that werent duplicated then i ran a cross tab query to make the data look like

product |[rm 1]|[rm 2]
-------------------------
Product 15 | 54 | 0.1 |
Product 32 | 24 | 0.3 |

so i can now see which products contain certain combination of two raw materials and what level of those raw materials it has.

one extra complication to this process is that products can contain products in their formulation. so for example if a product is made up of only a blend of two products, it would be impossible to know what raw materials has gone into it. what i plan to do is check if the code in the raw material field is a product, if it is, flag it as a sub assembly, which would at least let the person know that there could be something using that product that has the raw material they are interested in. to be able to work out how much of that raw material is in the top level product and which top level product is in may a little more tricky.

papa john · Nov 20, 2019

the stock management system that is used is called Opera 3

in opera there are three types of items; components, sub assemblies and assemblies.

components are items that are used in assemblies or sub assemblies.

sub assemblies are made up of components or sub assemblies but go into other assemblies or sub assemblies.

assemblies are made up of components or sub assemblies but do not go into anything else.

can anyone think of a way that an assembly or sub assembly could be "exploded out" / "broken back" to show all the components it contains and to show what % of those components it would have. consider that there could be multiple levels, an assembly could contain a sub assembly that contains a sub assembly etc.

product 3 in my example in OP would be a product that contains sub assemblies. the raw data export would only have one level ie in the product field it would have the product code, in the raw material field it would have the sub assembly code, but there isnt another field which says what goes into the sub assembly. although the sub assembly would have it own record which says what goes into it.

to determine the %of a component in the top level the component percentage in the sub assembly would have to be multiplied by the sub assembly percentage in the next level sub assembly and so on until the assembly is reached.

Micron · Nov 20, 2019

Instead of substituting the values

I meant substitute your example text values with PK data from other tables/fields as an example, not just alter the data haphazardly. Sounds like that's what you ended up doing.
If product to ingredient is one to many, and one of the ingredients can be a product, then that sounds like a many to many relationship to me.

papa john · Nov 20, 2019

Micron said:
I meant substitute your example text values with PK data from other tables/fields as an example, not just alter the data haphazardly. Sounds like that's what you ended up doing.
If product to ingredient is one to many, and one of the ingredients can be a product, then that sounds like a many to many relationship to me.

I havent changed anything in the "raw data" table. i made a create table query for both product and raw material code that made a list of all the codes without duplicates creating two new tables with unique codes as the PKs. i then used these PKs as foreign keys in the raw data table.

yes i guess this is a many to many relationship then

would it be problematic that a code could exist in both the raw material and product table when in reality they are their own unique item?

perhaps i should make just one table containing all the items? some of them would have formulations and some of them not, this list would then be the foreign key for both the product field and the raw material field, is that possible?

Casperaprel · Nov 21, 2019

Hi, i'm asking how do i convert it from combo box item to list box

example my combo box control source is "IDSUMARRY '' and my row source is" SELECT tblProgram.ID, tblProgram.Project
FROM tblProgram;
"

how to convert to a text box. TQ

theDBguy · Nov 21, 2019

Casperaprel said:
Hi, i'm asking how do i convert it from combo box item to list box

example my combo box control source is "IDSUMARRY '' and my row source is" SELECT tblProgram.ID, tblProgram.Project
FROM tblProgram;
"

how to convert to a text box. TQ

Hi. Welcome to AWF! You should start your own thread, since your topic is unrelated to this one.

papa john · Nov 22, 2019

papa john said:
the stock management system that is used is called Opera 3

in opera there are three types of items; components, sub assemblies and assemblies.

components are items that are used in assemblies or sub assemblies.

sub assemblies are made up of components or sub assemblies but go into other assemblies or sub assemblies.

assemblies are made up of components or sub assemblies but do not go into anything else.

can anyone think of a way that an assembly or sub assembly could be "exploded out" / "broken back" to show all the components it contains and to show what % of those components it would have. consider that there could be multiple levels, an assembly could contain a sub assembly that contains a sub assembly etc.

product 3 in my example in OP would be a product that contains sub assemblies. the raw data export would only have one level ie in the product field it would have the product code, in the raw material field it would have the sub assembly code, but there isnt another field which says what goes into the sub assembly. although the sub assembly would have it own record which says what goes into it.

to determine the %of a component in the top level the component percentage in the sub assembly would have to be multiplied by the sub assembly percentage in the next level sub assembly and so on until the assembly is reached.

please could someone answer this

theDBguy · Nov 22, 2019

papa john said:
please could someone answer this

Hi. Depending on what you have in mind, take a look at TreeListView controls.

Micron · Nov 22, 2019

Sorry but I find the thread a bit confusing. The original question was 'do I have to normalize' but it seems that it has morphed into something else, plus you have made design changes that if you've illustrated, I missed them even though I just looked again. Perhaps it would be better if you could post a db copy with some instructions on what to do and what to look for, and probably most important - where you want to end up.

papa john · Nov 22, 2019

really need to be able to

papa john · Nov 22, 2019

attach images

papa john · Nov 22, 2019

need 10 or more posts

Formulation lookup (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

AWF VIP

Super Moderator

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

New member

I’m here to help

Registered User.

I’m here to help

AWF VIP

Registered User.

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread