How can I get the duedate.

[lasse]

Hi
I have this problem: In my database over dogs I have among other tables one called - DogData - (contains - DogID, Name, DateofBirth, VaccineID, MothersName, and so on), one called - Vaccines - (contains -
VaccineID, VaccineDateParvovirus8weeks, VaccineDateParvovirus12weeks, VaccineDateParvovirus3year, VaccineDateParvovirus6year...., VaccineDateKennelhoste12weeks, VaccineDateKennelhoste1year, VaccineDateKennelhoste2year.....,VaccineDateRabies12weeks.... and so on) There are actually 4 different vaccines. A relationship exists between -DogData - VaccineID (one) and - Vaccines - VaccineID (many). I have a query called - LastVaccineDate - using - SELECT Max...... and this works ok. I get the last date of the different Vaccines when I run it as expected. (Actually I have 4 of this queries because I have not been able to figure out how to get all 4 ' last vaccinedates' run in the same query. My problem is that I would like to get 'Next vaccine date' (witch differs between the vaccines - 4 weeks, 1 year, 3 year) displayed in a form and or a repport that already contains all other info abt the dog and not only in a separate form as it is now.

I hope there are someone out there who knows abt this sort of things.

Greatings

Lasse

 

[MarkK]

Things like "parvovirus" and "12 weeks" are data, but you've stored them as field names. This makes your data very difficult to work with. Consider doing a little reading on "database normalization" to get a better understanding of how to structure data in a relational database.
One important concept is that one row in a database table is intended to only store one instance of one thing, and fields in that row are dimensions of that one thing.
One row in a Vaccine table should contain the name of exactly one vaccine, and if that one vaccine has multiple related administration dates, those should be stored in a related VaccineAdmin table, with one record for each individual administration interval or duration. Vaccines that are only administered once only have one related VaccineAdmin record.
And there's your dog table, and then you need a table to relate a dog to specific VaccineAdmin record, so you need a DogVaccineAdmin table, and that record has a date of when the actual admin occurred, of which vaccine to which dog, and the dose, and the dog's reaction to the admin. See how each of those things is a discrete "object" in respect to the information you are dealing with? So your data are chained like this ...

Vaccine-->VaccineAdmin-->DogVaccineAdmin<--Dog

Four tables. Dog table knows nothing about vaccine, vaccine table knows nothing about dog, one vaccine has many possible admin dates, one vaccine date should correspond to one real-world administration of one dose of vaccine to one dog, one dog might have been administered many vaccines on many dates.
Make sense? You can structure your data differently than this, but it will make it very very hard to work with.
hth

 

[lasse]

Thanks lagbolt.
I feared there were more to it than just combining the four queries. I understand that I've mixed up a little conserning rows, fields and so on, but I've found a lot of info abt normalization so I have a job to do there. It's a little confusing for the time beeing as english isn't my native language, but I hope I'll manage.
Again thanks for now. hope I can come back later when I've cleaned up the tables.

Greatings
Lasse

 

[MarkK]

Your communication in written English is at least as good as most native speakers.
Feel free to post back here if you need further assistance.
Mark

 

[lasse]

Hello again Mark

I believe I'm starting to get the hang of the consept - Normalization -, I've made 4 tables as suggested:
VaccineT(VaccineID,Parvovirus,Valpesyke,Rabies,Kennelsyke),
VaccineAdmT(VaccineAdmID,8Weeks,12Weeks,1Year,2Year,3Year.....), DogVaccineAdmT(DogVaccineAdmID,ActDate,VaccineType,DogName), DogNameT(DogID,DogName,DogBirthDate,.....).
Relationships:
In VaccineAdmT I have VaccineID as foreign key(many) against the VaccineID(primary key (one)) in VaccineT.
In DogVaccineAdmT I have VaccineAdmID as foreign key(many) against VaccineAdmID(primary key(one)) in VaccineAdm.
In DogNameT I have DogVaccineAdmID(foreign key(many)) against DogVaccineAdmID(primary key(one)) in DogVaccineAdmT.
My problem so far is how to enter the proper data, preferably by means of one or more forms. Do I have to go via a query or directly from one or more tables. I have tried both ways, but with no luck.
I'm searching the Net for a tutorial that can explain this in plain letters or even better visually. If you happen to have an address or two can you please share?

Regards
Lasse

 

[MarkK]

No. Consider this ...

tVaccine
VaccineID
VaccineName (could be parvovirus, rabies, etc..., the name of the virus is data, not a field name)

tVaccineAdmin
VaccineAdminID
VaccineID
TimePeriodFromBirth (could be 8weeks, 2years, whatever, the duration is data, not a field name)
(and one vaccine has one or more admin points)

tDog
DogID
DogName
BirthDate

tDogVaccineAdmin
'this is where most of the events occur
DogVaccineAdminID
DogID (base a combo box on this field, so the user can select the dog)
VaccineAdminID (Combobox, so user can select which scheduled vaccine admin event this is)
CustomerID???
ActualDate (entered automatically as today)
Dose??? (how much vaccine. does this matter?)
Price???

Your dogs don't change much. Your vaccines don't change much, the admin schedule doesn't change much. All you work is going to be recording what dogs got what scheduled admin of what vaccine. Making more sense?

 

[lasse]

Hello Mark.

YES it made a lot more sense. There are always parts that is troublesome to figure out and one is this. In the 'TimePeriodFromBirth', what shall I enter? I thought maybe - 'Date of birth + a number of days/weeks/years - to get the next duedate for a certain vaccine, an then probably have to enter the actual date when the vaccineshot has been administred.
The goal should preferably be an automatic reminder abt next vaccine dates when opening the database or using a button to start a report. The reminder is the main thing here.

Hope you kan clarify for me as good as you did last time.

regards

Lasse

 

[MarkK]

The vaccine is administered on a schedule. You need to create that schedule in your data so that you can calculate a due date for the next dose. In your original post you had intervals like 8 weeks, 12 weeks, 3 years, 6 years and presumably those are periods of time from the birth of the dog, so you need to store that information. By itself. Maybe a clearer name for the VaccineAdmin table is VaccineSchedule, so data looks like . . .

[B]tVaccine[/B]
VaccineID VaccineName
      123  Parvovirus

... with child schedule records like ...

tVaccineSchedule
VaccineScheduleID VaccineID DaysInterval
             4476      123            56       '8 weeks
             4497      123            84      '12 weeks
             4501      123           364       '1 year

... so what you are doing is storing the vaccine schedule all by itself. No dog data here at all. One vaccine, many scheduled admin dates on intervals from the the birth of some, any, abstract dog.

Then, to find the due-date for a prticular real-world dog you add the VaccineSchedule DaysInterval to the BirthDate of a particular Dog. But the schedule itself has to work for all dogs. Is that making more sense?

cheers,

 

[lasse]

Hello again Mark.
Sorry for the delay, but been otherwise angaged. You were spot on so now everything is as it should be. Just some tidying up and I can use the database.
Thank you very much for all your excellent help. The way you explained made it so easy to understand.
Maybe I can hope for your help if the need arises in the future.

Again thanks a lot and have a nice day.

Lasse

 

[MarkK]

Very kind of you to say. Have a fine day yourself.
Mark

 

[lasse]

Hello again Mark.
I thought I'd solved all problems. Guess what. When I pressed 'Package solution' under 'Save and publish' it seemed like it all went ok and I created a CD. BUT when I try to install the database on a user's PC ( I've tried on XP, 7 and 8 pc's) they alle show a warning 'A potential security consern has been identified' and when I press ok, runtime opens without the base. Just two tabs: File (print, Privacy Options, Exit and Save as Adobe PDF) and Acrobat (Create PDF aso.....).
Obviously I've overseen something, but what. Do you have any idea?

Regards

Lasse

 

[MarkK]

Check out this post about what registry settings you might need to tweak to get a packaged solution to run . . .
http://fanaticalmoderate.blogspot.ca/2013/02/access-developer-extensions-specify.html
That post is about how to set the datatype of the registry setting using the Access Developer Extensions packaging wizard, but what you need to know, I think, is to set the Access\Security [VBAWarnings] registry setting.
Also, you may need to explicitly create or show ribbons, since the runtime version of a solution will exclude the default Access ribbons, since most of those functions require access to be installed.

Also in English: "Overlook" and "Oversee" have very different meanings. They shouldn't, but they do.

All the best,
Mark

 

[lasse]

Hello Mark

Thanks for your suggestions. I've read and tried to figure out what's what, but now I'm more confused than ever before. Whatever I try I ends up with the same error message so obviouly I have not understood how to do this. The wizard on the 'third step' gives me the possibility to enter register info, but I'm not able to figure out what goes where. When I go to :'fanaticalmoderate.blogspot.ca...s-specify.html' it seems quite easy, but where do that info fit into the wizard.
Lastely you say: 'Also, you may need to explicitly create or show ribbons, since the runtime version of a solution will exclude the default Access ribbons, since most of those functions require access to be installed.' Do I have to create ribbons to make it work apart from the security error message? If so where do I do that?
Now I feel quite stupid because last time I made a runtime verion of an access database I used office 97 (sort of 15 years ago) and as I can remember it was much easier to get it up and running. My only hope now is that you can help me with a detailed explenation of what to do.
Lasse

 

[MarkK]

You might want to start a new thread about some of this stuff because I have only deployed one runtime database solution. That solution has a custom ribbon, and the only ribbon that shows up is that custom one.

But it's not hard to test. A runtime file is just a regular file with the last letter changed, so change your development copy filename to WhateverDb.accdr and run it. What ribbon do you see?

In the ADE wizard, on page three, how many fields are there in each row where you specify the registry keys? If there are three then you need to add . . .

Field 1: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security
Field 2: VBAWarnings
Field 3: #1

. . . and in field 1, you might need to change the version to 14.0 or whatever version your runtime is. 12.0 is Access 2007, which is what I have.

Yeah, since 1997 there has been a lot of malicious code created and released, and trust in computing has become a major issue. The ADE installer will require a user to have Admin privileges on the target machine, and it's on the strength of that authentication that the registry will allow the update to occur. But without that, your runtime VBA code is prohibited from running.

Does that answer your questions?