If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
Rookie user: Access 2007, Using “Picture Yourself Learning: Microsoft Access
2007” as reference guide. Need suggestions for how to structure what seems to me to be a very complicated DB. I want to build it the best way the first time, so I don’t do a lot of work and not have it do what I need. Here’s what I need to do: Track GIS datasets for about 50 natural and environmental hazards. The data itself does not need to be tracked. I do need to track its source (National, State, County or City data) and know its date of creation, and frequency of updates for example. I don’t know whether to create one Table with tons of fields, and then try to figure out queries. Or should I create tables for each specific hazard like Earthquake, Flood, etc.? Should I create tables for each source of data such as National, State, County and each City from which I obtain data? What I want to be able to do is easily determine which updates to which data need to happen when, which data came from which source, what data is available for each specific hazard and what datasets overlap at the National, State, County and City levels. Here’s an example: I have data from the National Wetlands Inventory that is updated annually. I also have data from the County that is updated quarterly. The State and the Cities have no additional data. I need to be able to know, for 50 different hazards or issues, the source of the data, how often it’s updated, a scheduled reminder when it’s time to update if possible, and a field with a hyperlink to the actual data or metadata. My reference book includes instructions on Creating a DB, Creating and Editing Tables, Improving Table Design and Creating Relationships, Creating Forms, Creating Simple Queries, Creating Queries that filter and Summarize Data and Creating Reports. I think it’s a beginner type book – well it must be, if I’m able to sort of understand it. But I’m hoping someone who understands the whole picture can tell me what parts to build and where. I can follow directions, but can’t see the forest for the trees at this point. Any help would be extremely appreciated, and paid forward! |
#2
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
YOU understand the whole picture, much better than we can!
I agree that you're much better off to get your table structure down right before you move on. I strongly recommend against committing spreadsheet on Access (one table, tons of fields). You will always be revising that table and your queries, reports, forms, etc if you use that approach. What are the "things" about which you want to keep data? These are the "entities" in relational database design. In your situation, it sounds like [Hazards], [Sources] and [Datasets]. Now, how are they related ("relationship")? Can you have one [Hazard] (e.g., "earthquake") that shows up in many [Datasets]? (guessing yes). Can you have one [Source] that shows up in many [Datasets]? (again, guessing yes). Then your [Dataset] table would probably have a field for the [Hazard], a field for the [Source], and a field for the datetime that it was created. I'm not sure I understand if the frequency of update is being calculated from the other data, or is a piece of data in its own right. Good luck! Post back with further questions! That's how most of us learned. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "SandraRae2000" wrote in message ... Rookie user: Access 2007, Using "Picture Yourself Learning: Microsoft Access 2007" as reference guide. Need suggestions for how to structure what seems to me to be a very complicated DB. I want to build it the best way the first time, so I don't do a lot of work and not have it do what I need. Here's what I need to do: Track GIS datasets for about 50 natural and environmental hazards. The data itself does not need to be tracked. I do need to track its source (National, State, County or City data) and know its date of creation, and frequency of updates for example. I don't know whether to create one Table with tons of fields, and then try to figure out queries. Or should I create tables for each specific hazard like Earthquake, Flood, etc.? Should I create tables for each source of data such as National, State, County and each City from which I obtain data? What I want to be able to do is easily determine which updates to which data need to happen when, which data came from which source, what data is available for each specific hazard and what datasets overlap at the National, State, County and City levels. Here's an example: I have data from the National Wetlands Inventory that is updated annually. I also have data from the County that is updated quarterly. The State and the Cities have no additional data. I need to be able to know, for 50 different hazards or issues, the source of the data, how often it's updated, a scheduled reminder when it's time to update if possible, and a field with a hyperlink to the actual data or metadata. My reference book includes instructions on Creating a DB, Creating and Editing Tables, Improving Table Design and Creating Relationships, Creating Forms, Creating Simple Queries, Creating Queries that filter and Summarize Data and Creating Reports. I think it's a beginner type book - well it must be, if I'm able to sort of understand it. But I'm hoping someone who understands the whole picture can tell me what parts to build and where. I can follow directions, but can't see the forest for the trees at this point. Any help would be extremely appreciated, and paid forward! |
#3
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
Do what Jeff said.
Adding / reinforcing the fine points, start by pushing your Access book and the computer aside and decide on what the ENTITIES that you want to database are, and what the relationships are between them. You might even discuss that further here in this forum at this early important stage. My first guess is that main main entity is dtatsets, which are instances of receiving data, or, more specifically, instances of data being recorded or summarized by your sources. "Sources" will probably be another major table. If so, a good guess would be to put a SourceID autonumber PK field in your Sources table, and an integer "SourceID" FK field in your Datasets table and link those two fields. "Hazards" will probably be more of a "lookup table" to fill a "hazard" field in your Datasets table. Good luck! |
#4
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
I'm trying these things you suggested, but what is FK?
"Fred" wrote: Do what Jeff said. Adding / reinforcing the fine points, start by pushing your Access book and the computer aside and decide on what the ENTITIES that you want to database are, and what the relationships are between them. You might even discuss that further here in this forum at this early important stage. My first guess is that main main entity is dtatsets, which are instances of receiving data, or, more specifically, instances of data being recorded or summarized by your sources. "Sources" will probably be another major table. If so, a good guess would be to put a SourceID autonumber PK field in your Sources table, and an integer "SourceID" FK field in your Datasets table and link those two fields. "Hazards" will probably be more of a "lookup table" to fill a "hazard" field in your Datasets table. Good luck! |
#5
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
Hi Sandra,
In order to answer your last question: I know you're having some trouble on this first step for your medium complexity situation, but most people would, and that's what this design discussion group is for. Are you somewhat fluent on the the basics of Access design, like queries, forms etc? Will this dtatbase need to be used by other people who will know absolutely nothing about Access? If so, you will probably need to get substantial help from a developer. How much time to you have to get this up and running? PS: Don't use anybody who hits you up for money in these free advice forums. |
#6
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
FK is foreign key'and PK is primary key. You need to understand what these 2
extremely important concepts are. I suggest googling them. Basically, a PK is a column (or columns) in a table that uniquely identifies a row. A Fk is a reference to a PK from a related table. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "SandraRae2000" wrote: I'm trying these things you suggested, but what is FK? "Fred" wrote: Do what Jeff said. Adding / reinforcing the fine points, start by pushing your Access book and the computer aside and decide on what the ENTITIES that you want to database are, and what the relationships are between them. You might even discuss that further here in this forum at this early important stage. My first guess is that main main entity is dtatsets, which are instances of receiving data, or, more specifically, instances of data being recorded or summarized by your sources. "Sources" will probably be another major table. If so, a good guess would be to put a SourceID autonumber PK field in your Sources table, and an integer "SourceID" FK field in your Datasets table and link those two fields. "Hazards" will probably be more of a "lookup table" to fill a "hazard" field in your Datasets table. Good luck! |
#7
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
BTW that was just my first guess, not knowing the details of your
business/agency. But a good place to start, even if imperfect. FK = Foreign Key To answer this and your linking question. Let's say that you just entered a new Source, the city of Las Vegas, NV. You have an autonumber PK field named SourceID. When you entered that record, Access automatically gave it a source ID number of 1234. Now you have your first two datasets: December 2009 Plutonium leaks, and January 2010 Plutonium leaks. You want to enter these and record where they came from via a link. There are three steps to linking: One time: Put an integer (not autonumber) field called "IDNUmberofSourceofThisDataset" (or "SourceID" as I said before is also OK) into your Dataset table. This defines the intended use of this field as a "Foreign Key" ....FK is a USE, not a structure setting in Access like PK. One time: Open the relationships window and draw a line between this field and the "SourceID" field in your source table. When adding those two DataSet Record, write "1234" in your "IDNUmberofSourceofThisDataset" field. Later this loading of the field and and use of the links gets automated by forms. |
#9
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
"Steve" schreef in bericht ... If the responses you have gotten are all greek to you, I would like to offer tp build the database for you. I provide help with Access, Excel and Word applications for a small fee. My fee to help you would be very reasonable. Contact me and let's discuss how we can work together to create your database. Steve From now on I am going to hunt each and every post of you Steve Again.... It won't work but that 's no problem... it's a shame... -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!) Arno R |
#10
|
|||
|
|||
Rookie-building DB-want to get right the first time! Help?
"Steve" wrote in message
... If the responses you have gotten are all greek to you, I would like to offer tp build the database for you. I provide help with Access, Excel and Word applications for a small fee. My fee to help you would be very reasonable. Contact me and let's discuss how we can work together to create your database. Steve Still pimping your questionable services? Stevie is our own personal pet troll who is the only one who does not understand the concept of FREE peer to peer support! These newsgroups are provided by Microsoft for FREE peer to peer support. There are many highly qualified individuals who gladly help for free. Stevie is not one of them, but he is the only one who just does not get the idea of "FREE" support. He offers questionable results at unreasonable prices. If he was any good, the "thousands" of people he claims to have helped would be flooding him with work, but there appears to be a continuous drought and he needs to constantly grovel for work. John... Visio MVP |
|
Thread Tools | |
Display Modes | |
|
|