Solved

Access Database Design Question

Posted on 2011-02-27
12
235 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

930 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

10 Experts available now in Live!

Get 1:1 Help Now