Were to begin ... is this even possible?

[awsmitty]

First, let me stress the question posed in the subject line ... is the
following task even possible? I am not asking you guys to do this for me,
but I do need some help!

I am a dispatcher in a men's homeless shelter. I, along with three others,
take in donations over the phone, for example, Joe Citizen @ 1234 Park Ave,
My Town, OH, 34567 wants to donate a TV. I take this information down and
late that night I make up what we call a ticket (currently all of this is
done by hand ! ). This ticket also contains a map grid for the drivers to
use the following day to locate Joe and drive to Joe's house. The ticket
also serves as a tax receipt for Joe.

Here's were I would like to employ access, looking up Joe's grid number from
a database of addresses (the data base of addresses, zip codes, etc which
then correlates to the proper grid, is already done, in excel). We also need
to store Joe's receipt in case the IRS shows up questioning Joe's donation.
Access could also printing out the ticket the following morning for the
driver. I should also note that there are four truck routes, drivers, so,
access would also put the ticket on the correct truck.

The first question would be ... is this even possible in access?

There are several things to consider. One, for example, there are hundreds
of duplicate entries in this excel spreadsheet and this database is huge, at
least in my way of thinking 12+meg. Going back to the example above, 1234
Park Ave is in zip code 34567, and grid say 13A, but ... the 2000 Park Ave is
in zip code 34568, and grid 13B, and so on. A particular zip code has dozens
of streets; each grid has several streets, etc. Also, we cover several
counties and dozens of cities, hence, there are several Park Ave's

The next question is, were to begin. I have made one other posting here
about a month ago, and the replies were helpful and entertaining. I have
also perused the Internet and found more. But I have not found anything that
does something like this. I have thought about buying a book, say, "Access
for Dummies" or "Video Professor" (LOL), and maybe I should! Is manipulating
the above database (converting it to access) the place to start, then tring
to create a data entry form for the dispatchers to use while Joe is on the
phone. In other words, I envision the dispatcher entering the address, and
once done, immediately the grid, day of pickup, and truck are determined.

If some of you could just point me in the right direction, that would be
helpful


Thank you,

awsmitty


P.S. Very sorry to be so long winded

 

[John W. Vinson]

First, let me stress the question posed in the subject line ... is the
following task even possible? I am not asking you guys to do this for me,
but I do need some help!

I am a dispatcher in a men's homeless shelter. I, along with three others,
take in donations over the phone, for example, Joe Citizen @ 1234 Park Ave,
My Town, OH, 34567 wants to donate a TV. I take this information down and
late that night I make up what we call a ticket (currently all of this is
done by hand ! ). This ticket also contains a map grid for the drivers to
use the following day to locate Joe and drive to Joe's house. The ticket
also serves as a tax receipt for Joe.

Here's were I would like to employ access, looking up Joe's grid number from
a database of addresses (the data base of addresses, zip codes, etc which
then correlates to the proper grid, is already done, in excel). We also need
to store Joe's receipt in case the IRS shows up questioning Joe's donation.
Access could also printing out the ticket the following morning for the
driver. I should also note that there are four truck routes, drivers, so,
access would also put the ticket on the correct truck.

The first question would be ... is this even possible in access?

Seems very much possible. Not trivial, but not all that hard either. It could
even include a link to Microsoft MapPoint to dynamically display a street map
with a pushpin at the address, and a route calculator (a fair bit more work of
course!!!)

There are several things to consider. One, for example, there are hundreds
of duplicate entries in this excel spreadsheet and this database is huge, at
least in my way of thinking 12+meg. Going back to the example above, 1234
Park Ave is in zip code 34567, and grid say 13A, but ... the 2000 Park Ave is
in zip code 34568, and grid 13B, and so on. A particular zip code has dozens
of streets; each grid has several streets, etc. Also, we cover several
counties and dozens of cities, hence, there are several Park Ave's

Not a problem, and a place where Access will be much more flexible and
powerful than Excel; you'll have a table of Zips, a table of Gridblocks, a
table of Streets, and a table of Addresses, with appropriate relationships
between them.

Note that 12Mbyte is *tiny*, not huge - 1500MByte is getting too big for
comfort (meaning that you need to move to SQL Server, where 10 Terabytes is
getting a bit large).

The next question is, were to begin. I have made one other posting here
about a month ago, and the replies were helpful and entertaining. I have
also perused the Internet and found more. But I have not found anything that
does something like this. I have thought about buying a book, say, "Access
for Dummies" or "Video Professor" (LOL), and maybe I should! Is manipulating
the above database (converting it to access) the place to start, then tring
to create a data entry form for the dispatchers to use while Joe is on the
phone. In other words, I envision the dispatcher entering the address, and
once done, immediately the grid, day of pickup, and truck are determined.

Your existing spreadsheet will be a good source of the *raw data* to fill your
tables, but it will, I fear, be a very bad place to begin the logical design
of the tables and their relationships. A good spreadsheet can be an utterly
dreadful relational database design (and vice versa, of course).

You'll be far better off learning about the concepts of normalization and
relational design, creating a new, empty database (with multiple tables), and
running multiple Append queries to migrate data from your existing spreadsheet
into the new tables.

If some of you could just point me in the right direction, that would be
helpful

Here are some resources and links to more which might give you a hand. There
is in fact an Access for Dummies book, and it's good for what it does - but
what it does is explain how to USE an existing database, far more than the
much more complex task of designing the relational structure of a new one!
John Viescas' _Access <version> Inside Out_ is a much more thorough book, and
there are many other good ones.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

And... there's an active community on these newsgroups who will be glad to
help you up the rather steep and rocky learning slope of Access!

 

[awsmitty]

--
awsmitty

Seems very much possible. Not trivial, but not all that hard either. It could
even include a link to Microsoft MapPoint to dynamically display a street map
with a pushpin at the address, and a route calculator (a fair bit more work of
course!!!)


Not a problem, and a place where Access will be much more flexible and
powerful than Excel; you'll have a table of Zips, a table of Gridblocks, a
table of Streets, and a table of Addresses, with appropriate relationships
between them.

Note that 12Mbyte is *tiny*, not huge - 1500MByte is getting too big for
comfort (meaning that you need to move to SQL Server, where 10 Terabytes is
getting a bit large).


Your existing spreadsheet will be a good source of the *raw data* to fill your
tables, but it will, I fear, be a very bad place to begin the logical design
of the tables and their relationships. A good spreadsheet can be an utterly
dreadful relational database design (and vice versa, of course).

You'll be far better off learning about the concepts of normalization and
relational design, creating a new, empty database (with multiple tables), and
running multiple Append queries to migrate data from your existing spreadsheet
into the new tables.


Here are some resources and links to more which might give you a hand. There
is in fact an Access for Dummies book, and it's good for what it does - but
what it does is explain how to USE an existing database, far more than the
much more complex task of designing the relational structure of a new one!
John Viescas' _Access <version> Inside Out_ is a much more thorough book, and
there are many other good ones.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

And... there's an active community on these newsgroups who will be glad to
help you up the rather steep and rocky learning slope of Access!

John,

Many, many thanks

Let me get started, and think about this.

So, you think it would be better to break the data up, into its own fields,
as one table, e.g., street name ("Table 1"), hundred block ("Table 2"), zip
code ("Table 3"), grid number ("Table 4"), etc? I was thinking that all of
it would be in one table. That way all of the data, say 1234 Park Ave, right
next to it would be the zip, and next to that the grid, etc

Let me know what you think

Allan

 

[Steve]

Hello Allan,

You have a long and rough road to travel to get the database you want to
work. I can ease your pain. I provide help with Access, Excel and Word
applications for a very reasonable fee. I could work with you to create the
database you want and work with you to get it up and running. You can be
assured you would be well satisfied and very pleased with my fee. If
interested, contact me.

Steve
(e-mail address removed)

 

[John W. Vinson]

John,

Many, many thanks

Let me get started, and think about this.

So, you think it would be better to break the data up, into its own fields,
as one table, e.g., street name ("Table 1"), hundred block ("Table 2"), zip
code ("Table 3"), grid number ("Table 4"), etc? I was thinking that all of
it would be in one table. That way all of the data, say 1234 Park Ave, right
next to it would be the zip, and next to that the grid, etc

No. Sorry, that's not at ALL what I mean!!!!

You need to identify the logical relationships between the fields in your
data. The classic way to do this is to identify the "Entities" - real-life
things, persons, or events - of importance to your application. Each kind of
entity gets its own table; each table has as many Fields as are needed to
record the "Attributes" of the entity - discrete, nonrepeating chunks of
information that you need to record.

An Address is an important entity in your application. An Address has a
variety of attributes - the AddressNo (31140), Street (Circle Drive), City
(Parma), State (Idaho), Postcode (or Zip, if you call it that) (31140).

If you're tracking streets (as streets, i.e. you'll do something different
with Maple Street than you do with Wells Avenue), you'll want a table of
streets; you may well want a table of streets anyway, just for convenience in
data entry and to prevent misspellings.

I have no idea how your "grid" is set up, but I'd guess that one attribute of
an address is its grid entry.

Read some of the resources, particularly Crystal's tutorial to get an
overview.

 

[awsmitty]

John,

Many many thanks

So, you think it would be better to list each field in a seperate table,
e.g., Street Name as one table, Zip Code as one table, Grid as one table. I
was thinking it all should be one table so that the name, hundred block, zip
code, grid, etc., would all be side by side next to each other

 

[Gina Whipp]

awsmitty,

Along with the FREE resources provided by John, here's some FREE Data Models
that might help you sort out table set-up...

http://www.databasedev.co.uk/data_models.html

http://allenbrowne.com/AppHuman.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

Hello Allan,

You have a long and rough road to travel to get the database you want to
work. I can ease your pain. I provide help with Access, Excel and Word
applications for a very reasonable fee. I could work with you to create
the database you want and work with you to get it up and running. You can
be assured you would be well satisfied and very pleased with my fee. If
interested, contact me.

Steve
(e-mail address removed)

Just as expected, the US celebrates Thanksgiving and the turkey returns.

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.

Stevie is our local troll and is the only one who enjoys harrassing posters
for money.

John... Visio MVP

 

[awsmitty]

Let me say this about Steve

He must have overlooked the part about me being in a homeless shelter
(basically this means I’m broke ! ). I am doing this pro bono. I can't very
well pay someone to do this because I am not ever going to get paid. It's
just that this archaic way they have been doing this has got to stop.
Everything being done by hand, spiral notebooks, three ring binders, looking
up map grids, carbon copies, (when is the last time anyone has used carbon
copies, lol, not only that but these things cost 0.80¢ a piece), etc

Rumor has it that another shelter, much larger and a lot better off
financially than this one, has such a system. Whether it is based on access,
dbase III, just what, I don’t know. I would like to just look at it, just
look at it. It was probably developed by some one like me, who had to stay
there do to unfortunate and difficult personal circumstances. Now, this
other shelter does have the money to possibly pay for outside help. I also
know they do this from time to time. But the shelter I am in right now, like
me, is going broke too. If I can get this done, even if I have to work on
this after I leave, it would be a big help to them, and I don’t see anyone
coming in here in the near or distant future that would sit down and do this.

Next, Gina, I appreciate your links. I also want to say that I am going to
the library to see if I can check out a book on data structures in access. I
saw Access for Dummies at an electronics store, but it was for 2007, it was
also ~$30.00. We are using 2003. The version of Access I feel doesn’t
matter right now. Right now the problem is structuring the database in my
head, or on paper. The next phase will be some type of interface so that the
user can enter the street and zip, and boom, the grid and truck automatically
appear.

So again sorry Steve, aber ich habe kein geld, and thanks again to all of
you who come with help and guidance.

 

[John W. Vinson]

Next, Gina, I appreciate your links. I also want to say that I am going to
the library to see if I can check out a book on data structures in access. I
saw Access for Dummies at an electronics store, but it was for 2007, it was
also ~$30.00. We are using 2003. The version of Access I feel doesn’t
matter right now. Right now the problem is structuring the database in my
head, or on paper. The next phase will be some type of interface so that the
user can enter the street and zip, and boom, the grid and truck automatically
appear.

Drop me an email at jvinson <at> wysard of info <dot> com. I may be able to
help you get started, and of course there'll be no fee.

 

[Philip Herlihy]

Let me say this about Steve

He must have overlooked the part about me being in a homeless shelter
(basically this means I’m broke ! ). I am doing this pro bono. I can't
very
well pay someone to do this because I am not ever going to get paid. It's
just that this archaic way they have been doing this has got to stop.
Everything being done by hand, spiral notebooks, three ring binders,
looking
up map grids, carbon copies, (when is the last time anyone has used carbon
copies, lol, not only that but these things cost 0.80¢ a piece), etc

Rumor has it that another shelter, much larger and a lot better off
financially than this one, has such a system. Whether it is based on
access,
dbase III, just what, I don’t know. I would like to just look at it, just
look at it. It was probably developed by some one like me, who had to
stay
there do to unfortunate and difficult personal circumstances. Now, this
other shelter does have the money to possibly pay for outside help. I
also
know they do this from time to time. But the shelter I am in right now,
like
me, is going broke too. If I can get this done, even if I have to work
on
this after I leave, it would be a big help to them, and I don’t see anyone
coming in here in the near or distant future that would sit down and do
this.

Next, Gina, I appreciate your links. I also want to say that I am going
to
the library to see if I can check out a book on data structures in access.
I
saw Access for Dummies at an electronics store, but it was for 2007, it
was
also ~$30.00. We are using 2003. The version of Access I feel doesn’t
matter right now. Right now the problem is structuring the database in my
head, or on paper. The next phase will be some type of interface so that
the
user can enter the street and zip, and boom, the grid and truck
automatically
appear.

So again sorry Steve, aber ich habe kein geld, and thanks again to all of
you who come with help and guidance.

If you'd like to identify a book on Amazon and send me (philipherlihy) a
mailing address I'll be glad to buy it for you. Be sure to pick one that
covers development of databases, not use how to use the menus on an existing
one. I have a copy of this:
http://bit.ly/AccessBible
...., for example, and it does cover everything you need. It's a weighty
book, though, and the way it's written its best read cover-to-cover!
Nevertheless, learning new skills and doing something useful with them can
only help improve a body's situation.
One thing - if you're going to help the shelter, think of "succession
planning". Make the database as easy to use as possible, and document
everything pretty thoroughly. And above all, keep it as simple as possible.
Folk here will always help (without charging you for it).

You can mail me at googlemail with your choice and shipping address. Other
contributors might perhaps have suggestions?

Phil, London