If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to eliminate records that are used to test a created database?
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. |
#2
|
|||
|
|||
How to eliminate records that are used to test a created database?
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Liontamer" wrote in message ... 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. |
#3
|
|||
|
|||
How to eliminate records that are used to test a created database?
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" wrote in message ... 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. |
#4
|
|||
|
|||
How to eliminate records that are used to test a created datab
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" wrote: 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" wrote in message ... 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. |
#5
|
|||
|
|||
How to eliminate records that are used to test a created datab
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 "Liontamer" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
|
|||
|
|||
How to eliminate records that are used to test a created datab
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? "Liontamer" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
|
|||
|
|||
How to eliminate records that are used to test a created datab
I never saw a reply to me either, Tina.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "tina" wrote in message ... 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? "Liontamer" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#8
|
|||
|
|||
How to eliminate records that are used to test a created datab
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! "Douglas J. Steele" wrote: I never saw a reply to me either, Tina. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "tina" wrote in message ... 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? "Liontamer" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#9
|
|||
|
|||
How to eliminate records that are used to test a created datab
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 "Liontamer" wrote in message ... 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! "Douglas J. Steele" wrote: I never saw a reply to me either, Tina. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "tina" wrote in message ... 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? "Liontamer" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|