Solved

Access Database Design Question

Posted on 2011-02-27
12
234 Views
Last Modified: 2012-05-11
Please help. I was asked to create an Access Db to help our office manage some tasks. I am new-ish to access and understand a lot but also am learning some concepts. Below is something I am having trouble on.

My data is three tiered.
1. Regions (5 Regions)
2. Areas (500 areas that all relate to one of the 5 Regions)
3. Data. (1000 of records that relate one of the 500 Areas)

Data is linked to Areas and Areas are linked to Regions.

I need to figure out the best way for a user to:
1. Chose a Region
2. That displays the Areas associated with that Region
3. That then displays the Data associated with that area.
4. When a user enters a new record, the Area portion is auto filled in, even if the Area's Data is null. Meaning, I have an Area that does not contain any associated Data (records). When I use a query method to display this info and I attempt to add a record, I get an error. But if there is at least one record associated with the Area, then I can add a new record... Sheesh Sorry.

Should I use Queries or Filters? Am I way off base?

Any help would be great. I can clarify anything if needed.

THANKS in advance.
0
Comment
Question by:moot2010
  • 5
  • 4
  • 2
12 Comments
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
Do you have a table for each of the levels?
so a region table table with autonumber region id... as the primary key.

Then in the area table, each area will have an area id autonumber or code as its primary key, but then also each area will have a region ID foreign key.

E/.g
Region:
Region ID, description
1, the midlands

Area:
Area ID, description, RegionID
17, Evington, 1

then the same with data... each item should have a data id, and an area id.

=============================================================

I need to figure out the best way for a user to:
1. Chose a Region
Have a form (frmMain) with a dropdown (cmbRegion) which selects region..
2. That displays the Areas associated with that Region
have a dropdown (cmbArea) which selects from area table where is record source is a query with area fields where area.regionID = forms!frmMain!cmbRegion.value
3. That then displays the Data associated with that area.
Subform which selects data where data.areaid = forms!frmMain!cmbArea.value, which is refreshed by a change to cmbArea...
4. When a user enters a new record, the Area portion is auto filled in, even if the Area's Data is null. Meaning, I have an Area that does not contain any associated Data (records). When I use a query method to display this info and I attempt to add a record, I get an error. But if there is at least one record associated with the Area, then I can add a new record... Sheesh Sorry.

I don't think you can use  query to add a new record like that if the data set is empty.
I would have an add new record form which is based on a wizard of the data table, but the areaid would be pre-filled from forms!frmMain!cmbArea.value

Do you need a sample DB?  can you provide any sample data?
0
 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 250 total points
Comment Utility
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 250 total points
Comment Utility
Have created a little database that demonstrates

3 tables, Regions, Areas and data

1 Form to show the data

Should act as a starting point for you sample.accdb
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
Above is a sample file with the data how i would structure it, and a sample form for browsing, editing and new data entry.
0
 

Author Comment

by:moot2010
Comment Utility
All, thanks for the help. I was trying to respond Sudonim's initial reply and lost connection to the website when I tried to submit. (Dont ask. :)

I have downloaded the sample Db from both Sudonim and TinTombStone. Give me a minute to look at it.

To answer your initial reply, Yes.

Region Table
Fields
1. Primary Key, "RegionID", autonumber
2. "Region", Text

Area Table
Fields
1. Primary Key, "AreaID", autonumber
2. "Area", Text
3. "RegionID", number, using a one-to-many relationship with Region

Data Table
Fields
1. Primary Key, "AreaID", autonumber
2. Many Fields with applicable info
3. "AreaID", number, using a one-to-many relationship with Region

I have tried using Queries. The data is correct except for one instance. When the Data records are null for a certain area. Then all heck breaks lose and I can't add a new record. I get why, but it still is not cool man!

I just started trying to use Filters and I may be getting the syntax wrong, but am having a hard time filtering the Data form (based soley on the Data table) to filter and find records related to, les say, area 1. It just doesn't work well. (thats my fault im sure)

Again, Im going to look at both of your Db's right now.

THANKS! Ill post a response when I am done.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:moot2010
Comment Utility
Correction:

Data Table
*"AreaID", number, using a one-to-many relationship with Area
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
the filtering in my version is done in the query which provides the recordset.  more efficient to do it that way if the form is also going to be used for data entry.
0
 

Author Comment

by:moot2010
Comment Utility
Wow,

Among other things, you both showed me how to base a Form on a query. I did not know I could do that. You both responded at the same time and I have learned some much needed techniques from both of your Db's. I am rather new to Experts Exchange. Can I give both of you points? I don't think it would be fair to only give one of you points.

Oh yeah, and THANKS a lot. You both have ended a lot of frustration on my part.
0
 

Author Comment

by:moot2010
Comment Utility
Thank you SouthMod, I'll make sure to take the time to read any FAQ's or tips next time.

Thanks again to Sudonim and TinTombStone for your help and sample Db's. You've both answered this question and shown me new examples of issues I would have had down the road.

Experts Exchange is too cool! Well worth the money spent with just this one question alone!
0
 
LVL 6

Expert Comment

by:TinTombStone
Comment Utility
Thanks Moot

Good luck with the rest of your database
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now