Lookup Wizard and forms. (1 Viewer)

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Hey guys. I'm new to Access, and new here. I was assigned a project at work and it requires me to create a database, and an application for end users with Access. I'm already running into some issues. The application is going to be used for weekly assessments. It needs to include checklists for assessing multiple areas of multiple buildings.

What I'd like to do:

I have a small understanding of how the Lookup Wizard works. When creating my first table, I used it to create a drop down box that allows the user to choose what building they are assessing. Here's where the trouble comes; once the user chooses what building they're in, they'll need to then choose what area of the building they are assessing. What I'd like to do is create a look up or a macro that only updates the areas of the building that the user just selected.

For instance:

The sections are all numbered or named differently for each building. So the areas in "Building A" are called "Area 1", Area 2, etc. For Building B, every area is called "Section 1-101", or "Section 1-102". When the user selects Building A, can I build a macro or something that only updates areas in building A. So that when the user chooses a specific building, only the areas in that specific building appear as options in the next field. And can this be done for multiple buildings? Does that even make sense?

Any help would be appreciated,
Thanks
 

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
Well, step one... back away from the Look-Up Wizards and see...
http://www.mvps.org/access/lookupfields.htm

You can use what we like to refer to as *Look-Up tables*. Look-Up Tables are simply regular tables that hold lists of values for use in Combo Boxes on forms. This way you can add/change/remove items from the lists with ease.

Then, some reading (get some coffee)...
Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

The first thing your going to need is Tables, so let's build those first and post them here and we will be more than happy to review and give our assistance. But please, take a peek at the above it will help in building Tables, especially the Data Models links.
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Ok. I read a lot of that. Not all of it because a lot of the links didn't work. But enough of it to have a better understanding of the power of MS Access, and how I should begin this struggle.

As far as the tables go, I am thinking about what the most appropriate way to structure them is. Maybe you can help me with that? I just need a little help here so that I don't create useless tables, and turn them into useless forms that give me useless reports and don't answer my queries. So here is what I'm looking at.

There are 5 buildings, each with their own specific areas (about 20-30 areas in each building). Each one of those buildings needs to be assessed and there are 13 categories by which they need to be assessed. Each of the 13 Categories has about 10 questions that need to be answered in a Yes/No format, while allowing the user to provide a note if needed. The purpose of this whole project is so that the data put in by the end users (in this case, the people doing assessments) can be used to create reports on the whole site.

One of the links you mentioned above said that I should avoid putting too much info within the tables, and to have a good amount of preliminary tables. Posting the tables on here is going to be an issue (company policy is very strict). If you can provide assistance without getting a copy of them, that would be great. I could make a dummy version of this project if that would help?

The tables that I created are as follows:
1. The first table will document the users ID #, the building they are in, the area they assessed, date, and time. The primary key is the user ID #.
2. This is where my confusion begins. How should I orient the next few tables to include the buildings and their specific areas? Should I make one table for each building that lists each of its sections? Should I make one large table that includes all of the buildings and their specific sections/areas?
3. How should I orient the tables with the actual assessment questions on them? Should I make 1 table for each category? And write the questions as "fields", with "yes/no" being the data type?

Thank you again for your help,

Zoober1
 

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
Okay...

1. Just tested all the links and they alll worked for me, might be something at your work.

2. Posting the tables does not reveal anything so not sure why that would be a problem. No data needs to entered... I mean how much can a field name reveal?

As for what you have already...

Users should be in one table with all pertinent information. Anything *associated* to the Users should be in an ajoining table.

Buildings go in their own table and Areas in another (I think as I'm not sure what Areas are) and then you need a Junction table. Same goes for Sections.

Assessments go in another table and Categories in another with a Junction table. For the actual assessments this depends on how you want it set it up, like a survey? If so, have a look at...

Survey Database by Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
As for posting the tables, all of the information on them is considered proprietary. The field names themselves are considered proprietary information. I know it sounds silly. I could make one with fake names I suppose if you think that would help.

I've created all of the tables that you mentioned. And I'm sorry if this is a stupid question but, what do you mean by Junction Table?
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:51
Joined
Jan 23, 2006
Messages
15,364
Here's a tutorial to help you with table design and relationships.

Good luck.
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
(I think as I'm not sure what Areas are)
I'll try to explain how the buildings work:

I use the words "Area" and "Section" as synonyms. They are the same thing essentially, but each building calls them something different. They are essentially cells within a building that perform different functions, and each cell needs to be assessed separately.

For instance, Building A has Cell 1, Cell 2, Cell 3. Building B operates the same way, but in Building B, the cells are referred to as Positions (i.e. Position 1, Position 2 etc.)

Does that help?
 

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
Then this still holds no matter what you call them Areas, Cells or Rooms...

Buildings go in their own table and Areas in another (I think as I'm not sure what Areas are) and then you need a Junction table.

But you only need the two tables. You name the table something generic and then you can use for the many part of the Building. Nobody should ever see the table so the name is irrelevant. Maybe this will help...

Buckets (Tables) hold Water (Data). It is irrevelant what colour the Bucket is as long as it holds the Water!

EDIT/ADD: And that link posted by JDraw has great information so make sure you read that.
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Sorry, my tables didn't attach. Here they are...i think
 

Attachments

  • Assessment Tables.zip
    94.1 KB · Views: 72

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
Okay, I adjusted your Tables... hopefully, this gives you a better idea of what you have to as you go forward...

Also, I changed some field names because you were using Reserved Words which just makes life difficult for Access and you, so, here's a complete list...

http://allenbrowne.com/AppIssueBadWord.html

You might also want to re-read...
Naming Conventions…
http://www.access-diva.com/d1.html

...and why you should adapt one.
 

Attachments

  • AssessmentTables.accdb
    648 KB · Views: 84

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Thank you for doing that. It really helps me understand what normalized tables should look like. And it drives home the idea that everything in the table should depend on the entire primary key.

I am so relieved. Thank you.

So the next step from here is to create the forms, am I right?
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Also, I have a question about one of the tables. The tblEmployees assigns the employees a surrogate ID, and it's the primary key. What if the employee has their own 8 digit ID number? That would work as a primary key right? Because the rest of the fields in the table depend on that one?
 

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
Glad to do and even happier it gave you a basic understanding of normalization.

In those cases, where the Company has ID's for the Employees, I add a field named *eEmployeeNumber* and make it TEXT, so that no matter what, like the reall ID isn't available, I can add it later but still maintain the integrity of the table.
 

zoober1

Registered User.
Local time
Today, 11:51
Joined
Jun 24, 2014
Messages
11
Hey Gina,

I have a question about one of the tables that you created for my database. Its the "tblBuildingCategories" table. I'm not sure of its applicability for the purpose of the database. The buildings that are going to be assessed don't need to be placed into categories....rather, the assessments are going to be in multiple categories. For instance, "Part Identification" is an assessment category, and "daily clean up" is a category. I need to build the relationships with "tblAssessment", then "tblAssessmentCategories", then "tblPartIDQuestions" or "tblDailyCleanUpQuestions". Is there a chance you could help me with that? Thanks again
 

GinaWhipp

AWF VIP
Local time
Today, 14:51
Joined
Jun 21, 2011
Messages
5,901
I created tblBuildingCategories because of the tables I saw in your database but upon RElooking I see perhaps that should be tblAssessmentCategories, so, yes, you are correct.

Add you new tables (saving me a little time and guesswork) and then upload the file and I'll review.
 

Users who are viewing this thread

Top Bottom