Imported data has different ID

[June7]

I'm with Recyan, why must start with 1630?

And yes, can create an autonumber field in table with existing records, it just won't have relationship to records already in child tables.

[cheyanne]

I am not using NewClientRefID at all - I deleted it, and tblClient.CleintRef column when I renamed the "new" table as tblPets.

The last number in the tblPets ClientRef column is 1629 and these numbers need to exist to
match their respective pet ids. That has now happened, thanks to your assistance, and all the pets
currently marry up to their respective owner.

If the tblClient's next record was numbered 1566 it would totally screwup the required linkage, hence
my needing the next Client record to start at 1630.

[June7]

So the issue is still that ClientRef is not an autonumber field? Assigning the next number in sequence to the new record will require VBA code.

We have offered methods to create an autonumber ClientRef field. If you follow one of the suggestions, the new autonumber field will be created in tblClient and these generated IDs will be the fk values in tblPets and records will be properly related. ID 1566 would be the next number generated by the autonumber field in new record.

[cheyanne]

That was exactly why an autonumber field would not work in my database. It is imperative that the pets in tblPets
link to their resepective owner (held in tblClients). Let me give you an example of my own Client record. In the
autonumber field NewClientRef (albeit not currently used) my number is 1100. In my ClientRef field it is 1158.
My pets (I have more than one) relate and link properly to my 1158 number whereas the 1100 number links me
to other Client's pet. Currently the pets in tblPets seem to relate to their correct owner. It follows therefore that the
next Client needs to have a unique rreference number of 1630

I am currently researching what code would be needed to achieve this in VBA code. A fellow forum contributor TG_W suggested that I use =DMax("ClientRef","tblClients")+1 on the default property of the currently used ClientRef field.
If you have any comments re suitable code and where to include it I would be most interested to hear from you.

I hope that this has clarified the problem. It was Recyan's code that enables me to make a new table and I merely deleted the NewClientRef (autonumber) and the tblClient.ClientRef columns and
renamed the table tblPets.

As I have said before I am greatful for all the contributors comments. I shall now have to read up on "MAKE" tables as it is a totally alien concept to me - albeit clearly useful.

Regards

Cheyanne

[recyan]

Don't know if I am missing something :

Let us say, you have imported from excel sheet

tblClients

ClientRef
1585
1586
1587

and

tblPets

PetID    ClientRef
1    1585
2    1585
3    1586
4    1586
5    1586
6    1587
7    1587
8    1587
9    1587

_____________________________________

Now, in your tblClients, add a new AutoNumber field NewClientRefID & make it primary key.
Open the tblClients in Datasheet View.
It should appear as below :

NewClientRefID    ClientRef
1    1585
2    1586
3    1587

_____________________________________

Now if we run the below Make Table query to make a new table tblClientsPets:

SELECT 
    tblClients.NewClientRefID, 
    tblClients.ClientRef, 
    tblPets.ClientRef, 
    tblPets.PetID 
INTO 
    tblClientsPets
FROM 
    tblClients 
    INNER JOIN 
    tblPets 
    ON 
    tblClients.ClientRef = tblPets.ClientRef;

Now on opening the tblClientsPets table, the data will be seen as below :

NewClientRefID    tblClients_ClientRef    tblPets_ClientRef    PetID
1    1585    1585    2
1    1585    1585    1
2    1586    1586    5
2    1586    1586    4
2    1586    1586    3
3    1587    1587    9
3    1587    1587    8
3    1587    1587    7
3    1587    1587    6

The field NewClientRefID in this new table tblClientsPets is the foreign key on the Autonumber Primary field NewClientRefID in the tblClients.
You can delete the 2 fields tblClients_ClientRef and tblPets_ClientRef from the new table after observing that the result is as per your requirement.
You can delete the tblPets after above procedure.

The 2 tables tblClients and tblClientsPets are now the relevant required tables with
NewClientRefID as Primary Autonumber Key in tblClients
and
NewClientRefID as Foreign Key in tblClientsPets.

Edit :
Delete ClientRef from tblClients .

Finally the 2 tables :

tblClients

NewClientRefID	ClientRef
1	1585
2	1586
3	1587

and


tblClientsPets

NewClientRefID	tblClients_ClientRef	tblPets_ClientRef	PetID
1	1585	1585	2
1	1585	1585	1
2	1586	1586	5
2	1586	1586	4
2	1586	1586	3
3	1587	1587	9
3	1587	1587	8
3	1587	1587	7
3	1587	1587	6

Thanks

[cheyanne]

Hi

I have uploaded two screenshots. The first "Extract Clients" is a screenshot of the Client's table, in the original database, before it was exported to Excel.
The second "Extract" is a screenshot of the Pet' table, again in the original database before it was exported to Excel.

Taking "Extract Clients" first this shows the first field as being ClientRef and the last records is shown as having the number 1629 This is why the next Client must be numbered 1630.

The NewClientRef was an automated number field I introduced to my own table - tblClients. Then I imported the rest of the data from the Excel Client file. It was then that I first noticed that
there were actually only 1565 records and not 1629, albeit spread over the range 1 to 1629 - hence my concluding that 60 plus records had been deleted over time.

At no time did the ClientRefNew appear in the data imported.

The second screenshot shows three reference fields a idAnimal, Numero (number) and the ClientRef, the latter tallies with the ClientRef of the Clients table.
This is of course why the animals are bound to ClientRef's from the Client table. For example Client 1629 has a pet called Fivi. The client with that
reference number is 1629 Gomez Pepe.

This is why it is imperrative that Fivi (reference 1944) is attached to Client 1629 Pepe Gomez.

Does this make my problem clearer to you? I do appreciate that advising from afar is difficult and do appreciate the time you have spent advising me.

I would like to find some VBA code to make the autonumbering start at 1630. I tried the code given to place in the default property of ClientRef but it seems
to be putting these records BEFORE the first physical record. So when I use the button to go to the first record i.e. 1, I actually go to the first record I entered after implanting
this code 1980 plus. If I then use the "Next" record button I eventually find "Record 1". Clearly not ideal and will lead to user confusion.

I have re-indexed the PetRef field as smallest to largest but whilst accurately portrayed in the tblPets, on the frmPets form the records start at the first record I entered, post i
ntroducing the default code, and not the actual first record.

Regards and thanks

Cheyanne

[cheyanne]

Hi Recyan

Maybe I am missing something as well.

I have tried what you suggested innumerable times but recall this area of expertise is alien to me.

I named my tables tblClientNew and tblPetsNew so that the originals were unaffected.

I have attached a screenshot showing the SQL code I have used in the Make Table, but I get an error
message saying that the newly created table cannot have two autonumber fields. Clearly correct but I do not
know why this is the case.

On your original MakeTable code I only created a table with four fields. I then decided it might be have been better
to itemise all the fields for that table.

My tblClientNew table auto numbers from 1630 for a new record but I need to ahve the equivalent in teh tblPetsNew
table.Is there another way of incorporating the NewClientRef into tblPetsNew? If not do you know where have I gone wrong?

Regards

Cheyanne

[June7]

cheyanne, did you try the alternative method I suggested?

[recyan]

I get an error message saying that the newly created table cannot have two autonumber fields.

Perhaps, you have tblPetsNew.PetRef set to AutoNumber.

Thanks

[cheyanne]

Hi June7

No I haven't tried your suggestion yet but have printed it out. I resolved the "two" autonumbers problem but it still does not work as suggetested
by Recyan. I shall keep trying but Update, Appemd Make Tables techniques are all alien to me and I am probably making some "obvious" mistakes
to more experienced programmers.

Will keep posting any progress until I find a solution.

Regards

Cheyanne

[recyan]

I resolved the "two" autonumbers problem but it still does not work as suggetested

What does not work or What is the error being thrown up?
If you tell this, it will be easier for some one to help.

Edit : Try following Post 20 steps in a test db, using the data as shown in the post. See if that works. If it works, then try with your db.

Thanks

[cheyanne]

Hi June7 and Recyan

I think I have found a problem using the current MakeTable code.

When it creates the tblClientPets which will then become tblPets, I need the PetRef to be an autonumber field.
The current Make Table code makes the NewClientRef the autonumber field. In the tblClientPets table PetRef
is a number field and I cannot change it to an autonumber one, even having changed NewClientRef to normal. As you
have said I can introduce a new autonumber field but then the links to the "old" PetsRef are lost and the record
numbers are different.


I have attached two screenshots

a. MakeTable shows the code I am currently using to produce the table tblClientsPets
b. tblClientPets shows the design of that table.

I think that I need the MakeTable code amending so that the table retains PetsRef as the autonumeric field.

Any ideas?

June7 I did try inserting autonumeric fields into both tblClients and tblPets. It worked fine in tblClients but did not work as intended in tblPets. A
ll the linkages were lost to the "old" PetRef which meant that I lost all previous linkages with pets. I could not see a way of re-aligning them.

I'll keep trying and update you both as and when necessary!


Regards

Cheyanne

[recyan]

I think I have found a problem using the current MakeTable code.

When it creates the tblClientPets which will then become tblPets, I need the PetRef to be an autonumber field.
The current Make Table code makes the NewClientRef the autonumber field. In the tblClientPets table PetRef
is a number field and I cannot change it to an autonumber one, even having changed NewClientRef to normal. As you have said I can introduce a new autonumber field but then the links to the "old" PetsRef are lost and the record numbers are different.

Try :
1) Change the NewClientRef the autonumber field to Number Field in tblClientPets.
2) Add a new field PetRefID as AutoNumber Field in tblClientPets.
3) Delete the old PetRef in tblClientsPets.
4) The link is One to Many from NewClientRef in tblClients to NewClientRef in tblClientsPets.
5) PetRefID is now the primary key in tblClientsPets & uniquely identifies each pet.

Edit :
Assuming that your Pets table is not linked to any other table except tblClients.
Thanks

[June7]

My suggestion did not indicate to create an autonumber field in tblPets. The suggestion was:
Create another autonumber field in tblClients and let it number the records. If you are lucky the autonumber sequence will be the same as the existing ClientID number field and can just remove it and rename the new autonumber field (relationships, if established, will have to be removed and reset). If not the same then create another number field in tblPets and update it to the new tblClients autonumber ID in an UPDATE query that joins the tables on the orginal pk/fk fields. After update, remove the relationship between the original pk/fk fields, delete old fields, rename the new fields, and establish new relationship. The renaming is only if you want to use the original field names.

A unique ID field in tblPets is needed only if tblPets has dependent tables although doesn't hurt to have one. Just create it after the pk/fk clientID fields are fixed.

If Recyan's last suggestion doesn't get you through this, can you provide the db for analysis? Follow instructions at bottom of my post. One of us will fix the tables for you and we can hopefully stop going in circles.

[cheyanne]

Hi June7

Many thanks for your patience in this matter. I have uploaded a zip file
spreadsheets.zip which contains the two Excel files I imported into tblClients
and tblPets respectively. In each case ClientRef and PetRef were autonumeric fields.
I have also included the text file (WordPad) for the MakeTable SQL code I used -
ii might provide a clue as to where it went wrong!

My database is a an accdb type and I have tried to convert it to earlier vesions but it
says I cannot. It appears that Access 2010 has facilities in it that cannot be read by earlier
versions of Access. I looked at the obvious "additions" and could not see anything thast I had used.

I have just added the database to the Spreadsheets .zip file.

If you need anything else please ask.

Thanks to you both

Cheyanne