Another rookie with a desgin question

[_Kev]

Howdy all,

I just started my new analyst job and I've been handed an manually constructed Excel file that I'd like to port to Access. Let me see if I can state my scenario clearly so you can point me in the right direction(s).

There are 3 tables listing the following - monthly temperature, monthly humidity, and monthly dew point. The feilds are a Building header and the 12 months. The records are the ten buildings for which we're collecting data.

My source is a pair of log files -Temp and Humidity - for each building. The kicker is that these log file poll every 15 minutes.

So the plan to date is
1) Import the log files
2) Find the monthly average for each
3) Use these to find the Monthly dew point
4) Build a form to display all of this
5) See if there is a way to turn this into a batch process (and in turn a scheduled task)

After browsing this board I think I've got steps 1 and 2 down. I still need help with the rest though. Can you point me to any examples or post that I haven't seen?

I repost to this with more Q's after the lunch break.

Thanks

 

[_Kev]

4) Build a form to display all of this

I've been reading up on Junction tables and it seems like this is what I'd need to build the Month/Building table.

The problem is that I don't understand the instructions for setting the table up or how I will relate these to items. I think I need to set up a Month table and Building table and then join them, but I don't see where the relationship is.

I've been searching the forums under junction and many to many, but I still can't get my mind around it. Are there any demo's or a thread that those of you with more time on the board know will help?

 

[neileg]

I don't think you need a junction table. I presume that you have many readings for each building, but each reading only relates to one building. This is a one to many relationship and doesn't need a junction table to implement.

 

[_Kev]

Ok so I think I have even less of a clue now.

Does this mean I have to tie in the three averages to the building? If so I think I'd have to set up a relationship between the BuildingID field in the Building table and the MonthlyAvg field in each of the the three MonthlyAverage queries. So I'd have three Avgs pointing into Buildings.

I've messed with this in the relationship window and I don't see how this can be set up as a one to many. I also don't see how I'd take this relationship and use it to recreate the three Excel tables I described.

Apparently I'm still not seeing some fundamental thing with the relationships. What am I missing?

 

[neileg]

Sorry, I don't have enough information to give you a detailed answer.

However, I would point out that you don't create relationships in Access. The relationship is one that exists in the real world data. You use the tools in Access to model the real world. So if you have a one to many realtionship for real, that's what you need in Access.

I suggest you stop messing about in Access and work out on paper exactly what it is your trying to do. The Doc Man has posted in these forums about using post its and a pencil to work out the design. He's spot on there. Worth doing a search to track down his advice.

 

[The_Doc_Man]

Thanks for the kind words, Neil.

Kev, here is my advice in a nutshell. (But you don't have to shell out any money to this particular nut...)

There are a couple of rules to consider in any project of this sort. They are so simple that sometimes you give yourself the V8 slap on the forehead when you see them - but when you are deep in the trees, you can't always see the forest. Been there, done that, wore out the T-shirt.

First, ask yourself what you want to see. (You've stated this but indulge me, I'm going somewhere.)

Next, ask "what do I need in order to see this?" For instance, if you want to see an average humidity over time then you have to have time and humidity readings. AND... that little word "average" jumps out, too. Average over what? Multiple buildings? Then you need individual readings that identify buildings. Average over an hour? Then you need a way to form the hourly averages before you report/print/graph your hourly averages.

Now, here is where it gets hinky. Drop yourself back in time pre-1950. Can you do this by hand? Never mind that it is labor intensive, you don't have to do it often. But CAN YOU DO IT? (With the data you've got or that you are going to get?) Here is where the "Old Programmer's Rules" kick in.

1. "No computer will ever tell you anything someone didn't tell it first." So if you want something, you need a data source for that something. That "do it by hand" step will highlight whether you know enough to proceed.

2. "If you can't reach the goal on paper, you won't reach it in Access." That is, if you don't understand the required operation well enough to do it by hand, you are going to have one helluva time translating the hand operation to an Access operation.

If you can do it by hand and have satisfied yourself that you have the data you need, you are good to go to the next step - actually laying this out in Access. But even here, you still have pitfalls.

OK, purists out there, I'm about to be a bit heretical...

You really want to normalize this database long-term. But it is perfectly OK to putter about in a less than perfectly normalized database to try to get the formats right and try to get the queries working.

OK, purists, you can turn up the volume again...

Normalization isn't only for sissies. It also is necessary for practical databases too, because normalization saves you lots of space and a goodly amount of data entry time for updates. I see all sorts of issues here, and part of this includes that you will probably have data that is inherently NOT normalized on initial acquisition. Ideally, you should have a table of base observations that looks like this (but pick your favorite field names)...

tblBaseObs
WhenSeen, date/time, time and date of observation
WhereSeen, integer building and other ID, place where observation was taken
TempThere, single or double, temperature observation
HumidThere, single or double, humidity observation
...
anything else you need to record from that location

The keys for this are not only WHEN but WHERE an observation was taken. So you have a compound primary key.

Now, one more thing to learn if you haven't already figured this out. Queries are your friends. You can use queries to group time by the hour, day, week, month, or year and take time-based averages. You can use queries to isolate readings for single buildings or to compute average readings across multiple buildings. You can use queries to sort, group, average, or isolate just about anything in the data sets. Get the base table right and use queries anytime you want some unusual view of things.

 

[pbaldy]

Great post, Doc Man!

 

[Bratlien]

Excellent advice!