How to eliminate records that are used to test a created database

[Liontamer]

Dear Tina, Douglas J. Steele and friends of this post:

The reason you didn't get my prior posts is because the service was not
working properly. I tried to send you both some responses, but apparently
the system did not save them for sending when the service returned. Oh well,
sometimes the good has to have some bad too. I'll attempt to respond again
here.

Let me start off by saying thank you to both of you.

I want to specifically state that I've only had an opportunity to learn from
the online training provided by Microsoft (MS) about Access. So far, I've
managed to study and learn from the first two modules. 1. Get familiar with
Access. and
2. Build your first database. And "boy o boy" am I ever excited. Yeap!
Cause from what I've learnt, stuff that I'm trying to do seems very possible
and benefitial.

Needless to say, I've got to juggle many things in my work day and
activities to both find the time and keep my learning process going, at
least, at a moderate pace.

Now, what I've got from the online training clearly indicates that the use
of Lookup Boxes is an option for a table. However, what I'm getting from you
folks is contradictory to MS teachings. Now, do keep in mind, that I realize
you're both trying to guide me in the right direction. But why would MS want
to do otherwise?

What I'm going to do now is comment on your prior posts. So, let's start
with Douglas J. Steele's original post:

Your application should always be split into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relations), even if it's only a single-user
application. In that way, you use a "test" back-end for testing, and a
"production" back-end once it's done.

Okay, look it may be helpful to you both to know this about what I'm using:
I am using MS XP Professional Media Center Edition, SP3.
I have MS Office Professional 2007. And in this suite is Access 2007.

When Douglas is talking about Front End and Back End, he is talking
programming talk on a level that more experienced software programmers will
more easily relate to.

I have, several years ago, worked in the computer industry as a regional
product and sales training specialist. So, I have a background in computers
and an understanding of software to some degree. But, that was from several
years ago. And, although, I understand the complexities of programming from
my days back at college, I do not in any way make any claim to understanding
programming skills in today's day and age.

Now getting back to Douglas's comment:
Front End and Back End are concepts which are new to me, but on some level I
kindof get what he's saying. But, let's keep in mind, the MS online training
does not stress this as a concern of importance to the end user who is
embarking on learning how to use the Access 2007 program.

I understand what Tables are designed for. They are to contain the data
from records. Some of those records may have variable but repetitive types
of comments. For example, if you're tracking different types of prospects.
One type may be interested in Product A while another may be interested in
Product B. However, suppose you were interested in knowing which item a
prospect was interested in. Then you certainly don't want to type in this
data for each an every prospect record. And it becomes a real benefit to use
a lookup box for that particular field of the record.

All I've done so far with my database is make two tables. One contains very
basic prospect identification information. The other sort of qualifies
different aspects of those potential prospects. That's it.

The point I'm at now, I am not using queries, forms, reports, macros and
modules. Will I consider these items in the future? Yes, to some degree.
But this database, which I'm creating is for my own exclusive purposes.
Nobody else will be using it. And so far, all I've done is break up my data
for various prospect records into two tables. One table is basic contact
info. The other table is for more qualifying types of concerns.

The only relationship between the two tables is the prospect names. Instead
of making one super long table with lots of fields, I broke it into two
tables. Hey, I'm learning. And "yes" even though this stuff takes quite a
bit of time to master, I'm having some fun learning how to fly this
spaceship. It's tough but, at least, I didn't hit any asteroids so far.

For you information, I have successfully related my two tables. And, I have
set referential integrity. That took a bit of figuring out, but I've got it
working now.

Am I planning on using queries in the future? Yes. But only to give me
reports which I feel I may need.

Am I planning on using forms? Well, I don't know right now. Since I'll be
the one entering the various record data into the tables. However, if I feel
the forms option will make my life easier while entering the data then "Yes"
I'll use a form. But, at this point, forms may not be necessary.

Regarding macros and modules, that's like using fulton topedos to me. I
have a basic idea what they do, but at this point--unless I encounter some
hostile aliens--I don't think I'll be using them.

When Douglas says: "In that way, you use a "test" back-end for testing, and a
"production" back-end once it's done." Again, I read this as programmer
talk. Actually, I do not know what he means when he writes: "use a "test"
back-end for testing". But, I think I understand what he means by saying: "a
"production" back-end once it's done." The production part is probably
relating to using your tables or queries to produce reports. Again,
programmer talk can be difficult to understand with my level of understanding.

Moving on to Douglas's comment about AutonNmbering:
However, it sounds as though you're trying to assign meaning to the value of
the AutoNumber fields. Don't. AutoNumbers exist for one purpose only: to
provide a (practically guaranteed) unique value that can be used as a
primary key. 346, 350, 351 serves that purpose just as well as 1, 2, 3. Note
that AutoNumber values aren't guaranteed not to have gaps in them (if you
start to work on a record and then change your mind, the value that would
have been assigned to that record is lost), and if you should use
replication, they will be changed to Random from Sequential, so you can't
even be guaranteed that they'll be positive.

I was not trying to assign a meaning to the AutoNumbering fields. But, it
is interesting news to me to learn that: "AutoNumbers exist for one purpose
only: to
provide a (practically guaranteed) unique value that can be used as a
primary key." Practically guaranteed, oh my goodness, and to think I
thought computers are logicial things. You know, number one comes before
number two, etc. I do get that a number is a number, however. And, so long
as they are mutually exclusive of each other then there isn't a problem.

When Douglas says: "if you start to work on a record and then change your
mind, the value that would have been assigned to that record is lost"--this
is a disappointment to me. For I would hope that if I chose to delete a
record from a table then whatever value is set for it's Primary ID field
would also be available again. But, after thinking about this, I think I can
understand why this does not happen. If it did happen then the Primary ID
fields of other records would also have to change. And that's probably why
the Primary ID field number is lost when you delete a record. I don't know
for sure, but that seems the reason.

The use of replication, at this point for me, is over my head. Quoting
Douglas: "they will be changed to Random from Sequential". Well, this
statement answers another question I had: Can I set the starting record
number in the Primary ID field? And, after reading Douglas's comment, I
realize I've answered my unasked future question. The answer must be "No"
because AutoNumber does what it want to do. If it's sequential then there's
still no guarantee that the sequence will not have gaps. If it's random then
there's no sequence. Yet, a number is a number, so who cares as long as each
record has a unque number different from the other records.

Now on to Tina's comments:
I have printed out the report you recommended: "Tiips to Get You Going.
Basic information that applies to all databases." Thank you. I need some
time to read this writing. However, at first glance, this information seems
to be geared for the programmer of a database. Again, the MS online training
modules is all the training I've had on using Access to date. I need for you
to keep this in mind. I believe I'm applying what they've taught in these
online training course properly. I'll have to get back at ya, regarding the
tips you've shared. But, again, I get the feeling these comments are for
those who actually create the software behind a database. I'm not sure. I
have to read it more carefully.

Tina's comment: "...if you're willing to invest the time and effort it
takes, you can become a skilled Access application developer, too. these
newsgroups and the many
resources on the internet can help you get there." Thank you. I am always
willing to learn new things. It's fun and exciting to learn new stuff which
applies to what I'm trying to do. If it allows me to be more effective at
what I do and help others in the long run then I'll be happy.

Now for a few other comments:
MS Access 2007 comes with Featured Online Templates. One of them is: Sales
Pipeline. However, I found my initial review of this template to be sort-of
something I wanted to do, but not more than say 40%. In other words, I just
didn't really like the set up completely. This is why I set out to learn the
first two training modules for Access 2007 by MS. I figured, I can learn and
I can create just what I feel I needed.

I haven't figured out how to get charts from some of the fields in my
tables, but I'll get to that later.

I haven't figured out how to make notes for a particular record that can
perhaps go beyond the memo capacity of 255 characters. And, I'm not too keen
on utilizing an attachment to my Word 2007 documents to do so for each and
every record. But I'm thinking I can just get a separate paperback notebook,
use my Prospect ID number to reference the pages, and write the notes in the
book by hand. This will allow me to conserve space on my computer's memory.
Although, I can probably figure out how to do this with a CD-R. I don't know
yet, how I'm going to work this out. But this is the direction I want to go
in. Of course, if I can do it all using my trusty PC (Oh, how I love my PC,
we've got this bond, you know. Don't get jealous Tina!) then this will be
fantastic. I even wonder about the MS program called Notes. But I can only
learn about one thing at a time.

You may also be wondering why I don't just use the Business Contact Manager
(BCM) for all my needs. Well, there's a good reason. I don't want to
clulter it up with tons of prospect records. I'd rather use the BCM for
accounts and priority prospects who've become clients.

Okay, now I've got to go and eat my Texas Chili which I've been makin' while
I've been working on this response to my new friends. And, as a gift for you
all, you may want to try this stuff. It's sooooooooo goooooooooooood! But,
like anything you've got to make it a few times to really get good at it.
Here's where you can get it: jardinefoods.com or 800-544-1880 It's the
Texas Chili works. Trust me, the smoke will be comin' out your ears, if you
like it hot!

I am the Liontamer!

 

[Arvin Meyer MVP]

Now getting back to Douglas's comment:
Front End and Back End are concepts which are new to me, but on some level
I
kindof get what he's saying. But, let's keep in mind, the MS online
training
does not stress this as a concern of importance to the end user who is
embarking on learning how to use the Access 2007 program.

Unfortunately, the Microsoft Online training is lacking in many areas, and
most of the people at Microsoft responsible for both creating Access and
especially the technical writing are not database developers. The technical
writers may not even be thoroughly familiar with Access.

Fortunately, the Microsoft MVPs are mostly database developers. Some of us
are also teachers and trainers, and all of us are thoroughly knowledgeable
in Access. When Doug Steele mentioned splitting the database, he perhaps
didn't stress enough the importance of doing so. An entire web page from
Access MVP is devoted to that:

http://www.granite.ab.ca/access/splitapp/overview.htm

I understand what Tables are designed for. They are to contain the data
from records. Some of those records may have variable but repetitive
types
of comments. For example, if you're tracking different types of
prospects.
One type may be interested in Product A while another may be interested in
Product B. However, suppose you were interested in knowing which item a
prospect was interested in. Then you certainly don't want to type in this
data for each an every prospect record. And it becomes a real benefit to
use
a lookup box for that particular field of the record.

Yes, however in almost all cases tables are solely for storing (not viewing)
records. Queries are for gathering records and forms are for entering and
viewing the data. Reports are for viewing and printing data. NEVER use
lookup fields in tables:

http://www.mvps.org/access/lookupfields.htm

There are lots of other concepts that you are touching on, and perhaps
others will comment. I'd like to suggest some excellent tutorials by Access
MVP Crystal Long:

http://www.accessmvp.com/Strive4Peace/Index.htm

and some training videos, also from Crystal:

http://www.accessmvp.com/Strive4Peace/Videos/Index.htm

for Access 2007 specific tutorials, try Access MVP Oli Stohr's website:

http://www.access-freak.com/tutorials.html

and for lots of code and answers to many questions, the 3 websites in my sig
below:

 

[Liontamer]

Wow, Arvin Meyer MVP, you rock! Forgive me for not answering this post with
a "Yes" to the question: "Did this post answer the question?" For I realize
more time is necessary for the study of this program. I've got more
questions. And I'd like to keep this discussion thread open.

Here's one such question:
I originally thought about the use of tables to store data about a prospect.
I wanted to have one table to store basic contact info about the prospect.
I wanted to have a second table to store select info and qualifications
about the same prospect.
I felt it would be a good idea to break this up into two tables. It would
reduce the horrizontal length of just one table. It will allow for easier on
screen real estate management while viewing a particular record.
Now I'm thinking I relate table two (the many side) to table one (the one
side). Why? Because I want to repeat the prospect_name field in table two,
without the need to type in the prospect name again at table two. However,
this is not as easy as it may seem. Because, when I go through the process
of adding an existing field to table two, I must reference the Primary Key
from table one (hiding it) and name the field Prospect_Name in the Lookup
Wizard. On the surface, this seems to be following the guidelines presented
in the MS training I've reviewed thusfar (see prior comments). However,
after reviewing some of the comments written by the MVP's, I am beginning to
understand why this is not quite such a good idea. For one thing, after
thinking about the effect of doing this, I can understand the length of the
lookup box can get quite long. That's not good. So, I am wondering (and
this is my quesion) how can I get the prospect_name field of table one
replicated onto table two without using a Lookup Box?

I'm thinking if I set up a one to one relationship then will it accomplish
this without a Lookup Box? But I don't know if this is gonna work properly.
For it seems, this situation may have to involve two different types of
fields. Than there indexing (without allowing duplicates for the foreign
field). So, this doesn't seem to be the answer which I seek. Recall, all I
wanna do is replicate the propect_name field from table one to table two.
I'll have to play with this situation a bit, perhaps do some research, in
order to figure out what to do.

There's good reasons for why I'd like to do this:
1. On screen real estate while viewing records will be easier.
2. The tables can contain data about each prospect_name which also can be
editted or updated as necessary. It seems most of the MVP's, who've been
nice enough to provide some assistance here, agree that table are to store
data. Even you Arvin say: "Tables are for storing, not viewing records."
BUT BUT BUT....

Since the tables have stored data and since any record in the table can be
reviewed and editted, it seems logical to just work with that as a fact. I
mean this: Say, I establish several records. A phone number changes for a
record. Well, I can just go back to the record and edit it. Or say, the
estimated close date for this record changes. Then, again, I can just go
bact to the record and edit it in the appropriate talbe. See, this is the
type of thing I set out to do originally.

And, if I want to run a query then I can just set one up. For example,
suppose I want to know which records are interested in Product A and are
ready to close in Aug '09. Then I can set up a query for that with boolean
operators to return the report on these records meeting the criteria I seek.

However, note that in one case all I'm doing is editting or updating a field
or fields in a record. The other case, I am actually asking Access to search
my database for records that satisfy a given set of criteria. In the latter
case, a query is best suited for this purpose. But in the former case, a
query is not really necessary. And that is what my original ideas for using
Access where based on.

Now, I'm learning from your comments Arvin that the MS training is not
really the best quality training. And that there're so much more to know
before I can succeed at getting my ideas for using Access to work properly.

Gee, I sure wish things were a bit easier. Just when I think I'm going down
a track and making progress towards my objective, that's when I find out that
more time is needed to learn something else. And my project stalls until
further notice.

I actually thought my originally ideas were sound and based on the
definitions which I learnt from the two MS training modules I studied.

Waiting now on a reply. Hope everyone enjoyed their 4th of July 09.

 

[Arvin Meyer MVP]

So, I am wondering (and
this is my quesion) how can I get the prospect_name field of table one
replicated onto table two without using a Lookup Box?

Use a query. In reality, that's all a Lookup is (I.e. an SQL select
statement) but it is exposed so that any developer (including yourself) can
easily see what's happening.

I'm thinking if I set up a one to one relationship then will it accomplish
this without a Lookup Box?

There is sometimes a good reason for a 1 to 1 relationship, but generally
they are neither required or adviseable. If you, for instance, have a table
of people and have specific information, like some are suppliers, and some
are customers, that is unique to each type of person, a 1 to 1 may be the
best choice. In that case there would be 3 tables. 1 for people, then 1 each
for suppliers and customers. The supplier table may have information like a
product line (say, for instance, insurance), that the customer table will
never require, so it is less efficient to include that field in the primary
table.

But I don't know if this is gonna work properly.
For it seems, this situation may have to involve two different types of
fields. Than there indexing (without allowing duplicates for the foreign
field). So, this doesn't seem to be the answer which I seek. Recall, all
I
wanna do is replicate the propect_name field from table one to table two.
I'll have to play with this situation a bit, perhaps do some research, in
order to figure out what to do.

There's good reasons for why I'd like to do this:
1. On screen real estate while viewing records will be easier.
2. The tables can contain data about each prospect_name which also can be
editted or updated as necessary. It seems most of the MVP's, who've been
nice enough to provide some assistance here, agree that table are to store
data. Even you Arvin say: "Tables are for storing, not viewing records."
BUT BUT BUT....

Screen real estate is of negligable concern when using a form because you
can organize data into groups and use a tab to contain that group.

Let me also say, that while a table may have as many as 255 fields it is
extremely rare to use more than 30 in a good database design. I'd say that
hte overwhelming majority of my tables have 15 or less fields. Most of the
time, the answer is not more fields, it's more tables. If you start using
fields like Address1 and Address2, you will often need an address table, and
once you go to Address3, you ALWAYS need an address table.

Since the tables have stored data and since any record in the table can be
reviewed and editted, it seems logical to just work with that as a fact.
I
mean this: Say, I establish several records. A phone number changes for
a
record. Well, I can just go back to the record and edit it. Or say, the
estimated close date for this record changes. Then, again, I can just go
bact to the record and edit it in the appropriate talbe. See, this is the
type of thing I set out to do originally.

And, if I want to run a query then I can just set one up. For example,
suppose I want to know which records are interested in Product A and are
ready to close in Aug '09. Then I can set up a query for that with
boolean
operators to return the report on these records meeting the criteria I
seek.

However, note that in one case all I'm doing is editting or updating a
field
or fields in a record. The other case, I am actually asking Access to
search
my database for records that satisfy a given set of criteria. In the
latter
case, a query is best suited for this purpose. But in the former case, a
query is not really necessary. And that is what my original ideas for
using
Access where based on.

A query is necessary in the former case as well. How else will you find a
record without having to look through all of them?

 

[tina]

Arvin, here's a link to the original thread that Doug and i posted to, in
this ng, in case you find it useful. tina

http://groups.google.com/group/micr...ad/35b59d5a374e76d6/368b0d5220e4a696?lnk=raot

 

[Arvin Meyer MVP]

Thanks Tina.

 

[Liontamer]

An message update specifically for Arvin Meyer MVP and tina
and, of course, other friends of this thread:

Thank you so much for posting your guidance at this thread. From some of
your teachings, I have learnt more about using this awesome program.
Obviously there is much involved in setting up a functioning database.

My original ideas were to utilize Access as a tool for managing leads and
prospects, the way I needed to do so. I played with my ideas somewhat. I
even solved my problem of replicating a field in table one in table two.
This is easily accomplished by just using a longer table containing more
fields. For those following this discussion thread, you all know what I'm
talking about here. There was not an easy way to accomplish this via setting
up a lookup box, nor was it recommended by others.

Furthermore, after some futuristic thinking, I realized how inefficient was
my original objective for using Access. If I had been managing say 50
different records then perhaps my idea would work with just one table, not
two. (I discovered, however, that any one table can have quite a number of
fields. Although this is not recommended by the MVP's. That was my way of
eliminating the need for replicating a field from table one to table two.)

But that doesn't solve my problem either. Why? Because even though I can
have more fields in a table, I have to think of how many records I'm going to
be storing into that table. This is where I have to put the brakes onto my
idea. For when I start entering 100, 250, 1000 + records, my original idea
becomes haphazard.

You see, my original idea was to create a record with fields of my choosing.
As I progressed through time with this lead or prospect, I wanted to just
update various fields of the record. Now with 50 records this could be
managable with just a table, using a query would not even be needed.
However,, with 1000 records this can be a hassle. Even if I was able to
search for a particular record, in order to edit various fields, it still
would not satisfy my needs. Other problems would surface.

For example, if I wanted to download say 400 records for the month of (X) to
a CD-R then I can't do it with access. Because I have to have a table made
for every month and hope I can copy that file to a CD-R with the intention of
keeping my computer memory space available for more current data. And what
if I wanted to go back to a subset or records from a particular month then
I'd have to reload the data into my computer every time. Oh, just
contemplating the possibities, makes me uncomfortable here.

I even wanted to try and add a History field to a record in my table. I
only had two choices. One was to use a Memo field. But this is no good.
Why? Because I can not see the entire contents of the field all at once, as
you can read this discussion. The other choice was to use an attachment.
This is not good either. Why? Because for each record another Word '07
document has to be created. This translates into more computer memory usage
and more files. And trying to download all of this properly to a
CD-R...forgetaboutit !!!!

So, what I had originally hoped for by using Access, is really not suitable
for this program.

The teachings which those of you have shared herein are much appreciated.
However, because of my current realizations (described above), I find Access
to be unsuitable for my current needs. And therefore I will be exploring
another means to accomplish what I am trying to do. One such idea I have is
to purchase a software program such as ACT. For it is with this program,
that I will be better able to accomplish what I originally wanted to do with
Access '07. Even if there is a way to do what I want to do with Access '07,
it just seems way to complicated and involved to set things up properly using
this program.

I'd like to take this opportunity once again to thank those of you who have
been helpful in my quest to find answers to what I was originally trying to
accomplish. How nice of you all to be concerned. Have a pleasant day.