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

[Liontamer]

Dear Friends:

I have just completed the design of my first database. In the process of
testing it, I am running into some concerns.

I created some test records to see how things work. However, I cannot seem
to completely remove these test records from the database. Even though I can
successfully delete the record, when I go to create another test record I
cannot seem to start with a clean completely cleared out database. This is
confusing to me.

Let me give you a little background on this problem. Basically, I created
two different tables. I created a one to many relationship between the two
tables. I used the ID Field of table one (Primary Key) to create my
relationship with table two. (Bear with me, I trying here.) Table one has a
name field. Table two has the same name field. I've noticed that when I
enter a record in Table one and save it, then open Table two, the look up
list of the name field of Table two contains the name from Table one. This
is not the problem. The problem comes about when I delete a record from
Table one.

I can delete a record from my database. However, when I enter a new record
into my database, the ID Field keeps counting from the next available number.
Gee, I hope I'm explaining this situation clearly. (I'm trying.) What I
expect to happen is not happening. Here's what I mean. If I delete a record
from my database then i expect any and all information related to that record
also disappears. That includes it's ID #. So, if I establish a record with
the ID #1 but then I delete it from the database then the counter for records
in the database should reset back to zero. And when I replace the deleted
record with a new record then it should now get the status of being the first
record or ID #1. This is the problem. This is not happening.

Hence:
I wonder if there's a way to completely reset the database back to zero, as
if there never was a record entered into it in the first place. Is this
possible?

Note: I have other concerns too. But I want to just address this issue in
this post. Thank you for your support.

 

[Douglas J. Steele]

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.

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.

 

[tina]

Doug's post addressed your primary concern. but i noticed that your table
description indicates that Table two has a Lookup field in it. recommend you
get rid of the Lookup; change the field to an ordinary Number or Text field.
i'm guessing that, as a newbie, you probably are making a number of other
common mistakes, such as the one Doug pointed out. for more information on
Lookup fields, and other "getting started" pointers of things to do, things
to avoid, take a look at http://home.att.net/~california.db/tips.html.

hth

 

[Liontamer]

Thank you tina. I will review the info you've pointed to. Gee, I'm sure
hoping I can get my database to work properly. I'm trying. I know I'll
figure this stuff out. But please read my reply to Doug.

 

[tina]

you're welcome. Access is a powerful program, but it has a steep learning
curve, and you need to do a fair amount of learning upfront before you can
build a solid, working database. don't be discouraged. we all started as
newbies to Access, and though many expert programmers in these newsgroups
have broader programming skills and experience, there are others, like
myself, who have no "schooling" in computer programming and have basically
taught themselves to use Access to design and develop relational databases.
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.

hth

 

[tina]

btw, i didn't see a post in this thread that replies to Doug's post. maybe
there's a problem with my newsreader, or, are you sure you posted back to
this thread?

 

[Douglas J. Steele]

I never saw a reply to me either, Tina.

 

[Liontamer]

Dear Tina, Douglas 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!

 

[tina]

no problem re the posting; things happen. glad it's working for you now.

let me break the news to you, hon: when you build an Access database, you
ARE an Access developer. whether you build a simple db, or complex, whether
you build it well, or poorly, doesn't change that fact. 1) if you want to
ensure data integrity, and ensure that the information you extrapolate from
the data is correct, then you need to follow relational design principles in
building your tables/relationships. 2) in your database, you SHOULD be using
forms to interact with the data; it doesn't matter whether or not you're the
only user, Access tables are not intended for data entry. use forms, with
comboboxes for lookups, as i said before. 3) split your database into
front-end, back-end, files; again, this is best practice even for a
single-user database. backend db holds all the tables, with relationships
set up and enforced. frontend db holds all other objects - queries, forms,
reports, macros, modules. link the backend tables into the frontend db. 4) i
can't comment on the A2007 software directly, except to say that AFAIK the
Memo data type is still available. when you need to add copious freehand
notes that will not fit into the 255 character/space Text data type, then
use the Memo data type for the notes fields. just go back to relational
design principles and make sure that the data you're is in fact freehand
notes, and not data that should be split out into separate, defined data
fields.

i also can't comment on Microsoft's tutorials specifically, since i've never
reviewed any. but, frankly, i wouldn't expect them to build an option into
the software and then turn around and trash it. but the overwhelming
consensus of experienced, and in many cases, expert, Access developers in
these newsgroups is that Lookup fields in tables are to be avoided, period.
whose advice you choose to follow is your business. my *guess* is that the
tutorials provide instruction on how to use the software, nuts-and-bolts
things like how to add a control to a form, add a field to a table, etc. you
certainly need to learn the basics of how to physically use the tool, but
that's maybe 20% of what you need to know to build a sound, solid working
database in Access (remember what i said about a steep learning curve). i
strongly urge you to read the first tip on the webpage i sent you to (link
posted elsewhere in this thread) *and* follow the link in that tip, to the
awesome resources listed on the ConradSystems website. good luck in
developing your database.

hth