Imported data has different ID

[recyan]

just check out below :
cheyanne.zip

Edit :
1) Done in haste, so could lead to waste.
2) All I have done is implement the last 5 steps, that I had suggested in my previous post.



Thanks

[cheyanne]

Hi Recyan

Many thanks for your assistance. I presume that what you wished me to do was to import
tblClient and tblPet into the database and then run the query. I did that but got the error message
saying that the created table can only have one autonumeric field. You advised before to make the
ClientRef autonumeric field a number field, which I did, but then I could not get an autonumeric
field that tallied with the now numeric ClientRef. For some reason there seemed to be a gap of some
60 plus records i.e. the new autonumeric field was 60 plus records diffferent that the Client Ref field.


I have not made any changes whilst awaiting your comments.


Regards


Cheyanne

[recyan]

No, don't run the query.
I have already done that.
I kept the query just for your reference.
You can delete it after taking a look.
Everything is already done.
Just look at the tables to check if the data is correctly linked i.e. Clients are linked properly to their Pets.
Reattaching, in case something has gone haywire.
cheyanne.zip

[cheyanne]

Hi Recyan

Again thanks for your time and patience.

I have now importred the tables - without running the query - but the Clients do not relate to their pets.

I have attached two screenshots concerning my own record namely MyCat and Really My Cat.

In the first screenshot it shows the owner of Negra as belonging to Client 1158 which is Esteve Maseres

In the second screenshot it now shows Negra as belonging to me i.e Client 1098.


If you look up Suki Pet Ref 416 she is also supposedly owned by Client 1158 which is Esteve Maseres,
when in truth she is my dog.

Not sure where the problem lies but the Clients and their Pets do not align with each other.

Sorry!

Regards

Cheyanne

[recyan]

1) You don't have to import anything.
2) Just open the database I have attached.
3) Open the table tblClients
4) Click on the + sign to the left of ClientRefNew
5) You should be able to see the Pets against that Client.
6) The Old ClientRef no longer exist.
7) Coming to your example of Pet "Negra". "Negra" belongs to "Woodall" "Peter" as per your Excel sheets.
8) Go to "Woodall" "Peter" in tblClients. The ClientRefNew is now 1098. Click on the + sign. You should be able to ideally see Negra.
9) Same for Suki.
10) The Old ClientRef are no longer relevant.

Thanks

[cheyanne]

Hi

At the risk of undoing your kind efforts what is the best way of compiling the whole database, tables, forms queries etc?

Is it best to import your tables into the full database or bring all the objects into the Cheyannr database. I did the former
i.e. imported the two tables and got the results I sent to you. Maybe somewhere alon the line I needed to do something else.


Regards


Cheyanne

[cheyanne]

Hi Recyan

I have just introduce copies of both your tables into the full database and tried to establish a relationship
between tblClients.ClientRef (I had to rename it to gel with the Client form) and tblPets.ClientRef and got the
error message shown in the screenshot. I have looked at the design for each table and both fields are set to
long integer.

Regards

Cheyanne

[cheyanne]

Hi to both of you.

Don't shout too loud but I think it might now be working as intended.


Having opened the main database and I deleted both tables tblClient and tblPets. I then imported tblClients and tblPets
from the Cheyanne database and then deleted all the relationships before reconnecting them - all seems to
be working. I haven't tried inserting records to these tables but cannot see why that should not work.

Can I once again express my gratitude to the pair of you. Both of you are significantly further down the Access highway
than I and without your help what has been acieved simply wouldn't have been.


I'll close this lengthy post as resolved and keep my fingers crossed that I have not done so prematurely.


Thanks again and best wishes for the future from Spain.

[recyan]

Thanks

[ssanfu]

This is mostly an FYI.... in reading the posts, it sounds like you are using the autonumber field type for the fields "Referencia animal" and "Referencia Cliente" and that the autonumber fields identify the client/animal. This is really not a good idea. You have seen how much trouble it has caused so far.

An autonumber field is simply a way to create a unique identifier for each record. It should NEVER be treated as a meaningful piece of data. It is not guaranteed to be sequential/consecutive; it is not even guaranteed to be positive. Granted, it rarely goes negative...... but it can. Since the Autonumber type is a long integer, the range is from -2,147,483,648 through 2,147,483,647. That is a lot of negative numbers....

Don't get me wrong. I use an autonumber field in almost every table. But it has no meaning/use other than to provide a unique identifier for each record. And it is never displayed on a form/report (unless it is for me in trouble shooting).


Anyway, that is my $0.02.....

[June7]

Another $0.02. You have a field NumberOfPets in tblClients. Should not save this data to table. It should be calculated in query when needed. Also in tblPets is Weight field. Weight can change. Isn't this data collected with each visit? Seems redundant to have Deceased and DateDied fields. If there is no date then they are alive.

[cheyanne]

Hi Steve

Thanks for your comments. You are right that the autonumbering system has caused me serious trouble this time. However these problems were largely, if not exclusively, caused by my importing date from a commercial
programme. That programme , which is used by a lot of Vets is frankly unworkable. For example you have to, visit about five different pages to produce an invoice. It is so unworkable that many Vets use it just for basic
data storage i.e. Client details and Pets details, although potentially it can do a lot more (It has over 230 tables). I have a non "imported" data version of this database,with my own data inputted into it, and it worked fine from day one.


I am somewhat concerned by your comments on autonumeric fields as that is what all the books on Access tell you to do. (e.g. Access Bible, The Missing Manual etc) Years ago I used a different database programme and in that one there was no autonumeric field so one had to use a table, which stored the identifier numbers, and on "insert record" a particular page took the last number from that table and added one and on leaving the record send the "new" number to that table for storage pre starting the cycle again.


I am now a pensioner and developing this database for a family friend who is also our Veterinary Surgeon. I last used that database twenty plus years ago so although I remember the theory I would not be sure how to implement such a system in Access 2010. I had a quick look in the manuals and could not see any such solution.


I have to say Steve that I have only used this forum actively during the last three months or so and the contributors are seriously good and generous of their time. It is true that I have seen some posts which have
been somewhat abusive to the contributors, but thankfully most post submitters are deeply grateful for the assistance they get.

It you have any comments to make on how to avoid "autonumeric" fields I am happy to listen and learn.

Regards

Cheyanne (Peter)

[cheyanne]

Good morning June 7

I put the "NumberofPets" field in the Pets table purely for my information when I was having difficulties you are all too familiar with. By doing so I could see how many pets a Client supposedly had
and could compare it with what should be the case. If the numbers were differnet then the linkage was not working properly. Not sure if I will keep it in the table but, if I do, I shall use a query.
Would you store that to a field or have it in an unbound one?

The field weights is required and as you summised will be recorded every visit. At the moment that would need to initially visit the Visits form, as this is the form the Vet
would first use when the Client attended, take the pet's weight , note it and then visit to the Pets form to enter it.

It does howver need to be on the Pets form because the Vet needs to see the last recorded weight - a combination of the date held for the Visit and the weight taken on that visit. I am working
on how best to do that. Any ideas? The reason for the Deceased and DateDied fields is that, as you state, there needs to be a checkbox to say if the animal is alive or not, Howver it goes deeper
than that firstly thedate held in the DiedDate field can be use for


a. Knowing exactly when a Pet died - a Yes/No cannot tell you that.
b. Calculating the age at which the pet actually died. Without that his age increases albeit he is dead. (I noticed that one of my dogs was three years older, last week, than the date he died - hence its inclusion).

Have a nice weekend

Regards

Peter

[recyan]

Hi,

Unfortunately, was / am not in a position to open your db & on my comp, the relationships diagram you have attached is too small for viewing.
There is one thing that has bothered me in the past & is still bothering me.
Is there a chance that, a pet which belongs to one client today, can belong to another client tomorrow / future ?
For eg : A client leaves the area & due to some reason can't take his Pets along. Hence he hands it over to other people in the area.
If there is even a remote possibility & if it has not been taken care of, then I think, we need to take care of that right now.

Edit : Me & Peter posted at the same time. Ssnafu & June, the previous post by Peter has some queries.

Thanks

[cheyanne]

Hi All

This email is in answer to Recyan's last email.

Ther is a possibility, albeit slight, that a current owner passes their pet to a second owner as a result of
their relocation (returning to the U.K. e..g.) their demise, can't afford to keep the animal any longer,
can't cope with it anymore etc. In these circumstances she normally takes the information from the Pets file
and makes a new pet record with the new owner's details (new record for the Client iif necessary) and then
declares the pet "dead" in the old record. She then has a New pet with either an existing Client or a new one.


I could not reply earlier as I have been visiting the Vets practice on Satyurday mornings. She has a Saturday
morning clinic in our village. She has two other practices in our nearest town about 8 kilometres away. These
visits are useful as one can evaluate the needs of the Veterinary Surgeon.

I loaded the database onto her laptop this morning and we encountered an erro on both the Treatment and Medicines
tables. When she tried to alter the price of a treatment or medicine it only showed integer numbers e.g. if she typed in
€ 8.00 the record showed € 800.00. This is really strange becuase the forms work perfectly on my computer running
Access 2010 on Windows 7. Have you any ideas what is happening. She is running the same porgarmmes as I but their
Spanish, not U.K., versions. She agreed with June7´s comment (see post 41) that as the pets weight is taken at each and
every visit, then it would be useful if the pet´s weight could be recorded on the Visits page, with the weight being updated
on the Pet´s page automatically. This would save her having to note the weight and then having to open the Pets form and
then enter it.

I have mentioned the assistance you have all given to me and she asked me to pass on her sincere thanks.

She is taking her laptop home this weekend to "play" with the database and try to find any glitches. This is much
better than her visiting my my home and spending an hour or so whilst I show her how it should function.


I hope all of you have a peaceful weekend.


Regards


Cheyanne