Update numbers in database

  • Thread starter Thread starter inge
  • Start date Start date
I

inge

Hello,..
I have numbers (like 4,3 years or months) input in the database.
What i would like is to have theese numbers to be updated when
they shows on the report if possible.

I have used several queries to make the report, so i was hoping that i could
use
something in the queries.

i am very new to this and have only made it so far thanx to good answers here.

Thank you
 
Hello,..
I have numbers (like 4,3 years or months) input in the database.
What i would like is to have theese numbers to be updated when
they shows on the report if possible.

I have used several queries to make the report, so i was hoping that i could
use
something in the queries.

i am very new to this and have only made it so far thanx to good answers here.

Thank you
Click to expand...

Please explain what you mean by "updated when they show on the report". Do
you want the values in the table to change whenever you run the report? What's
the structure of your table, and what do these numbers mean? Are they a
calculated age perhaps?
 
Hi,.

I think it is a ordenary database with several (many) posts.
The point with the database is to make several reports, and we can make some
inputs only once. Some posts is experience and the input will be in years or
months like this : 5,3. It will be the same for years or months ofcourse
depending on the experience. I have made this in excel datasheet and i only
used the ((TODAY() - (an input date)/30) + the input number) also /12 if
years.
and 30 days a month etc... but i dont know how to do this in access.
working ok there.

The input experience is the time they have when making the input in to the
database. just a number, something like 6.3 or 10,9. Its going to be in years
or months but looking like 6,3 or 10,9 etc.

What i need is how to make the report show with updated numbers or the
database to update the figure from the input figure, and change my queries.

I just managed to make the report show the figures from the database.
but cant see how to autoupdate this numbers.

maybe i need another database for the update?

John W. Vinson said:
Hello,..
I have numbers (like 4,3 years or months) input in the database.
What i would like is to have theese numbers to be updated when
they shows on the report if possible.

I have used several queries to make the report, so i was hoping that i could
use
something in the queries.

i am very new to this and have only made it so far thanx to good answers here.

Thank you
Click to expand...

Please explain what you mean by "updated when they show on the report". Do
you want the values in the table to change whenever you run the report? What's
the structure of your table, and what do these numbers mean? Are they a
calculated age perhaps?
Click to expand...
 
Hi,.

I think it is a ordenary database with several (many) posts.
The point with the database is to make several reports, and we can make some
inputs only once. Some posts is experience and the input will be in years or
months like this : 5,3. It will be the same for years or months ofcourse
depending on the experience. I have made this in excel datasheet and i only
used the ((TODAY() - (an input date)/30) + the input number) also /12 if
years.
and 30 days a month etc... but i dont know how to do this in access.
working ok there.
Click to expand...

Take a look at

http://www.accessmvp.com/djsteele/Diff2Dates.html

It's a VBA function which can take two date fields and display the time
elapsed in various formats, including years/months.
The input experience is the time they have when making the input in to the
database. just a number, something like 6.3 or 10,9. Its going to be in years
or months but looking like 6,3 or 10,9 etc.
Click to expand...

I'm sorry, I still don't understand. Are you CALCULATING the number of years
and months of experience based on dates in your table and today's date? Or are
you typing it in? If you're typing it in I'd use two fields...
What i need is how to make the report show with updated numbers or the
database to update the figure from the input figure, and change my queries.
Click to expand...

Again: What do you mean by "updated numbers"? Do you want Access to calculate
it, or to display newly manually entered values, or what?
I just managed to make the report show the figures from the database.
but cant see how to autoupdate this numbers.

maybe i need another database for the update?
Click to expand...

Access can do what you want... I haven't quite figured out what you DO want so
I am stumbling in attempting to tell you how!
 
im sorry for my bad explanation, but i try as good i can.
maybe this is better:

I have a database with many posts.
I use a form to make all the inputs.
I have queries to make information on a report linked
to the database, using a form with combobox for selecting what info to show.
in the database the user will make inputs looking like 5,3 or 7,3. theese
numbers
represents experience time before registration in the database. (months or
years)
Also i have two posts in the database showing dates, one representing the
date of first time registration and one representing date this time onboard.

how can i update the inputs looking like 5,3 or 7,3 using the input numbers
and date? example,.. when a year have ellaps after the first time
registration the 5,3 number should show 6,3 and the 7,3 number should show
8,3. or after 6 months 5,3 should be 5,8 etc.

post in database (named experience):
years in rank : 5,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
years on tanker : 7,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
Months onboard this tour of duty : 2,1 (this number i hoped to show
automatic calculated from access using the date this time onboard)
date first time onboard : an date(11.10.08) user input
date this time onboard : an date(11.11.08) user input

(theese two dates is only for making the update, and is not showing in the
report.
The dates is now only in the database, but i can move them if you have a
better sugestion.)

The number format is a requirement, how its going to show on the report.

(the queries are showing info from database from a name selected from the
combobox. Some info is as described and other is plain text or checkbox. This
information shows on the report.)

So its all about having the input "numbers" for years in rank, years on
tanker,
and months onboard to be updated by access after the first time registration.
The date for this time onboard need to be updated by user every time the
person comes onboard.

brgds
inge
 
inge said:
im sorry for my bad explanation, but i try as good i can.
maybe this is better:

I have a database with many posts.
I use a form to make all the inputs.
I have queries to make information on a report linked
to the database, using a form with combobox for selecting what info to show.
in the database the user will make inputs looking like 5,3 or 7,3. theese
numbers
represents experience time before registration in the database. (months or
years)
Also i have two posts in the database showing dates, one representing the
date of first time registration and one representing date this time onboard.

how can i update the inputs looking like 5,3 or 7,3 using the input numbers
and date? example,.. when a year have ellaps after the first time
registration the 5,3 number should show 6,3 and the 7,3 number should show
8,3. or after 6 months 5,3 should be 5,8 etc.

post in database (named experience):
years in rank : 5,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
years on tanker : 7,3 (this number is typed in, and then i hoped it will be
updated when time ellaps)
Months onboard this tour of duty : 2,1 (this number i hoped to show
automatic calculated from access using the date this time onboard)
date first time onboard : an date(11.10.08) user input
date this time onboard : an date(11.11.08) user input

(theese two dates is only for making the update, and is not showing in the
report.
The dates is now only in the database, but i can move them if you have a
better sugestion.)

The number format is a requirement, how its going to show on the report.

(the queries are showing info from database from a name selected from the
combobox. Some info is as described and other is plain text or checkbox. This
information shows on the report.)

So its all about having the input "numbers" for years in rank, years on
tanker,
and months onboard to be updated by access after the first time registration.
The date for this time onboard need to be updated by user every time the
person comes onboard.
If possible i want the calculated figures to be displayed in the report only.
brgds
inge
Click to expand...
 
Comments interspersed.

"posts" = "records"? The latter is the more common jargon term.
Good
Click to expand...

Rather than entering 5,3 initially - would it be possible to enter, or
calculate, an "experience start date"? The trouble with storing any duration
value - such as this "years and months of experience" value - in a table is
that (as you are seeing!!) it becomes WRONG over time.

The solution is to *not store the months of experience in your table AT ALL*.

Rather than storing a value which you know will be wrong in a couple of weeks,
and will constantly need updating and tweaking, you can store a *static,
permanent* value from which you can calculate the months of experience
whenever it's needed.



The dates are exactly what you WANT to store in the database. You may want to
"back calculate" the date the rank was attained rather than storing "years in
rank" in a table field.

Don't confuse data STORAGE with data PRESENTATION. They're different. If
you're storing a Text field such as "7,3" in the table... don't. Instead,
store the start date, and use Graham's Diff2Dates() function posted elsewhere
in this thread to dynamically *calculate* the text string "7,3", based on the
stored Date/Time field and today's date.

That's what I'm trying to convey. It is neither necessary NOR appropriate to
"input" numbers for years in rank or years on the tanker or months onboard.
These numbers should *simply not exist anywhere in your table*.

Instead you should store Date/Time fields for the date rank was attained, or
date came aboard, or date registration was attained. This date need not appear
on the report, but it will be used in a Query (using the Diff2Dates function)
in order to dynamically calculate the value appropriate for the report.
 
how can i update the inputs looking like 5,3 or 7,3 using the input numbers
and date? example,.. when a year have ellaps after the first time
registration the 5,3 number should show 6,3 and the 7,3 number should show
8,3. or after 6 months 5,3 should be 5,8 etc.
Click to expand...

Inge, did you ever get this working?
 
Hi john,..

it is working.
But i still use the 5,3 input format, because some personell have experience
from different types of vessels, and im looking for only one type. And they
know
what their experience is. or else i had to put in many dates for making this.

i used the datediff funcsion to make out the days and divided on days in a
month and/or months a year to get the correct format. The format is also a
requirement
on the report. the numbers will only be updated on the reports.

thanx alot again for your input,

brgrds
inge
 
Back
Top