how do I get autonumber function in access to work

[Katharine Jansen]

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

 

[Aaron G]

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

 

[Aaron G]

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

 

[Katharine Jansen]

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

 

[Katharine Jansen]

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]

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]

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