By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

Access best database for this application?

P: n/a
Hi,
I am not a programmer and in the past have only created very simple, flat
file databases. Although I'm a newbie, I think I could probably figure out
basic Boolean logic and financial functions.

The database I want to create would import quite a few records -- maybe as
many as 100,000. I would be importing them from an Internet source (comma
delimited) to my desktop so that I could manipulate the data how I wanted.

Despite the large number of records, the answers I need to extract from the
data are very simple. I need to get answers like this:

"For all sales made between 1-1-2005 and 12-31-2005, and that were made in
Region A, [and a few other criteria], what was the median sales price?" Or,
"Give me a list of all Regions that meet the following criteria."

Given all this, would Access be the best database for me or is there a
better choice for my skill level and needs? The choice doesn't have to be
perfect, but my top priorities are reliability and ease-of-learning (in that
order).

Thank you.
Aug 30 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Spam Man,

1. importing records
This shouldn't be very hard. Depends on the structure of your
database. But creating import specifications for various imports should
take care of most of this. (well, hard to tell without seeing the data)

2. querying - as long as your tables are indexed well, then you
shouldn't have any problems querying.

3. "answers". You can create unfiltered reports that show whatever
information you want and then filter the report when you open it.

4. Median. I think there's code on the MS website for determining
median. Basically it's "order the records, get the middle number". At
any rate, that's been done before, so once you find the function,
you're home free.

"best" is a matter of opinion, but you can certainly do this in Access.
One thing I would definitely recommend is searching the Access NGs for
answers to your questions. There are over ten years worth of answers
here, so no doubt there's information you can use.

on top of that, you can always pump your data out to Excel or word
without much of a problem.

So far I see no reason why Access can't do this. Most of it is pretty
simple. About the hardest thing here is creating filters on the fly,
but you can do that with an unbound form. Search the NG or post more
questions. Not hard once you get the hang of a few concepts.

Of course learning a little VB won't hurt, but most of what you have
outlined shouldn't require it.

You've already solved one of the larger problems - a good handle on
exactly what you want to do, which is a very good start. I would guess
that the next step is to define what "things" you're trying to keep
track of and how they're related. When I did this in school, my [good]
instructor put it this way:

1. write down on a piece of paper the "story" of your database. If you
were going to do this with Northwind, it would be something like:

Northwind sells products to customers. For each sale, an invoice is
generated and each invoice detail represents a quantity of a single
item on that invoice. Each product can appear on more than one
invoice...

Of course, that's incomplete, but you get the picture (or the beginning
of it). If I remember right, normalization is covered really well in
Roger Jennings' book on Access. You might want to read te part on
normalization, because understanding it is critical to building solid
databases. (Sorry, Access isn't a program you can use out of the box
without reading the manual. If you don't design your database right,
you won't be able to get the information out that you want.)

Come to think of it, the Que book on Access might be money well spent.
Don't worry that it's _fat_. If you get your head around
normalization, you'll be well on your way. In my opinion, once you
have a well normalized database, the rest is fairly simple. On the
flip side, if it's not normalized right, getting simple answers is a
complete nightmare. So don't just pump huge amounts of data in your
database right off the bat. put in enough so that you can manually
verify that everything works as it should. (So start with probably 5
records per table, so you can do the validation... but I'm waaaay ahead
of myself...)

Hope that gets you started. Post back if you have more specific
questions.

Aug 30 '06 #2

P: n/a
....for the requirements you've stated Access could do all of them using just
its built in wizards ...it won't be pretty nor "normalized" but it will be
highly reliable and very easy to learn ...the "Get External Data" menu item
will help you select the csv import wizard, easily define the incoming data,
and then make a new table (or append to an existing one) ...once the data is
in the Access table, you can use the Query wizards to build the queries you
defined, tweaking the resulting queries to produce the results you need
....there are readily accessible examples in the help files to assist you in
defining the query criteria you need.

....there is no need for you to do more than that to produce the results you
defined ...those who develop professionally in Access can point out
literally thousands of things they would do instead starting with
"normalizing" the imported data, etc, etc ...and Access certainly has tools
that will assist you in such things as you learn to take advantage of its
myriad capabilities ...but the answer to your specific question is that
Access is ideally suited to do exactly what you need with nothing more
required of you than the skills you've already demonstrated in defining your
needs ...trust me on this one, I deal every day with people incapable of
defining what answers they want or need in a manner anywhere near as
specific as you did.

....hth

William Hindman

"No Spam Man" <bl**********@blah.comwrote in message
news:YM5Jg.2452$c07.1049@fed1read04...
Hi,
I am not a programmer and in the past have only created very simple, flat
file databases. Although I'm a newbie, I think I could probably figure
out
basic Boolean logic and financial functions.

The database I want to create would import quite a few records -- maybe as
many as 100,000. I would be importing them from an Internet source (comma
delimited) to my desktop so that I could manipulate the data how I wanted.

Despite the large number of records, the answers I need to extract from
the
data are very simple. I need to get answers like this:

"For all sales made between 1-1-2005 and 12-31-2005, and that were made in
Region A, [and a few other criteria], what was the median sales price?"
Or,
"Give me a list of all Regions that meet the following criteria."

Given all this, would Access be the best database for me or is there a
better choice for my skill level and needs? The choice doesn't have to be
perfect, but my top priorities are reliability and ease-of-learning (in
that
order).

Thank you.


Aug 30 '06 #3

P: n/a
Access can accomplish these simple tasks, but it is overkill.

It appears that you are dealing with a single, flat file (one table), and
there are other programs that handle these tasks (e.g. FileMaker).

However, this could be a good starting point in learning Access, which can
handle so much more.

Steven

"No Spam Man" <bl**********@blah.comwrote in message
news:YM5Jg.2452$c07.1049@fed1read04...
Hi,
I am not a programmer and in the past have only created very simple, flat
file databases. Although I'm a newbie, I think I could probably figure
out
basic Boolean logic and financial functions.

The database I want to create would import quite a few records -- maybe as
many as 100,000. I would be importing them from an Internet source (comma
delimited) to my desktop so that I could manipulate the data how I wanted.

Despite the large number of records, the answers I need to extract from
the
data are very simple. I need to get answers like this:

"For all sales made between 1-1-2005 and 12-31-2005, and that were made in
Region A, [and a few other criteria], what was the median sales price?"
Or,
"Give me a list of all Regions that meet the following criteria."

Given all this, would Access be the best database for me or is there a
better choice for my skill level and needs? The choice doesn't have to be
perfect, but my top priorities are reliability and ease-of-learning (in
that
order).

Thank you.


Aug 30 '06 #4

P: n/a

"No Spam Man" <bl**********@blah.comwrote in message
news:YM5Jg.2452$c07.1049@fed1read04...
Hi,
I am not a programmer and in the past have only created very simple, flat
file databases. Although I'm a newbie, I think I could probably figure
out
basic Boolean logic and financial functions.

The database I want to create would import quite a few records -- maybe as
many as 100,000. I would be importing them from an Internet source (comma
delimited) to my desktop so that I could manipulate the data how I wanted.

Despite the large number of records, the answers I need to extract from
the
data are very simple. I need to get answers like this:

"For all sales made between 1-1-2005 and 12-31-2005, and that were made in
Region A, [and a few other criteria], what was the median sales price?"
Or,
"Give me a list of all Regions that meet the following criteria."

Given all this, would Access be the best database for me or is there a
better choice for my skill level and needs? The choice doesn't have to be
perfect, but my top priorities are reliability and ease-of-learning (in
that
order).

Thank you.

I wanted to thank very much the three people who replied and gave such good
advice. I'm either going to get Access or Filemaker, but I think the
posters helped me clearly see that regardless of the tool I use, I need to
spend time on planning the database and carefully defining the answers I
want to get from it. Even though my needs are extremely simple, you've
helped me see more clearly that second-rate planning might produce a
second-rate database. I bet this is self-evident to all the experts on the
forum, but a newbie like me needed to hear that.
Thank you again.
Aug 31 '06 #5

P: n/a
Access was specifically designed for usage like yours. Excel might be
able to crunch the numbers a little more easily because you don't have
to create queries and such, but Excel has a 65,000 row limit. Where
Access can easily handle 100,000 records (on the desktop with only one
user). The only area where Access encounters problems is in the
multi-user environment - when you have a lot of data and multiple users
accessing the data from a server based mdb. But if your application is
on the desktop with only you using it, Access will process your data
almost instantly.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 31 '06 #6

P: n/a
....and on the other hand I have a number of clients with as many as twenty
users on fe/be mdbs with hundreds of thousands of records that process their
data "almost instantly".

William Hindman
"Rich P" <rp*****@aol.comwrote in message
news:44***********************@news.qwest.net...
Access was specifically designed for usage like yours. Excel might be
able to crunch the numbers a little more easily because you don't have
to create queries and such, but Excel has a 65,000 row limit. Where
Access can easily handle 100,000 records (on the desktop with only one
user). The only area where Access encounters problems is in the
multi-user environment - when you have a lot of data and multiple users
accessing the data from a server based mdb. But if your application is
on the desktop with only you using it, Access will process your data
almost instantly.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Aug 31 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.