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 do I get autonumber function in access to work
I work for a small newly established company and am setting up a database in
Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#2
|
|||
|
|||
Katharine,
You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#3
|
|||
|
|||
Katharine,
I was just reading what I posted and realized I missed a sentence in the middle. To recap then: Create "ClientID" in your primary table. Make it autonumber. Create "ForeignClientID" (or whatever) in your secondary tables. Make them just "number" as the data type. Link the ClientID and ForeignClientID together with relationships. Sorry for the confusing previous post. Aaron G Philadelphia, PA "Aaron G" wrote: Katharine, You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#4
|
|||
|
|||
Hi Aaron, thanks so much for your advice. I have made the change as you have
specified, unfortunately I can't seem to get the Course Details table to pull the "Client Ref" data from the primary "Client Details" table. Does this mean I will have to delete the Course Details table and do it again?? I'm hoping it won't have to come to that. Thanks Katharine "Aaron G" wrote: Katharine, You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#5
|
|||
|
|||
Hi Aaron,
I've just done what you suggested, putting the "foreignClientRef" in the secondary table and it still isn't working. What has happened is a plus sign has come up in a Column to the left of the initial "ClientRef" column in the primary table. When selected it brings up the related (secondary) table. Does this then mean I am to manually input the data, as it does not appear to be related. There is no data when I select the plus sign in this column, but when I open the related table I originally created, all the data is there less the data which is to appear in the ForeignClientRef which should have been pulled over from the primary table as you advised. Am I still missing something here??? Thanks in advance Katharine "Aaron G" wrote: Katharine, I was just reading what I posted and realized I missed a sentence in the middle. To recap then: Create "ClientID" in your primary table. Make it autonumber. Create "ForeignClientID" (or whatever) in your secondary tables. Make them just "number" as the data type. Link the ClientID and ForeignClientID together with relationships. Sorry for the confusing previous post. Aaron G Philadelphia, PA "Aaron G" wrote: Katharine, You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#6
|
|||
|
|||
Katharine,
If you've already started putting data into your tables, then you're going to have to do some manual cleaning up. It's easy, but will be a little time consuming if you've put in a lot of data. Here's what happened and what you need to do: Since you originally created the tables (we'll just work with two for now) both having "autonumber" they didn't link. If you simply switched the secondary table's "foreignClientRef" to a number, then you've only gone half way. You'll notice that the numbers that were auto generated before are still in those fields. These numbers will not, however, match the primary table. The numbers must be IDENTICAL to "hook" the two tables together. You'll notice if you create a totally NEW record on the secondary table that it WILL link up fine (or at least it should). So, what do you do? Assuming that you don't have too many records, the easiest way to do this is a simple copy and paste. Before you do the copy/paste thing, first make sure your relationships are set up right. Add a new record and see if it "sticks". I'm assuming it will. If not, let me know. Now, create a query. Call it something like "IdCleanup" or whatever. Add both your primary table and your secondary table. Drag down the "ClientRef" and "foreignClientRef". Also add as many fields as you would need to identify what you're linking. Say "LastName" and "CourseName". Now look at your query. You will see the ID columns right next to each other. Simply copy the ID from the primary table over the corresponding secondary. This isn't the prettiest solution, but assuming you don't have too many records, it should work pretty easy. Good luck! Aaron G Philadelphia, PA "Katharine Jansen" wrote: Hi Aaron, I've just done what you suggested, putting the "foreignClientRef" in the secondary table and it still isn't working. What has happened is a plus sign has come up in a Column to the left of the initial "ClientRef" column in the primary table. When selected it brings up the related (secondary) table. Does this then mean I am to manually input the data, as it does not appear to be related. There is no data when I select the plus sign in this column, but when I open the related table I originally created, all the data is there less the data which is to appear in the ForeignClientRef which should have been pulled over from the primary table as you advised. Am I still missing something here??? Thanks in advance Katharine "Aaron G" wrote: Katharine, I was just reading what I posted and realized I missed a sentence in the middle. To recap then: Create "ClientID" in your primary table. Make it autonumber. Create "ForeignClientID" (or whatever) in your secondary tables. Make them just "number" as the data type. Link the ClientID and ForeignClientID together with relationships. Sorry for the confusing previous post. Aaron G Philadelphia, PA "Aaron G" wrote: Katharine, You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
#7
|
|||
|
|||
Aaron
Thanks so much for your help. It was very useful and has helped me on the way to completing this database. Ever appreciated. Katharine "Aaron G" wrote: Katharine, If you've already started putting data into your tables, then you're going to have to do some manual cleaning up. It's easy, but will be a little time consuming if you've put in a lot of data. Here's what happened and what you need to do: Since you originally created the tables (we'll just work with two for now) both having "autonumber" they didn't link. If you simply switched the secondary table's "foreignClientRef" to a number, then you've only gone half way. You'll notice that the numbers that were auto generated before are still in those fields. These numbers will not, however, match the primary table. The numbers must be IDENTICAL to "hook" the two tables together. You'll notice if you create a totally NEW record on the secondary table that it WILL link up fine (or at least it should). So, what do you do? Assuming that you don't have too many records, the easiest way to do this is a simple copy and paste. Before you do the copy/paste thing, first make sure your relationships are set up right. Add a new record and see if it "sticks". I'm assuming it will. If not, let me know. Now, create a query. Call it something like "IdCleanup" or whatever. Add both your primary table and your secondary table. Drag down the "ClientRef" and "foreignClientRef". Also add as many fields as you would need to identify what you're linking. Say "LastName" and "CourseName". Now look at your query. You will see the ID columns right next to each other. Simply copy the ID from the primary table over the corresponding secondary. This isn't the prettiest solution, but assuming you don't have too many records, it should work pretty easy. Good luck! Aaron G Philadelphia, PA "Katharine Jansen" wrote: Hi Aaron, I've just done what you suggested, putting the "foreignClientRef" in the secondary table and it still isn't working. What has happened is a plus sign has come up in a Column to the left of the initial "ClientRef" column in the primary table. When selected it brings up the related (secondary) table. Does this then mean I am to manually input the data, as it does not appear to be related. There is no data when I select the plus sign in this column, but when I open the related table I originally created, all the data is there less the data which is to appear in the ForeignClientRef which should have been pulled over from the primary table as you advised. Am I still missing something here??? Thanks in advance Katharine "Aaron G" wrote: Katharine, I was just reading what I posted and realized I missed a sentence in the middle. To recap then: Create "ClientID" in your primary table. Make it autonumber. Create "ForeignClientID" (or whatever) in your secondary tables. Make them just "number" as the data type. Link the ClientID and ForeignClientID together with relationships. Sorry for the confusing previous post. Aaron G Philadelphia, PA "Aaron G" wrote: Katharine, You only need to create the "autonumber" on the primary table. Say you called this "ClientID". Create the same field (or however your naming conventions is working) but do NOT make it autonumber. Make it just "number" as the data type. Then link up the relationships. Ths secondary table will automatically pull over the "autonumber" from the primary table as soon as you create a field. HTH Aaron G Philadelphia, PA "Katharine Jansen" wrote: I work for a small newly established company and am setting up a database in Access on Office 2000 Premium to store client information and account information. I have set up the system to create an autonumber for each new entry, but the database needs to be relational so that I can recall data about customer accounts from various tables/forms, you know the drill. My stumbling block is the autonumber function in the relational database. I have designed the database with four files; client details, course details, uncleared payments and closed accounts. The client details file has been created with the client reference as the primary key and with an autonumber function. My problem is when creating a relationship between the client details and course details files it has somehow changed the number of the client references, so the clients have different refernces in the course details file than they do in the client details file. The client reference is an autonumber in both the client details file and the course details file. I don't want to pursue with the database when it fails to work competently at this stage. Can anyone shed some light on what I appear to have done wrong?? Thanks Catherine Jansen |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access or Visual Studio? | Jerome | General Discussion | 61 | April 7th, 2005 07:14 PM |
Access 97 to XP and problems with the Format function | Al K | Setting Up & Running Reports | 1 | December 14th, 2004 01:34 AM |
Word 2000/2002 - Proper Mail Merge steps for ODBC? | Tony_VBACoder | Mailmerge | 7 | September 2nd, 2004 09:21 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Running Access from network | Alan Fisher | Using Forms | 4 | July 3rd, 2004 02:37 AM |