Help to create database and form

[Avogadrow]

Dear all
Hi, I am a new member and I have no experience in Access.


What I need, If any one can help me to create an access file contain database and a form to enter new data.
I've attached an excel sheet to explain what I need.
the purpose of this Access file is:
I have a list of many products, each product has three or four component, each component has to be analysed in the Lab by four or five methods, we have to develop an analytical method for each method, each developed method has to be validated by six or seven items. if all items of the validation are done, at this time the SOP (standard operating procedure) should be issued.

[June7]

Since no one has stepped in, I will offer this for your consideration. Your question is too broad. Suggest you spend a solid week studying an introductory tutorial book. Develop a schema and when you have a design for us to analyze, post question.

[Avogadrow]

Since no one has stepped in, I will offer this for your consideration. Your question is too broad. Suggest you spend a solid week studying an introductory tutorial book. Develop a schema and when you have a design for us to analyze, post question.

Thank you for your advise. I tried to develop an access database but the result is very bad. I already downloaded many youtube regarding this topic but it is very difficult for me to understand all these information. if you can share the introductory tutorial book that you mentioned.

[June7]

I don't have a specific book to recommend. Don't remember what I used years ago. Search Amazon or B&N or local used book store.

I learned VBA programming from a bunch of old used cheap VB reference books from Barnes & Noble. VB and VBA have enough in common that learning one carries over to other. I also already understood programming concepts from other languages.

[Micron]

IMO the main purpose of a forum like this isn't to teach as you would be taught in a school setting. Nor is it a forum for finding someone to develop a full blown application for you at no charge. Even with a lot of experience it can be a time consuming thing, a lot of which involves understanding the nature of the business. It's a place where you try and then ask why it isn't working or how to improve, but you have to put the bulk of the work into it.

Maybe some of these links will help while you look for old books as June7 suggested. Note: the "How do I create an application" looks like not much, but there are several links within the article that will lead you to other pertinent information. Also, you can still download the Access Bible in pdf format - maybe a Google search will lead you to it. Sure it's outdated but probably 95% of it still applies. When you do research in your chosen medium (video, books, whatever) and something or someone isn't "speaking" to you, find another teacher/author until you find one that does.

Normalization is of paramount importance.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[Avogadrow]

Thank you alot

[Avogadrow]

Dear Micron, thank you for helping.
I already developed a database and form for my project (it is not for business, my uploaded excel sheet is enough). I do not figure out what is the problem in my developed access data. I'll try again after reading the articles you shared and back again.
thank you!

[Micron]

You're welcome, and good luck.

[CarlettoFed]

Product	Component	method	item1	item2	item3	item4	item5	item6	item7	item8
Product1	Component1	method1	System suitability	Specificity
Product1	Component1	method2	System suitability	Specificity	Forced degradation
Product1	Component1	method3	Specificity	Forced degradation
Product1	Component1	method4	System suitability	Specificity	Forced degradation	Accuracy
Product2	Component2	method5	Linearity	Accuracy	Precision 1
Product2	Component2	method5	System suitability	Specificity	Forced degradation	Linearity	Accuracy	Precision 1	Precision 2	Ruggedness

You should create a practical example, like the one shown, and explain step by step what you would like to do in order to understand how to act.

[Avogadrow]

[QUOTE=CarlettoFed;452791]

Product	Component	method	item1	item2	item3	item4	item5	item6	item7	item8
Product1	Component1	method1	System suitability	Specificity
Product1	Component1	method2	System suitability	Specificity	Forced degradation
Product1	Component1	method3	Specificity	Forced degradation
Product1	Component1	method4	System suitability	Specificity	Forced degradation	Accuracy
Product2	Component2	method5	Linearity	Accuracy	Precision 1
Product2	Component2	method5	System suitability	Specificity	Forced degradation	Linearity	Accuracy	Precision 1	Precision 2	Ruggedness

You should create a practical example, like the one shown, and explain step by step what you would like to do in order to understand how to act.[/QUOT
Thank you so much, and sorry for delaying reply. please find attached excel sheet like your example as well as access file which I created based on my poor experience. Access file is designed for one component only in the product, I built a multi to multi relationship. if you have time to see it and edit it to cover multi component, your work is appreciated.

[CarlettoFed]

I ask you:

- the methods to be applied to the Component are always the six you have written or they can be less or more?
- for each Method the validations are always the eight you have written or they can be less or more?
- in the example in Excel you say that to be sent it must always pass the Ruggdness exam but then in the Access example this does not appear exam validation form Assay

- let's examine product 16 - kalawa:

- in the AssayName1 check you assign done
- but in the form Assay the controls report

- ss -> done
- Specificity -> not
- ForcedDegradation -> not

it would seem that, not being all three set to done, it has not passed this exam and therefore in the Products form, done should not be reported.
is my interpretation correct?

[Avogadrow]

Hi Carlettofed,
the Excel sheet is the right structure, the Access was made to test the relationship only so I created it with minimum items.
to clarify mu process the below example will help:
The product name is multivitamins tablet lets call it Centr.
Centr tablet contain 7 active ingredients (Vit C, Vit B1, Vit B2, Vit B3, Vit B6, Vit A and Vit E).
now we will starting to develop analytical methods
after lab work done we found that we developed 3 methods of analysis :
Method 1 to determine Vit C, Vit b1 and Vit B2
Method 2 to determine Vit B3, Vit B6 and Vit A
Method 3 to determine Vit E
after this development process done and now we have 3 methods of analysis to determine only the content ( assay) of each vit.
Now we have to develop new methods to determine the impurities in each Vit, methods to test the dissolution of the tablets, methods to check the homogeneity of the tablets which we call it Single assay test, methods to determine the residues after cleaning of the press machine this method we call Swab and finally another method to determine the residues after cleaning of the press machine this method we call rinse.
Suppose, 3 methods for Assay each method determine no. of Vit. in general, the number of method may be increased or decreased.
7 methods for Single Assay, each method determine one Vit only. in general, in general, the number of method may be decreased.
7 methods for impurities (some times they call it related substance method) for each vit. in general, in general, the number of method may be decreased
7 methods for Dissolution Test for each Vit. in general, in general, the number of method may be decreased
7 methods for swab Test for each Vit. in general, in general, the number of method may be decreased
7 methods for Rinse Test for each Vit. in general, in general, the number of method may be decreased.

Now we have methods for each category ( assay, single assay, related (impurities), dissolution, swab and rinse).
each developed method we have to record that method is well developed and accepted.
After recording method development status, each method has to be validated.
each method has to tested by the following items to complete the validation of the methods:
1. System suitability
2. specificity
3. Forced degradation
4. Accuracy
5. Linearity
6. Precision 1
7. Precision 2
8. Robustness
8. Ruggedness
To say the validation is completed, each test from the above has to be passed, and the final test is ruggedness.
if the Ruggedness test is passed we can now say the validation process is passed and accepted and it is the time to issue an SOP for each method.

I know it is very complected process, I tried only to connect the relationship for one component only like Vit C in the above example.
thank you

[Avogadrow]

Hi Carlettofed,
I've restructured the access file to fit the above example of Centr tablet product. Centr tablet is multivitamin tablet contains Vit C, Vit B1, Vit B2, Vit B3, Vit B6, Vit A and Vit E.
My limited experience allows my to create access file to collect data for assay of Vit C, Vit B1, Vit B2 (method 1) and single assay of Vitc only I can not connect all the needing data.
could you please have a look.

[CarlettoFed]

Product	component	Method	Validation of the the method								SOP
Product1	component1	Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component1	Single Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component2	Single Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component2	rinse sample	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component2	dissolution Test	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component3	Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component3	Single Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component3	Impurity	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product1	component3	dissolution Test	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	Single Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	Impurity	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	swab sample	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	rinse sample	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component1	dissolution Test	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed
Product2	component2	Assay	System Suitability	Specificity	Forced Degradation	Accuracy	Linearity	Precision1	Precision2	Ruggdness	send if Ruggdness is passed

I explain my interpretation:

A Product can have one or more components
The components can be subjected to one or more test methods
each Method must undergo 8 Validations

the Ruggdness Validation decides whether to send it

now let's see the Validations:

must occur in succession or you cannot switch from System Suitability to Specificity if System Suitability is not set to Done

[Avogadrow]

Thank you so much,
this is what I need,
Regarding the Validations:It is not occur in succession, you can switch from System Suitability to Specificity if System Suitability is not set to Done.