Database Guidance

[On3K]

Hello All, I am building a vendor management database for my organization. I do not have a lot of experience with access, most of what i have learned was self taught. With that being said I was going to see if I could get some guidance.

I have built a form that will allow you to add a vendor. There are 15 questions that have to be answered for every vendor. I built 15 tables with 1 questions in each table. The idea was to have a question table and the question would auto fill every time you would add a new record. Once I am on the form it does not auto fill like I thought it would. Once all of the vendors and question are answered the form should have all of the data there and you can go back and make modifications, but the data seems to be disappearing on the form when I close out of the form and re open it.

I have attached my database to this post.

If anyone could take a look at what I have done that would be greatly appreciated, I am open to all feedback. Hopefully I made some sense.

Thanks for any help in advance.

 

[Ranman256]

no, you want 1 table with 15 questions.

 

[On3K]

Hi Ranman, Could you explain how I would make that work? The questions that I have will always be default so I want them to auto fill to save the user from having to type each question in every time. Would I be able to set the form up the same, have the vendor form and then the question sub form?

Thanks for the help!

 

[Minty]

You can add all the existing questions using a insert query when the new vendor is added.
They would be accessible on one sub form, with the questions table a source for a junction table tblVendorQuestions.

You would store the VendorID, QuestionID, and probably their answer , and maybe the completion date in this Junction table.

When you add question 16 or eventually question 21, nothing would have to change in your design.

 

[On3K]

Thanks for your response. I think I am following what you are saying. I will build a new database and see if I can build it like you said below. If I have any questions I will reply back here. Thanks again.

 

[On3K]

You can add all the existing questions using a insert query when the new vendor is added.
They would be accessible on one sub form, with the questions table a source for a junction table tblVendorQuestions.

You would store the VendorID, QuestionID, and probably their answer , and maybe the completion date in this Junction table.

When you add question 16 or eventually question 21, nothing would have to change in your design.

Minty, I just started to rebuild the database. I just want to make sure I am thinking the same way that you are.

I will have a vendors table, a question table, insert query correct? If I have all of the question in 1 table I wont be able to specify which question was completed on a certain date? For some reason I am struggling to visualize how all 15 questions can be in 1 table but separate at the same time. I feel like I am just going in circles with my thought process. Sorry for the newbie questions.

 

[jdraw]

On3K,

My recommendation is for you to work through (30-45 minutes) the Entity Relationship tutorial from RogersAccesLibrary {{look in the Topic area for the tutorial}}. You do not start with a physical Access database. Start with a list of requirements -can be in the form of a narrative; do some analysis; make a data model and test it on paper.

By working through the tutorial, you will learn and experience a process that can be used with any database. It will save you countless hours of frustration you will encounter with a trial and error approach to database.

Good luck.

 

[On3K]

On3K,

My recommendation is for you to work through (30-45 minutes) the Entity Relationship tutorial from design_topic238.html"]RogersAccesLibrary[/URL]. You do not start with a physical Access database. Start with a list of requirements -can be in the form of a narrative; do some analysis; make a data model and test it on paper.

By working through the tutorial, you will learn and experience a process that can be used with any database. It will save you countless hours of frustration you will encounter with a trial and erro approach to database.

Good luck.

Jdraw, thanks for the link. I will watch the tutorial ASAP. Thanks for the reply.

 

[jdraw]

On3K,

This is not a tutorial to WATCH, this is a tutorial to work through.

Here is a link with many database videos, articles etc some of which you can watch.

The videos and articles are great reference materials, but the older tutorials from RogersAccessLibrary will guide you through a process --if you work through 1 or 2.

Good luck with your project.

 

[On3K]

On3K,

This is not a tutorial to WATCH, this is a tutorial to work through.

Here is a link with many database videos, articles etc[/URL] some of which you can watch.

The videos and articles are great reference materials, but the older tutorials from RogersAccessLibrary will guide you through a process --if you work through 1 or 2.

Good luck with your project.

Jdraw, I just assumed it was a video tutorial my bad. I went and worked through the one that you linked from RogersAccessLibrary. I feel like I understand the basic concepts behind making tables and the relationships of the database.

With my specific database every vendor will have 15 questions that need answered. I want to get the questions to auto fill for each new vendor that may be added to avoid repetitive typing. So my thought process was to create a form for the vendors and then the question will be added in a sub form. I just don't know how to efficiently get the questions to the sub form without making 15 tables.

I will continue to look through the other information that you linked for me.

I really do appreciate the help!

 

[jdraw]

No problem.
Can you give us a sample of question and answer?
Are the answers explicit eg numbers, yes/no etc, or are they free form text?

You won't have 15 tables.

Vendor --->VendorAnswerToQuestion<---Question

where

tVendor
VendorId PK
VendorName
VendorOther

tQuestion
QuestionID PK
QuestionText


tVendorAnswerToQuestion
VendAnswerID PK
VendorID
QuestionID
QuestionDate 'unique composite index to prevent duplicates
AnswerText

The lime green identifies probable fields to use as a unique composite index

 

[On3K]

No problem.
Can you give us a sample of question and answer?
Are the answers explicit eg numbers, yes/no etc, or are they free form text?

You won't have 15 tables.

Vendor --->VendorAnswerToQuestion<---Question

where

tVendor
VendorId PK
VendorName
VendorOther

tQuestion
QuestionID PK
QuestionText


tVendorAnswerToQuestion
VendAnswerID PK
VendorID
QuestionID
QuestionDate 'unique composite index to prevent duplicates
AnswerText

The lime green identifies probable fields to use as a unique composite index

Okay, I see where you are going with it. A sample question is below. Each question is different.

Question Field - Evaluate the third party's financial condition.
Required Filed - Yes/No
Completed_by filed - Name
Date_Completed filed - xx/xx/xxxx

Is that what you were looking for?

 

[jdraw]

?? not sure
Seems your questions are not really questions, but more like items or point that must be completed.

So, given the 'question" in your post, what exactly would be an acceptable "answer"?

Can you show us a list or a few of these "questions"?

 

[Uncle Gizmo]

The idea was to have a question table and the question would auto fill every time you would add a new record. Once I am on the form it does not auto fill like I thought it would.

I demonstrated how to create a checklist a few years back with video's, text and code.

I also show how to modify the checklist so that you can record text answers to the questions.

There is even the option to have a selection, so that the user can choose an answer from a combo box.

See my blog here for further details:-

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

Sent from my SM-G925F using Tapatalk

 

[On3K]

?? not sure
Seems your questions are not really questions, but more like items or point that must be completed.

So, given the 'question" in your post, what exactly would be an acceptable "answer"?

Can you show us a list or a few of these "questions"?

I have just been calling them questions they may be more statements that need a yes or a no.

I would answer the question in the yes/no required filed. Example - Vendor is XYZ, the statement reads "evaluate the third party's financial condition". If it is applicable then a yes would be recorded. If it is not applicable then it would be a no.

Its almost like a checks and balances, each vendor would be reviewed with the same criteria. Each user would record their name and date for each vendor that they covered.

Other sample questions - "Review the adequacy of the third party's insurance coverage". "Review audit reports or other reports of the third party". "Monitor for compliance with applicable laws, rules, and regulations".

 

[On3K]

I demonstrated how to create a checklist a few years back with video's, text and code.

I also show how to modify the checklist so that you can record text answers to the questions.

There is even the option to have a selection, so that the user can choose an answer from a combo box.

See my blog here for further details:-


Sent from my SM-G925F using Tapatalk

I will check this out, thanks Gizmo.

 

[Uncle Gizmo]

I will check this out, thanks Gizmo.

I did a separate blog on "Checklist Extras"

I demonstrate how to automatically fill the check list when you add a new customer.

I demonstrate how to check and uncheck all of the items.

I demonstrate how modify it to collect, for instance the barcode numbers of a delivery.

Here:-

http://www.niftyaccess.com/check-list-extras/#

Oh! I also demonstrate how you can select from a list of predefined answers with a pop-up form. I haven't shown how this is done, however if enough people express an interest by making a comment under the YouTube video, then I might be persuaded to do a set of videos and blog about it.

Sent from my SM-G925F using Tapatalk