Solved

Access Database Design Question

Posted on 2011-02-27
12
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

739 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