Need to un-Normalized

[Anthony Fontana]

I am not sure if this question has been asked, or even if this is the correct
forum, but I'll have a go at it anyway.

I understand the benefits of normalizing my information when I create my
tables - separate tables and all properly linked.

Yet, when I report or query on the normalized data, I must un-Normalize the
data and put it into one big unnormalized table.

Why are databases written this way. If I go to the effort of normalizing my
data, shouldn't I be able to report on them that way?

 

[Bernard Peek]

I am not sure if this question has been asked, or even if this is the correct
forum, but I'll have a go at it anyway.

I understand the benefits of normalizing my information when I create my
tables - separate tables and all properly linked.

Yet, when I report or query on the normalized data, I must un-Normalize the
data and put it into one big unnormalized table.

Normally when you report data from your systems you create a view rather
than a new table.

Why are databases written this way. If I go to the effort of normalizing my
data, shouldn't I be able to report on them that way?

I don't understand why you think that there is a problem here.

 

[Flick]

I am not saying it is necessarily a problem. I can get what I want in rpts
through queries as well as functions like dcount, dsum, and others. It just
seems strange that the DBMS unnormalizes the system to report on it.

 

[Steve]

If you are putting all your data into one big unnormalized table using
queries to create a report, you are probably not designing your report
correctly. You probably need to report at least some of your data in a
report/subreport.

Steve
(e-mail address removed)

 

[Anthony Fontana]

I seem to have given the wrong impression. I am not creating one large table
to report on. I am using the querys and reports (with queries contained in
them) to design the reports in the normal way, as intended by Microsoft.

My understanding was that the denormalizing took place in the background
when the report is run. I was curious as to why, as I understand it, the
DBMS needs to (?) / does (?) create one table or view or whatever to report
on. That seems to de-normalize. Maybe I don't have a proper understanding
of what exactly happens when the report is actually run. I do not know how
to state this any other way or give an example without appearing to be some
ignoramus or a fool.

It's really not a big deal. Just curious that's all.

 

[Chris O'C via AccessMonster.com]

When you run a report or query, Jet *displays* the data in the way your query
or report asked for it, but the data and its storage arrangement aren't
changed.

It's like if you have $1.26 in your pocket. Place the dollar on the table,
the quarter to the left and the penny to the right of the dollar. That's $1.
26. Move the quarter to the right of the dollar bill. It's still $1.26, no
matter how you choose to display the money.

Display properties may make the data *look* different but they don't really
change the underlying data.

Chris

 

[Bernard Peek]

I seem to have given the wrong impression. I am not creating one large table
to report on. I am using the querys and reports (with queries contained in
them) to design the reports in the normal way, as intended by Microsoft.

My understanding was that the denormalizing took place in the background
when the report is run.

I see what you mean. When you create a vie based on link tables the view
normally contains repeating elements where there is a 1:many
relationship between the parent and child tables.

I was curious as to why, as I understand it, the
DBMS needs to (?) / does (?) create one table or view or whatever to report
on. That seems to de-normalize.

Not really, because the data is only going to be stored temporarily.
It's even possible to avoid the appearance of denormalisation if you
structure your reports in certain ways. On the other hand you could run
a make-table query that really would create denormalised data, and there
are circumstances where that might be the right thing to do.

Maybe I don't have a proper understanding
of what exactly happens when the report is actually run. I do not know how
to state this any other way or give an example without appearing to be some
ignoramus or a fool.

It's really not a big deal. Just curious that's all.

Denormalisation isn't something to be frightened of. There are certain
limited circumstances when you might deliberately choose to store your
data in a denormalised form. It's generally done to make some database
operations run faster. The important thing to remember is that there is
always a price to pay for doing it and it's always risky.

 

[Steve]

An ignoramus/fool is one who does not ask questions. You asked the question
so you are NOT one of those!!!

Do you know what a subreport is? Do you use them?

Steve