asked on

Access Database Design Question

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.

Si Ball

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?

SOLUTION

Si Ball

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

TinTombStone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Si Ball

Above is a sample file with the data how i would structure it, and a sample form for browsing, editing and new data entry.

moot2010

ASKER

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.

moot2010

ASKER

Correction:

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

Si Ball

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.

moot2010

ASKER

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.

moot2010

ASKER

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!

TinTombStone

Thanks Moot

Good luck with the rest of your database

Si Ball

thanks.