Dear NG,
In an earlier post to the group, I was trying to find and easy way to
calculate %change estimates between years for a group of variables. My
data looks like this:
Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21
While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.
It was suggested that I restructure my table to look like the
following:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7
This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.
Anyhow, the following Harvest Table is one of many tables that I built
in Access.
Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55
Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on
Another table would look like the following:
Year County District DVAs(deer-car crashes) RegisteredVehic les
1995 Athens 1 389 560,897
Finally, another table might be:
Year County District PermitType1 PermitType2
So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.
Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.
County Year Sex Season District RegulationType
If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.
Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.
Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.
Thanks in Advance...
Mike 2 1345
Takeadoe wrote:
Dear NG,
Hi Mike, please see responses inline.
In an earlier post to the group, I was trying to find and easy way to
calculate %change estimates between years for a group of variables. My
data looks like this:
Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21
While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.
It was suggested that I restructure my table to look like the
following:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7
This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.
Anyhow, the following Harvest Table is one of many tables that I built
in Access.
Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55
Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on
Another table would look like the following:
Year County District DVAs(deer-car crashes) RegisteredVehic les
1995 Athens 1 389 560,897
Finally, another table might be:
Year County District PermitType1 PermitType2
I think you're on the right track. You have modeled three entities:
Harvests, Crashes, and Permits. There's some room for improvement I
suspect, read on...
>
So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.
Since you have so many entities to model, separate tables are the way to
go. You mention counties Vs. districts. This is a good lead: In a couple
of your tables you have county and district. Do counties belong to
districts? If yes, then district is dependent on county. There's no need
to store this dependent information twice in a table in RDB design. Keep
it in a separate table.
Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.
County Year Sex Season District RegulationType
I doubt you should structure your tables based on analysis needs. You
can join related elements using queries to get at the data you want.
Unless you are processing data warehouse volumes (which is unlikely),
carefully setting up your tables using good normalization practice will
serve you well for your current and future needs.
>
If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.
Why not? As you proposed above, these two tables have common elements
(year, county/district). You can join on these commonalities and, if
desired, summarize by the specifics. RDB design makes this relatively
easy. A "flat" table design with everything in one row does not.
>
Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.
Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.
I think you are on the right path Mike. I also think you would benefit
from taking a couple hours to study on "database normalization" (Google
that, with the quotes) to familiarize yourself with the concepts --
although I do believe you are well on the way to embracing this on your own.
>
Thanks in Advance...
Mike
Good luck
--
Smartin
Smartin,
Thank you very much for your time and assistance. I have just printed
40 pages worth of articles on database normalization and will dig in
tonight (bed time reading!).
Thanks again. I will do my best to PayForward!
Mike
Smartin wrote:
Takeadoe wrote:
Dear NG,
Hi Mike, please see responses inline.
In an earlier post to the group, I was trying to find and easy way to
calculate %change estimates between years for a group of variables. My
data looks like this:
Year County VarA VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21
While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.
It was suggested that I restructure my table to look like the
following:
1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7
This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.
Anyhow, the following Harvest Table is one of many tables that I built
in Access.
Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55
Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on
Another table would look like the following:
Year County District DVAs(deer-car crashes) RegisteredVehic les
1995 Athens 1 389 560,897
Finally, another table might be:
Year County District PermitType1 PermitType2
I think you're on the right track. You have modeled three entities:
Harvests, Crashes, and Permits. There's some room for improvement I
suspect, read on...
So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.
Since you have so many entities to model, separate tables are the way to
go. You mention counties Vs. districts. This is a good lead: In a couple
of your tables you have county and district. Do counties belong to
districts? If yes, then district is dependent on county. There's no need
to store this dependent information twice in a table in RDB design. Keep
it in a separate table.
Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.
County Year Sex Season District RegulationType
I doubt you should structure your tables based on analysis needs. You
can join related elements using queries to get at the data you want.
Unless you are processing data warehouse volumes (which is unlikely),
carefully setting up your tables using good normalization practice will
serve you well for your current and future needs.
If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.
Why not? As you proposed above, these two tables have common elements
(year, county/district). You can join on these commonalities and, if
desired, summarize by the specifics. RDB design makes this relatively
easy. A "flat" table design with everything in one row does not.
Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.
Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.
I think you are on the right path Mike. I also think you would benefit
from taking a couple hours to study on "database normalization" (Google
that, with the quotes) to familiarize yourself with the concepts --
although I do believe you are well on the way to embracing this on your own.
Thanks in Advance...
Mike
Good luck
--
Smartin
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: paul kölle |
last post by:
hi all,
I noticed that setUp() and tearDown() is run before and after *earch*
test* method in my TestCase subclasses. I'd like to run them *once* for
each TestCase subclass. How do I do that.
thanks
paul
|
by: Paul .V. |
last post by:
I need to setup two tables with default information.
First off I need a company table. Quite simple. Name, Address, phone,
tax number and so on. So I have setup that table with the fiels
corresponding to the above.
Question #1
When I print a report I need to look up one value, the tax number. I
assume I use the dLookup function but I get an #Error. Relating to my
|
by: Rosy |
last post by:
I am setting up a DB for our company and am at a loss on the best way
to handle some of the records.
I have a table (tblJobs) that houses the basic info on the job (when,
where, what, who). These jobs are assigned a job number based on the
year and autonumber (05-3589). However, sometimes these jobs spawn new
jobs that have to keep the original job number and add an alphabet
(05-3589A). I have another table (tblTime) that houses the...
|
by: shahram.shirazi |
last post by:
Hi guys,
I was wondering if someone could help me a bit here. Im trying to
desing an electronic register system for a school. In terms of the
table design, I obviously need a Student Details table with such
attributes as ID, FNAME, SNAME, Age, Tutor Group, Class, Dependant.
The bit i'm having trouble conceptualising is the system needs to be
|
by: news reader |
last post by:
Hi,
Does anoone of you know if there is already a simple application doing
something like this.
I would enhance / tune the missing features, but would like to avoid to
start from scratch or to start from something, that has far too many
features and is difficult to setup.
| |
by: Bob |
last post by:
I want my aps to the insalled as was the old way under c:\Program
files\Myapp or give the user the choice as before of installing the app in
another folder location.
That again no longer seems to be the default way things are done when you
publish an app in Vs2005, Microsoft has again <GGGG> seen in their infinite
wisdom that they needed to build a better mousetrap for us. Now they
install app somewhere in the mydocuments folder...
|
by: Joshua.Buss |
last post by:
I am trying to move a record from one linked table to another within
access, but I'm a complete beginner to VBA and don't know exactly where
to begin.
I have an access file that has the two linked tables, which each link
to separate SQL databases using separate ODBC connections.
I have a form that has a view of table1 and a set of buttons for
manipulating the data in database1.
|
by: jp45678 |
last post by:
Hi, been trying to do this for literally hours, cannot for the life of me remember how to do it.
I have one table for Clients, just surname, forename, address, contact number etc, and I have added an ID field.
I have another called Events, where basic information on events i hold is kept, name/date etc, and i also have an Event ID field in this table.
I have setup forms to add new client to the database, and add a new event to the...
|
by: cj |
last post by:
We have a legacy accounting system (not developed in house) here that
happens to be written in Visual FoxPro. One of the tables has an index
that is actually a coded function COMPANY1 &&"G_RETSDX(COMPANY)".
When I try to use this table in VFP I get an error message "File
'g_retsdx.prg' does not exist." I click ok and continue on. We've
developed many VFP programs outside the accounting system that use this
table and we have to include...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |