Using access as a historical tracker

[John Beeler]

I am a graduate student writing a thesis on a space in Indianapolis
called the Circle.

I'm trying to figure something out that would really help me out as I
write this thing.

I would like to be able to enter in a structure, the range of dates it
existed, and the lot number (there are 19 lots around the Circle, but
I'm planning on technically counting the center Circle itself as a lot
- so 20). I would enter something like:
English Opera House; September 1880 - December 1916; Lots 17, 18, 19

Then, later one be able to type in a date and have Access show me the
entire composition of the Circle.

I've also done some elementary Access database work before, for both
business and education purposes, so I know this is possible. The trick
is working with that range of dates.

Can anyone point me in the right direction?

I should state that I know that I could easily hire an access dev to do
this, but unless I can pay in ramon noodles (or historical knowledge!)
I can't contract one. =)

Thanks for any assistance,
John Beeler

[pietlinden]

Okay, break it down into simple sentences to describe the
relationships. then figure out how the pieces (nouns) are related.

can One structure occupy one or more lots during it's lifetime?
Each lot can contain one or more structures over time?

Sounds a bit like
CREATE TABLE Structure(
StructID LONG PRIMARY KEY,
StructName TEXT(50) NOT NULL);
CREATE TABLE LOT(
LotID LONG PRIMARY KEY,
.....);

CREATE TABLE OccupiesLot(
StructureID LONG,
LotID LONG,
BuildDate DATE,
DemolishDate DATE,
PRIMARY KEY(StructureID, LotID, BuildDate),
FOREIGN KEY (StructureID) REFERENCES Structure(StructureID),
(LotID) REFERENCES Lot(LotID));

This is a start - it may be different based on your rules - can two
structures share the same lot at the same time? (the alternative being
one is demolished and another built in its place?)

[Alan Webb]

John Beeler,
These days I wash dishes for a meal, so working for Ramen isn't far off the
mark. My personal favorite variation is red miso, home-made chicken stock,
diced chicken breast, and diced green onions. But enough about my new
career.
The entities you mention are STRUCTURE, LOT, and DATE. With DATE there are
quite a few built in functions to manipulate dates, do calculation on them,
parse them, format them, etc. I happen to like Ralph Kimball's suggestion
that if dates & times are a major player in a database design it makes sense
to build a table with all the dates and all the elements of dates that might
be used to summarize the data--days, weeks, months, quarters, years, and
whatever all stored in a table as an end result. For STRUCTURE & LOT you
have to ask, "What is it about structures and lots I want to report on or
analyze?" This will help you work out a list of attributes (fields or
columns in the entity (table)) that you need. The last question is whether
there are numeric measures you want to analyze. Typically there is at least
a column in the fact table with the number 1 stored in it so that counting
is a matter of summing this column. I couldn't tell you why but it seems
that sums are faster than counts. Fact table? Yup. This is a table with a
single column each containing a value that matches a value in the STRUCTURE,
LOT & DATE tables which uniquely identify each structure, lot & date. This
way you can look at structures by lot & date, lot by structure & date, date
by structure & lot, etc.
Someone else posted sample SQL to build the database. I haven't done so
because that's too much like consulting and I get paid for that. Also, this
group is educational and part of being a good teacher is to give enough
material to get a student started but not enough that the job is done. It's
up to the student to finish what a teacher starts in an assignment. I've
given you enough that you could work out on paper what your tables will be
and what fields each table will have. Now it's up to you to do the rest.
I'll follow this thread and answer any more questions you have.
"John Beeler" <jo********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I would like to be able to enter in a structure, the range of dates it
existed, and the lot number (there are 19 lots around the Circle, but
I'm planning on technically counting the center Circle itself as a lot
- so 20). I would enter something like:
English Opera House; September 1880 - December 1916; Lots 17, 18, 19

Then, later one be able to type in a date and have Access show me the
entire composition of the Circle.

[John Beeler]

Great. Thanks for the help guys. I managed to piece one together that
does the trick. Feel free to comment:

http://portfolio.iu.edu/jwbeeler/thecircle.mdb

Now I'm working on trying to generate a report that places each lot in
the appropriate quadrant (NW, NE, SE, SW, Center). I'm going along the
line of thinking that I'll need a subreport for each quadrant. I think
I'll figure it out, but if there's a more elegant way of doing it I
won't stop you from telling me.

Again, many thanks for the help.

John

[Alan Webb]

John,
It will probably be easier to choose the smallest geographic unit you want
to report on. If it's not lot, but quadrant, then build your schema around
quadrant instead of lot. Buildings will occupy a number of quadrants, then.
For reporting purposes your geographic dimension--LOT was what I suggested
will need columns that include the hierarchy in them. So, we could do
something like:
QUADRANT, LOT, BLOCK, DISTRICT, CITY, COUNTY, STATE, COUNTRY, CONTINENT in
one table as seperate columns. This way you can pretty easily examine your
data in terms of any level in the hierarchy by adding or removing columns
from this table.

"John Beeler" <jo********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Great. Thanks for the help guys. I managed to piece one together that
does the trick. Feel free to comment:

http://portfolio.iu.edu/jwbeeler/thecircle.mdb

Now I'm working on trying to generate a report that places each lot in
the appropriate quadrant (NW, NE, SE, SW, Center). I'm going along the
line of thinking that I'll need a subreport for each quadrant. I think
I'll figure it out, but if there's a more elegant way of doing it I
won't stop you from telling me.

Again, many thanks for the help.

John

[John Beeler]

Hmm. The smallest unit is in fact lot. There are less than 20 of
them, and I'm just studying this particular place in Indianapolis so I
don't need more information than lot.

What I am trying to do in the report is present it so that I can glance
at it and know that Christ Church was directly beside, say, Marion Fire
House in 1837. Hence the quadrants. It's not exactly what I want -
with the report I'd like to be able to place each structure in the
exact lot - but I can settle for merely putting one subreport in each
of the four quadrants of the Circle.

[pietlinden]

so, if you have it, automate Visio. Build your circle with all the
lots and then drop the buildings in or rename them?

[Alan Webb]

John,
Sometimes a bit of abstraction is necessary to give a client what they want.
The smallest unit may in fact be lot but if the client (you) wants to report
on a smaller unit then the database has to track whatever smaller unit is
being reported on as well as the client's stated smallest unit of lot. The
data has to be there to be able to analyze it. So this is why I suggested
increasing the grain-of-fact to a greater level of detail. If you were my
client I'd probably go ahead and build the database to support locating a
building by quadrant (It's only an additional column in one table if you
build it similar to what I suggested.) The additional disk space
requirements would be minimal and the cheapest time to do it is at this
stage when things are still being hashed out in a prototype.
So my workaround would be to include quadrants in my schema and make sure I
had a table which mapped the hierarchy of quadrant, lot, neighborhood, etc.
You've piqued my interest with this one. I hope you are able to put
something together that helps.
"John Beeler" <jo********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Hmm. The smallest unit is in fact lot. There are less than 20 of
them, and I'm just studying this particular place in Indianapolis so I
don't need more information than lot.

What I am trying to do in the report is present it so that I can glance
at it and know that Christ Church was directly beside, say, Marion Fire
House in 1837. Hence the quadrants. It's not exactly what I want -
with the report I'd like to be able to place each structure in the
exact lot - but I can settle for merely putting one subreport in each
of the four quadrants of the Circle.

[Alan Webb]

Piet Linden,
Clever. I thought of something similar using Map Point. I'd still want the
database as part of the solution, though.
<pi********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

so, if you have it, automate Visio. Build your circle with all the
lots and then drop the buildings in or rename them?