Table structure dilemas

[Garn77]

Hi

I am having trouble conceptualising how to pull this together.

Firstly I have data that comes in each month from various locations. At the intial stage all I want to do is enter the data for each location which consists of collection and reporting dates and a series of fields, some of which are common to all locations and some of which vary. From this a report will be produced each month for each site illustrating the results, easy.

The trouble is next. I want to have a query in the above process that flags data if it exceeds a nominated level. For each location and each of the series of fields this will vary??

Secondly once this has been achieved, I want to pull the data together in 3 ways as a review each quarter. Firstly a quarter review of the data from each site individally, including highlighting of data that has exceeded the nominated levels.Second, grouping some of the sites together on a form, possibly with tabs that again reviews last three months. Thirdly, I want to be able to review all of the various fields individually to see if they have exceeded nominated levels at at which locations for the quarter.

This will then need to be worked into reports which ideally have the capacity to review against last quarters and same quarters from previous years.

I know this is a bit vague but just looking for tips on how to overall structure something like this, not detailed as such

Cheers

 

[The_Doc_Man]

No one here will be able to answer your question better than you yourself will, once you step back and take a hard look at your problem. What we can do is tell you how to take that look.

To begin with, remember these "old programmer's rules" - told to you by an old programmer. (Me!)

1. The computer can't tell you anything you didn't tell it first. The computer can't know anything you didn't know first. The computer can't spew forth anything you didn't enter first.

2. The computer can't do for you in bits & bytes anything you couldn't have first done in pencil and paper.

3. You can shoot from the hip if you have to, but stopping to examine the target and take aim usually produces a better result.

(No, these were not intended as vague generalities.)

The "right" way to look is to step back and define the elements of your problem. Your elements will include "observables" (data sources) and "reportables" (data elements you seek to report). So to do this, get yourself a room with a white-board and some dry-erase markers (or use a big easel pad and any suitable markers). Invest in a couple of packs of sticky note pads. OK, now look at the problem elements.

You identify what you will get from your data sources (counting yourself). Make a list. Identify the "entities" - because each entity will be represented in your database by a table. Identify the processing required to take what you get and convert it to what you want. For reports, this might resolve to queries that contain your desired information plus the report template that draws from those queries. For more active processing, you might be looking at action queries - update, insert, delete.

OK, when you have your entities (tables) identified, you need to look at each one to identify what it should contain. (Here is where knowledge of the concept of NORMALIZATION becomes important.) Thinking about what you want to see in the report, define a list of what data elements you need in order to support that result. (See my rule #1 above.) If the data elements to support that result aren't present, see what you need to do to get that information.

You talk about having to show something for various calendar quarters. OK, this implies that your entities include a date field of some kind. Without it, you can't separate anything by quarters. (This is a practical example of my rule #1 in action.)

For each table you define, use the sticky notes to represent records in the table so that you can visually/mechanically step through the process of how you would compile the report.

Where am I going with this? You will have to solve your problem. You are unlikely to get us to do more than give advice in general (such as this post) or with regard to specific questions. But YOU are the one who knows the ins and outs of your problem best. (If this isn't true, see my rule #3.) Once you know it well enough to diagram it, you can put down notes to paper (see my rule #2). Once you can do it on paper, you know it well enough to start designing your application.