Brainstorming - Access Application for Quality Management

[DMee]

Greetings All,



I have been assigned a new project to revamp our quality systems. In particular, I need to create a new QA database in Access. This really would need to be it's own application entirely.

I'm looking for help brainstorming how to best create it. I want to use Access. I have no prior experience designing an application or using Access to do so, but I know it's possible because I've used one that works well before.

So, for the access experts out there, how hard would it be to create an application in access that does the following:

- Allows a user to print a test sheet for a certain product. *Note - this will need to be linked to a specification table or something somewhere, so the appropriate tests for each part print.
- Allows a user to enter / input lot testing data for each manufacturing run
- Create certificates of analysis, which would then be generated in PDF format

How would you approach this?

[CJ_London]

No necessary hard, providing you put the work in first

To quote my advice to another poster:

a good developer will spend some time with the client to clarify the actual requirements in detail, suggest ‘what if’ scenarios to test the viability of those requirements. determine constraints around populating the tables, human interaction with the data and reporting.

at the moment you are missing the details. from this you can build your schema (tables and relationships)

[Bob Fitz]

How would you approach this?

By reading about db design at table relationships.

Do you envisage the db being use on just a single computer?

[Micron]

Having done a couple of QS types of db's (ISO 14001 and ISO 9000) then can definitely say it's possible. If you want it to function well, you have to define the requirements as CJ_London says but you are going to be the developer you will have to put in a lot of up front learning time if you want it to function well. If not, do yourselves a favour and contract out the job.

[NoellaG]

Personally I like working with user stories. It forces you to think about different users with different roles that are going to use the application. Examples: https://www.atlassian.com/agile/proj...t/user-stories

[June7]

I was tasked to complete development of an Access db to replace a 20-yr old dBase IV DOS system. I inherited the partially built Access db that already had about 4 months work. Database design and programming wasn't foreign to me but never tackled anything major. I was lucky enough to have a consultant to rely on for some guidance. After 3 months I quit calling on the consultant. It took me 2 years to program, test, deploy. That included 1 year of side-by-side data entry with the old system. This db focus is processing and reporting of construction samples testing.

How does one approach anything new? You learn. In this case, the basics of RDBMS principles, Access functionality, programming concepts, and VBA language. Study a book or take a class.
Also helps to understand project management.

Identify data entities and relationships, build tables. Design forms and reports.

Or hire a contractor to build and support the product. That's what another of my employers did.

[orange]

DMee,

You have been given good general advice in the posts. Ask questions of potential users and bosses. Look for a patron who needs this application and has sufficient authority to "work through internal politics". Build some models - however simple they may be - and get confirmation/feedback (avoid making assumptions). Get others involved.
There are number of articles and links in the Database Planning and Design link in my signature that you may find useful. The BA-Expert knowledge nuggets are a humorous source of info to help with insight on some topics. I find videos and examples good sources to learn/overview new topics - but people have individual preferences when it comes to learning. You may prefer books, or reviewing existing code.....

Bottom line is Access and database are not magic bullets that will build an application for you. Access won't do anything for you that you don't tell it or design it to do. You have to fully understand the requirements of this proposed application before getting into Access. Database design is a whole subject in itself. But, since you have been tasked, use the project to educate yourself while solving the "company's" underlying quality concern.

Good luck. The forum is here to respond to questions and offer advice.

[DMee]

Hi All,

I spent some time over the weekend reviewing some information provided (thanks!)

I believe I will need 5 tables:

1) tblProducts
- PartNumber (primary key)
- Description

2) tblTestData
- RunID (primary key)
- WO#
- SO#
- PartNumber
- TestID
- Test Result

3)tblTest
- TestID (primary key)
- TestName

4)tblSpecTests
- SpecTestID (primary key)
- SpecID
- TestID

5) tblProductSpecs
- SpecID (primary key)
- SpecName

So, with tblProducts, tblTestData, tblTests, tblSpecTests, and tblProductSpecs...

Each part number can be run on different work orders (one - many relationship between tblProducts.PartNumber and tblTestData.PartNumber
Each test can be performed on different work orders (one - many relationship between tblTests.TestID and tblTestData.TestID)
Each spec can contain many tests and each test can be assigned to each spec (many - many relationship using junction table as follows: one - many relationship between tblTests.TestID and tblSpecTests.TestID AND one - many relationship between tblProductSpecs.SpecId and tblSpecTests.SpecID)

My question is -- is the SpecTestID field in the tblSpecTests unneeded?

Thanks,
D

[orange]

Does this set up allow for replicate tests -- that is say 10 replications of a test?
My first reaction to " is the SpecTestID field in the tblSpecTests unneeded" is probably not necessary if you use the combination of - SpecID and TestID as a compound PK/FK.
Have you mocked up relationships and tested the model with some sample test data?

[Bob Fitz]

Might be better to rename fields WO# and SO# so they don't use "#" in their names. See: https://learn.microsoft.com/en-us/of...ial-characters

[DMee]

Hi Orange - thanks for your reply!

It does allow for replicate tests -- I confirmed by adding another entry to the tblTestData with the same test ID, WO#, SO#, and PartNumber.

I confirmed the model has the desired integrity -- I cannot enter test data for a part that doesnt exist, or for a test that has not been entered..

From what I can tell, assuming this is "complete", from here, I am just building queries, forms, macros, etc?

Thanks again , Orange.

[DMee]

Great call Bob! I'll check out the link.

[Micron]

Each part number can be run on different work orders (one - many relationship between tblProducts.PartNumber and tblTestData.PartNumber

Can't tell if a run is about production or testing.

Shouldn't there be a WO (not WO# !!) and SO table?
Looks like you're using real data as pk's. Many think it's best to not do that. If you are, that means you're using it in as foreign key fields as well.

What is TestID in tblTest if not the pk field? Can 1 test have multiple runs, or one run have multiple tests? If yes to one or the other but not both, then it matters which one is the pk.

If tblSpecTests is for specifications that would be applied to tests it seems the table is misnamed (tblTestSpecs). If that's what it is, I say the TestID field does not belong there because the id for a test is not an attribute of a specification. I think I'm missing the gist of what a spec is vs what a test is. This isn't the sort of quality systems db I thought the post was about so I will probably not be contributing much.

[DMee]

Hi Micron,

Apologies for the confusion(s).

A "run" = a manufacturing order for "Product A"
A "spec" = a list of required tests and limits. Typically unique for a product (though one product can have many specs)

One "run" can and will have many "tests". One "test" can be performed on several "runs"

Bit confused by your last paragraph. tblSpecTests is supposed to be a table that captures all the "tests" required for any particular "spec". I hope this has been clear. Thanks for your reply!

[Micron]

My confusion probably stems from the notion that a spec, aka specification, is information that defines requirements about things like dimensions, weight/mass, colour, etc., sometimes including upper and lower limits. So a part may have a specification like 'width: 1.255" +/- .01' . A test is a procedure, often using calibrated equipment, that measures whether or not the specification(s) are within parameters. Your definition of spec seems to be about tests and whether or not the test measurements are within "tolerances". Not that your way is wrong, it's just different than what I'm used to.