Solved

Access Database Design Question

Posted on 2011-02-27
12
237 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
ID: 34995165
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
ID: 34995279
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 250 total points
ID: 34995282
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 15

Expert Comment

by:Simon Ball
ID: 34995285
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
ID: 34995803
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
 

Author Comment

by:moot2010
ID: 34995808
Correction:

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

Expert Comment

by:Simon Ball
ID: 34995966
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
ID: 34996313
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
ID: 34996462
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
ID: 34996603
Thanks Moot

Good luck with the rest of your database
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34997028
thanks.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

789 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