|
|
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
|
|
Hi Inge,
I would suggest that you only store the dates when something happens,
such as the start of being on board and the end of being on board. I would
not go the route of updating the 5,3, 7,3 etc. every so often since they are
out-of-date by, anywhere from the next day to the next 30 days. Well, once a
particular event has completed, they would become static for that event. But
then you still have the problem of how to add them up with other like events
so that you can get the total time for those same events combined. Plus,
will someone remember to correct them when they are correcting erroneous
dates? Better to calculate them on the fly, whether for display on a form
(never hand entered) or for display on a report. Since this is a bit of an
unusual form of time elapsed, you might want to use a custom VBA function
that you can call with the dates whenever you want to compute the value.
Maybe something like:
Public Function ElapsedTime(ByVal dtStart As Date, ByVal dtEnd As Date) _
As String
Dim intMonths As Integer
Dim intTotalMonths As Integer
Dim intYears As Integer
intTotalMonths = DateDiff("m", dtStart, dtEnd)
intYears = intTotalMonths \ 12 ' Using \ for integer result
intMonths = intTotalMonths - intYears * 12
ElapsedTime = intYears & "," & intMonths
End Function
So in your query you might have something like (in design mode):
Years on Tanker: ElapsedTime([Date First Time Onboard], Date())
This is simplified due to your not giving a lot of detail of your
database design. I hope that you are recording each instance of service
separately in a child table. You would then need to do some kind of summary
query to get a total time on board or total time in a certain rank. In which
case you might want to sum up the total months using just the DateDiff()
function and then in your report or form calculate the year,month value. So
your query might look something like this:
select PersonID, Sum(DateDiff("m", [Rank_Start_Date], Nz([Rank_End_Date],
Date()))) As Months_Onboard
from Tours_of_Duty
group by PersonID
The Nz(..., Date()) allows for a blank (null) ending date, which means
the tour of duty is still in progress; therefore use the current date for the
end date. And on your report you might have a calculated field with this for
a control source:
=[Months_Onboard] \ 12 & "," & [Months_Onboard] - ([Months_Onboard] \ 12) * 12
And, if this is used in a lot of places, it would make sense to put it
into a function.
Public Function ElapsedTime2(ByVal intMonths As Integer) As String
Dim intYears As Integer
intYears = intMonths \ 12
ElapsedTime2 = intYears & "," & intMonths - intYears * 12
End Function
Which would simplify the control source of the box to:
=ElapsedTime2([Months_Onboard])
Of course, you may want to be more fine tuned and calculate based on
total days instead of total months since the DateDiff() function with the "m"
option returns integers; no fractions. So someone who was onboard twice for
1 month and 25 days would get reported as being on board for a total of 2
months. Or maybe you could cause it to "round" by using the DateAdd()
function to add 15 days. It all depends on what your goal is.
Hope this helps,
Clifford Bass
"inge" wrote:
[Quoted Text] > 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
|
|
HI clifford,.
Thanx alot for your suggestions, but i finally found a way.
The input is the same as i descibed, cause some officers
have experience from other types of vessels and im looking
for a special type of vessel. The officers know their experience
when the are comming onboard, and the database makes out the
rest from there. So there is the reason for having this (5,3 etc)
numbers instead of a date.
I did use this datediff function and added some. i used the days and made out
the answer in general numbers so it matches the input number format.
i used 30 days a month, so over a ten years period it will be approx 40 days
wrong.
in this case that is ok, cause by that time the officer have most probably
changed vessel. hehe
But i have one more question, its about a textbox. I have a form with 11
comboboxes that will be connected to a criteria in 11 queries. These will
make out the information in 4 different reports. On this form i was thinking
to have this textbox
for making an input to the database. (one textbox for one combobox) the
input will be the date for this time experience record. is that possible? The
textbox will be located alongside with the combobox where the belonging
officer selection is. But this combobox only makes the input for the
belonging query for the report. i have tried this form.myform.textbox but it
will not make the input to the query. in the query, the date is now comming
from the database, and i have to use the inputform to change this date on all
onboard. The Comboboxes get the list from the datebase. and i used the
form.myform.combox to get this into the criteria. Is it becouse i used the
criteria for the combobox, the textbox wouldnt work? I didnt change any other
settings in the textbox or the daterecord in the query. so they are very
plain.
brgds
inge
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> I would suggest that you only store the dates when something happens,
> such as the start of being on board and the end of being on board. I would
> not go the route of updating the 5,3, 7,3 etc. every so often since they are
> out-of-date by, anywhere from the next day to the next 30 days. Well, once a
> particular event has completed, they would become static for that event. But
> then you still have the problem of how to add them up with other like events
> so that you can get the total time for those same events combined. Plus,
> will someone remember to correct them when they are correcting erroneous
> dates? Better to calculate them on the fly, whether for display on a form
> (never hand entered) or for display on a report. Since this is a bit of an
> unusual form of time elapsed, you might want to use a custom VBA function
> that you can call with the dates whenever you want to compute the value.
> Maybe something like:
>
> Public Function ElapsedTime(ByVal dtStart As Date, ByVal dtEnd As Date) _
> As String
>
> Dim intMonths As Integer
> Dim intTotalMonths As Integer
> Dim intYears As Integer
>
> intTotalMonths = DateDiff("m", dtStart, dtEnd)
> intYears = intTotalMonths \ 12 ' Using \ for integer result
> intMonths = intTotalMonths - intYears * 12
>
> ElapsedTime = intYears & "," & intMonths
>
> End Function
>
> So in your query you might have something like (in design mode):
>
> Years on Tanker: ElapsedTime([Date First Time Onboard], Date())
>
> This is simplified due to your not giving a lot of detail of your
> database design. I hope that you are recording each instance of service
> separately in a child table. You would then need to do some kind of summary
> query to get a total time on board or total time in a certain rank. In which
> case you might want to sum up the total months using just the DateDiff()
> function and then in your report or form calculate the year,month value. So
> your query might look something like this:
>
> select PersonID, Sum(DateDiff("m", [Rank_Start_Date], Nz([Rank_End_Date],
> Date()))) As Months_Onboard
> from Tours_of_Duty
> group by PersonID
>
> The Nz(..., Date()) allows for a blank (null) ending date, which means
> the tour of duty is still in progress; therefore use the current date for the
> end date. And on your report you might have a calculated field with this for
> a control source:
>
> =[Months_Onboard] \ 12 & "," & [Months_Onboard] - ([Months_Onboard] \ 12) * 12
>
> And, if this is used in a lot of places, it would make sense to put it
> into a function.
>
> Public Function ElapsedTime2(ByVal intMonths As Integer) As String
>
> Dim intYears As Integer
>
> intYears = intMonths \ 12
>
> ElapsedTime2 = intYears & "," & intMonths - intYears * 12
>
> End Function
>
> Which would simplify the control source of the box to:
>
> =ElapsedTime2([Months_Onboard])
>
> Of course, you may want to be more fine tuned and calculate based on
> total days instead of total months since the DateDiff() function with the "m"
> option returns integers; no fractions. So someone who was onboard twice for
> 1 month and 25 days would get reported as being on board for a total of 2
> months. Or maybe you could cause it to "round" by using the DateAdd()
> function to add 15 days. It all depends on what your goal is.
>
> Hope this helps,
>
> Clifford Bass
>
> "inge" wrote:
>
> > 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
|
|
Hi Inge,
You are welcome.
For your next question. It is possible. If I follow correctly; let me
know if I do not; you want to use a combo box and a text box on a form as the
criteria for a number of queries. In the queries, use
[Forms]![myform]![comboboxname] or [Forms]![myform]![textboxname] as your
criteria. So Forms with an s and exclamation points instead of periods.
With the periods if may be thinking you are comparing to fields in a table.
If that does not work/help, let me know.
Clifford Bass
"inge" wrote:
[Quoted Text] > HI clifford,.
>
> Thanx alot for your suggestions, but i finally found a way.
> The input is the same as i descibed, cause some officers
> have experience from other types of vessels and im looking
> for a special type of vessel. The officers know their experience
> when the are comming onboard, and the database makes out the
> rest from there. So there is the reason for having this (5,3 etc)
> numbers instead of a date.
>
> I did use this datediff function and added some. i used the days and made out
> the answer in general numbers so it matches the input number format.
> i used 30 days a month, so over a ten years period it will be approx 40 days
> wrong.
> in this case that is ok, cause by that time the officer have most probably
> changed vessel. hehe
>
> But i have one more question, its about a textbox. I have a form with 11
> comboboxes that will be connected to a criteria in 11 queries. These will
> make out the information in 4 different reports. On this form i was thinking
> to have this textbox
> for making an input to the database. (one textbox for one combobox) the
> input will be the date for this time experience record. is that possible? The
> textbox will be located alongside with the combobox where the belonging
> officer selection is. But this combobox only makes the input for the
> belonging query for the report. i have tried this form.myform.textbox but it
> will not make the input to the query. in the query, the date is now comming
> from the database, and i have to use the inputform to change this date on all
> onboard. The Comboboxes get the list from the datebase. and i used the
> form.myform.combox to get this into the criteria. Is it becouse i used the
> criteria for the combobox, the textbox wouldnt work? I didnt change any other
> settings in the textbox or the daterecord in the query. so they are very
> plain.
>
> brgds
> inge
|
|
Hi Clifford,..
it is looking as you have described, when using only the criteria from the
combobox,
and the date from the table, it is ok. but when i put this textbox criteria
for the input in the query it is not. i was thinking this, that the combobox
have a list from the table, and the textbox have not. could that be the
reason. cause im trying to make the textbox to make the input in to the query
and therefor it goes in to the table also. is this possible? i just made a
query for only the date, and i cant make that work. i used the "builder"
function, when making the criteria so i hope the markings are correct. but do
i have to do something more. the query record is the date record in the
table. is that a problem? Oh, have also this other textbox on the input form
for the table, can that be the problem?
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> You are welcome.
>
> For your next question. It is possible. If I follow correctly; let me
> know if I do not; you want to use a combo box and a text box on a form as the
> criteria for a number of queries. In the queries, use
> [Forms]![myform]![comboboxname] or [Forms]![myform]![textboxname] as your
> criteria. So Forms with an s and exclamation points instead of periods.
> With the periods if may be thinking you are comparing to fields in a table.
> If that does not work/help, let me know.
>
> Clifford Bass
>
> "inge" wrote:
>
> > HI clifford,.
> >
> > Thanx alot for your suggestions, but i finally found a way.
> > The input is the same as i descibed, cause some officers
> > have experience from other types of vessels and im looking
> > for a special type of vessel. The officers know their experience
> > when the are comming onboard, and the database makes out the
> > rest from there. So there is the reason for having this (5,3 etc)
> > numbers instead of a date.
> >
> > I did use this datediff function and added some. i used the days and made out
> > the answer in general numbers so it matches the input number format.
> > i used 30 days a month, so over a ten years period it will be approx 40 days
> > wrong.
> > in this case that is ok, cause by that time the officer have most probably
> > changed vessel. hehe
> >
> > But i have one more question, its about a textbox. I have a form with 11
> > comboboxes that will be connected to a criteria in 11 queries. These will
> > make out the information in 4 different reports. On this form i was thinking
> > to have this textbox
> > for making an input to the database. (one textbox for one combobox) the
> > input will be the date for this time experience record. is that possible? The
> > textbox will be located alongside with the combobox where the belonging
> > officer selection is. But this combobox only makes the input for the
> > belonging query for the report. i have tried this form.myform.textbox but it
> > will not make the input to the query. in the query, the date is now comming
> > from the database, and i have to use the inputform to change this date on all
> > onboard. The Comboboxes get the list from the datebase. and i used the
> > form.myform.combox to get this into the criteria. Is it becouse i used the
> > criteria for the combobox, the textbox wouldnt work? I didnt change any other
> > settings in the textbox or the daterecord in the query. so they are very
> > plain.
> >
> > brgds
> > inge
|
|
Hi Inge,
Post the SQL text of your query/queries and the names of the combo
boxes and text boxes. That may help me to answer you specifically, which
will probably help you understand how to fit it all together.
Clifford Bass
"inge" wrote:
[Quoted Text] > Hi Clifford,..
>
> it is looking as you have described, when using only the criteria from the
> combobox,
> and the date from the table, it is ok. but when i put this textbox criteria
> for the input in the query it is not. i was thinking this, that the combobox
> have a list from the table, and the textbox have not. could that be the
> reason. cause im trying to make the textbox to make the input in to the query
> and therefor it goes in to the table also. is this possible? i just made a
> query for only the date, and i cant make that work. i used the "builder"
> function, when making the criteria so i hope the markings are correct. but do
> i have to do something more. the query record is the date record in the
> table. is that a problem? Oh, have also this other textbox on the input form
> for the table, can that be the problem?
|
|
i couldnt make out the sql text for the form, but the name is officers
onboard, and the belonging combobox is "master"(list from field "names" in
table), and the textbox is " datemaster". The rest
of the comboboxes and textboxes will be the same configuration. i was
thinking that if make one i could do the rest..
this is the query that belongs to the first, will show on the report together.
the combobox master, also criteria here. datemaster is not. this query is
not completed yet, because im trying to make all work first. im going to add
some more
fields in here, so i only have one query per officer. actually it will be
two with the datequery.
master experience query:
SELECT Experience.Rank, Experience.Nationality, Experience.[Certificate of
Comptency], Experience.[Issuing Country], Experience.[Administration
Acceptance], Experience.[Tanker Certification], Experience.[STCW V Para 1 or
2 for current cargo], Experience.[Radio Qualification], Experience.ywo,
Experience.yir, Experience.yottot, Experience.yoatot, Experience.[Months on
vessel this tour of duty], Experience.[English Proficienty]
FROM Experience
WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
This is the query where i fix the updating for experience. (5,3 etc numbers)
the datemaster textbox, i wanted to put the date in to the [dateduty].
datequery:
SELECT Experience.Name, DateDiff("d",[datefirst],Date())/30/12 AS Years,
DateDiff("d",[dateduty],Date())/30 AS Months, [years]+[ywo] AS ywo1,
[years]+[yir] AS yir1, [years]+[yottot] AS yottot1, [years]+[yoatot] AS
yoatot1, [years]+[yoeapr] AS yoeapr1, [years]+[tyoe] AS tyoe1, [years]+[topv]
AS topv1
FROM Experience
WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
i know the names are funny, but this is my first time doing this. hehe.
i hope this info is ok.
brgrds
inge
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> Post the SQL text of your query/queries and the names of the combo
> boxes and text boxes. That may help me to answer you specifically, which
> will probably help you understand how to fit it all together.
>
> Clifford Bass
>
> "inge" wrote:
>
> > Hi Clifford,..
> >
> > it is looking as you have described, when using only the criteria from the
> > combobox,
> > and the date from the table, it is ok. but when i put this textbox criteria
> > for the input in the query it is not. i was thinking this, that the combobox
> > have a list from the table, and the textbox have not. could that be the
> > reason. cause im trying to make the textbox to make the input in to the query
> > and therefor it goes in to the table also. is this possible? i just made a
> > query for only the date, and i cant make that work. i used the "builder"
> > function, when making the criteria so i hope the markings are correct. but do
> > i have to do something more. the query record is the date record in the
> > table. is that a problem? Oh, have also this other textbox on the input form
> > for the table, can that be the problem?
|
|
Hi Inge,
If I understand correctly you were almost there and this should do it:
SELECT Experience.Name, DateDiff("d",[datefirst],Date())/30/12 AS Years,
DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
[years]+[ywo] AS ywo1,
[years]+[yir] AS yir1, [years]+[yottot] AS yottot1, [years]+[yoatot] AS
yoatot1, [years]+[yoeapr] AS yoeapr1, [years]+[tyoe] AS tyoe1, [years]+[topv]
AS topv1
FROM Experience
WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
If not, let me know.
Clifford Bass
"inge" wrote:
[Quoted Text] > i couldnt make out the sql text for the form, but the name is officers
> onboard, and the belonging combobox is "master"(list from field "names" in
> table), and the textbox is " datemaster". The rest
> of the comboboxes and textboxes will be the same configuration. i was
> thinking that if make one i could do the rest..
>
> this is the query that belongs to the first, will show on the report together.
> the combobox master, also criteria here. datemaster is not. this query is
> not completed yet, because im trying to make all work first. im going to add
> some more
> fields in here, so i only have one query per officer. actually it will be
> two with the datequery.
>
> master experience query:
> SELECT Experience.Rank, Experience.Nationality, Experience.[Certificate of
> Comptency], Experience.[Issuing Country], Experience.[Administration
> Acceptance], Experience.[Tanker Certification], Experience.[STCW V Para 1 or
> 2 for current cargo], Experience.[Radio Qualification], Experience.ywo,
> Experience.yir, Experience.yottot, Experience.yoatot, Experience.[Months on
> vessel this tour of duty], Experience.[English Proficienty]
> FROM Experience
> WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
>
>
> This is the query where i fix the updating for experience. (5,3 etc numbers)
> the datemaster textbox, i wanted to put the date in to the [dateduty].
>
> datequery:
> SELECT Experience.Name, DateDiff("d",[datefirst],Date())/30/12 AS Years,
> DateDiff("d",[dateduty],Date())/30 AS Months, [years]+[ywo] AS ywo1,
> [years]+[yir] AS yir1, [years]+[yottot] AS yottot1, [years]+[yoatot] AS
> yoatot1, [years]+[yoeapr] AS yoeapr1, [years]+[tyoe] AS tyoe1, [years]+[topv]
> AS topv1
> FROM Experience
> WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
>
> i know the names are funny, but this is my first time doing this. hehe.
> i hope this info is ok.
>
> brgrds
> inge
|
|
Hi Clifford,..
It is working perfectly.. Maaaaaany,many thanx.
May i bother you with another question aswell....?
There is one selection in the officers onboard form that may not be used
every time.
so i was wondering how i could make this "disappear" on the report when
there is
not any selected there.
the query on for the report will be the same as earlier, datequery and the
master query. only diff will be the names on the queries.
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> If I understand correctly you were almost there and this should do it:
>
> SELECT Experience.Name, DateDiff("d",[datefirst],Date())/30/12 AS Years,
> DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
> [years]+[ywo] AS ywo1,
> [years]+[yir] AS yir1, [years]+[yottot] AS yottot1, [years]+[yoatot] AS
> yoatot1, [years]+[yoeapr] AS yoeapr1, [years]+[tyoe] AS tyoe1, [years]+[topv]
> AS topv1
> FROM Experience
> WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
>
> If not, let me know.
>
> Clifford Bass
>
> "inge" wrote:
>
> > i couldnt make out the sql text for the form, but the name is officers
> > onboard, and the belonging combobox is "master"(list from field "names" in
> > table), and the textbox is " datemaster". The rest
> > of the comboboxes and textboxes will be the same configuration. i was
> > thinking that if make one i could do the rest..
> >
> > this is the query that belongs to the first, will show on the report together.
> > the combobox master, also criteria here. datemaster is not. this query is
> > not completed yet, because im trying to make all work first. im going to add
> > some more
> > fields in here, so i only have one query per officer. actually it will be
> > two with the datequery.
> >
> > master experience query:
> > SELECT Experience.Rank, Experience.Nationality, Experience.[Certificate of
> > Comptency], Experience.[Issuing Country], Experience.[Administration
> > Acceptance], Experience.[Tanker Certification], Experience.[STCW V Para 1 or
> > 2 for current cargo], Experience.[Radio Qualification], Experience.ywo,
> > Experience.yir, Experience.yottot, Experience.yoatot, Experience.[Months on
> > vessel this tour of duty], Experience.[English Proficienty]
> > FROM Experience
> > WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
> >
> >
> > This is the query where i fix the updating for experience. (5,3 etc numbers)
> > the datemaster textbox, i wanted to put the date in to the [dateduty].
> >
> > datequery:
> > SELECT Experience.Name, DateDiff("d",[datefirst],Date())/30/12 AS Years,
> > DateDiff("d",[dateduty],Date())/30 AS Months, [years]+[ywo] AS ywo1,
> > [years]+[yir] AS yir1, [years]+[yottot] AS yottot1, [years]+[yoatot] AS
> > yoatot1, [years]+[yoeapr] AS yoeapr1, [years]+[tyoe] AS tyoe1, [years]+[topv]
> > AS topv1
> > FROM Experience
> > WHERE (((Experience.Name)=[Forms]![Officers onboard]![master]));
> >
> > i know the names are funny, but this is my first time doing this. hehe.
> > i hope this info is ok.
> >
> > brgrds
> > inge
|
|
Hi Inge,
You are very welcome!
Sure you can ask. Again, let me make sure I understand. If say there
is a ship type combo box named cbShipType on your Officers onboard form and
someone can select say tugboats you want the report to give only tugboats.
But if they do not select anything you want all ship types. One way to do
that would be to use this in your where clause:
where ... and [Experience].[ShipType] = Nz([Forms]![Officers
onboard]![cbShipType], [Experience].[ShipType]) and ...
What this does is tell it to compare ShipType in Experience to the
combo box value if there is a value, or to itself if there is no value in the
combo box. So when there is no selection, the comparison of ShipType to
itself will result in true for all records with one exception. That
exception is if ShipType can be null. Since comparing null to null results
in false, you will need to do something like the following when ShipType can
be null:
where ... and Nz([Experience].[ShipType], "") = Nz([Forms]![Officers
onboard]![cbShipType], Nz([Experience].[ShipType], "")) and ...
This will in convert the nulls to zero-length strings, which will be
equal and therefore result in all records.
Hope that answers your question,
Clifford Bass
"inge" wrote:
[Quoted Text] > Hi Clifford,..
>
> It is working perfectly.. Maaaaaany,many thanx.
>
> May i bother you with another question aswell....?
> There is one selection in the officers onboard form that may not be used
> every time.
> so i was wondering how i could make this "disappear" on the report when
> there is
> not any selected there.
>
> the query on for the report will be the same as earlier, datequery and the
> master query. only diff will be the names on the queries.
|
|
Hi Clifford,..
I think we forget the last question for now.....
Cause i was wondering if i could get all records into 1 query.
i have tried this and all was ok, accept for the problem i had with
the [datemaster] thing. I get the same date for all the records
in the query. (offcourse i do). So i was wondering how i could make the query
use the correct formula there. i have tried to add another criteria
[datechoff] but then there was nothing. what i did is to put the
form.userform.combobox into the "name" field, in the OR field. The only thing
that is not correct is the formula
with the [datemaster] in. (it still works perfectly, when single.)
Also, I was wondering about this search function. I have put in this search
function in the input form. But all i want to do is to find a record in the
database and display info in the form. But in the search form displaying you
can do more.
I used command button with the wizard. So how can i make it only search for
names, first name, lastname or both. i know it can do both but i want to
"loose" the rest if possible.
Here's the query : i tried to delete many fields or else it will take some
pages to read.
SELECT DateDiff("d",[datefirst],Date())/30/12 AS Years,
DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
[years]+[ywo] AS ywo1, Experience.ywo, [years]+[yir] AS yir1, Experience.yir,
[years]+[yottot] AS yottot1, Experience.yottot, [years]+[yoatot] AS yoatot1,
Experience.yoatot, Experience.[Months on vessel this tour of duty], FROM
Experience
WHERE (((Experience.Name)=[Forms]![Officers onboard]![master] Or
(Experience.Name)=[Forms]![Officers onboard]![Choff] Or
(Experience.Name)=[Forms]![Officers onboard]![Choff jr]));
As you can see, i have made 3 criterias. The beloning text boxes will be
datechoff and datechoffjr.(accept for datemaster) I hope i didnt delete to
much of the info.
Brgrds
Inge
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> You are very welcome!
>
> Sure you can ask. Again, let me make sure I understand. If say there
> is a ship type combo box named cbShipType on your Officers onboard form and
> someone can select say tugboats you want the report to give only tugboats.
> But if they do not select anything you want all ship types. One way to do
> that would be to use this in your where clause:
>
> where ... and [Experience].[ShipType] = Nz([Forms]![Officers
> onboard]![cbShipType], [Experience].[ShipType]) and ...
>
> What this does is tell it to compare ShipType in Experience to the
> combo box value if there is a value, or to itself if there is no value in the
> combo box. So when there is no selection, the comparison of ShipType to
> itself will result in true for all records with one exception. That
> exception is if ShipType can be null. Since comparing null to null results
> in false, you will need to do something like the following when ShipType can
> be null:
>
> where ... and Nz([Experience].[ShipType], "") = Nz([Forms]![Officers
> onboard]![cbShipType], Nz([Experience].[ShipType], "")) and ...
>
> This will in convert the nulls to zero-length strings, which will be
> equal and therefore result in all records.
>
> Hope that answers your question,
>
> Clifford Bass
>
> "inge" wrote:
>
> > Hi Clifford,..
> >
> > It is working perfectly.. Maaaaaany,many thanx.
> >
> > May i bother you with another question aswell....?
> > There is one selection in the officers onboard form that may not be used
> > every time.
> > so i was wondering how i could make this "disappear" on the report when
> > there is
> > not any selected there.
> >
> > the query on for the report will be the same as earlier, datequery and the
> > master query. only diff will be the names on the queries.
|
|
Hi again, clifford,....
it is because i have been trying here. and it is ok, accept for the
datemaster thing.
when single it is ok. But when i add to it, it is not. i have now made a
query for every officer, but my report is funny. i've made a report, but all
is comming on one page by it self. And i want to have everything on one page.
text to left, and info right of that. On my selection. I only get my
selection on a colom. and when i try to change, i only get to delete a col,
or all. i want to move a part.... thats it....?
i also have this, cause ive been reading a book called access for dummies,
and maybe i should make a relation database instead of a plain database. i
have prox 40 inputs to my database page. do you think it would be better if i
make a relation database? Also for making the reporst?
brgrds inge
"inge" wrote:
[Quoted Text] > Hi Clifford,..
>
> I think we forget the last question for now.....
>
> Cause i was wondering if i could get all records into 1 query.
> i have tried this and all was ok, accept for the problem i had with
> the [datemaster] thing. I get the same date for all the records
> in the query. (offcourse i do). So i was wondering how i could make the query
> use the correct formula there. i have tried to add another criteria
> [datechoff] but then there was nothing. what i did is to put the
> form.userform.combobox into the "name" field, in the OR field. The only thing
> that is not correct is the formula
> with the [datemaster] in. (it still works perfectly, when single.)
>
> Also, I was wondering about this search function. I have put in this search
> function in the input form. But all i want to do is to find a record in the
> database and display info in the form. But in the search form displaying you
> can do more.
> I used command button with the wizard. So how can i make it only search for
> names, first name, lastname or both. i know it can do both but i want to
> "loose" the rest if possible.
>
> Here's the query : i tried to delete many fields or else it will take some
> pages to read.
>
> SELECT DateDiff("d",[datefirst],Date())/30/12 AS Years,
> DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
> [years]+[ywo] AS ywo1, Experience.ywo, [years]+[yir] AS yir1, Experience.yir,
> [years]+[yottot] AS yottot1, Experience.yottot, [years]+[yoatot] AS yoatot1,
> Experience.yoatot, Experience.[Months on vessel this tour of duty], FROM
> Experience
> WHERE (((Experience.Name)=[Forms]![Officers onboard]![master] Or
> (Experience.Name)=[Forms]![Officers onboard]![Choff] Or
> (Experience.Name)=[Forms]![Officers onboard]![Choff jr]));
>
> As you can see, i have made 3 criterias. The beloning text boxes will be
> datechoff and datechoffjr.(accept for datemaster) I hope i didnt delete to
> much of the info.
>
> Brgrds
> Inge
>
>
> "Clifford Bass" wrote:
>
> > Hi Inge,
> >
> > You are very welcome!
> >
> > Sure you can ask. Again, let me make sure I understand. If say there
> > is a ship type combo box named cbShipType on your Officers onboard form and
> > someone can select say tugboats you want the report to give only tugboats.
> > But if they do not select anything you want all ship types. One way to do
> > that would be to use this in your where clause:
> >
> > where ... and [Experience].[ShipType] = Nz([Forms]![Officers
> > onboard]![cbShipType], [Experience].[ShipType]) and ...
> >
> > What this does is tell it to compare ShipType in Experience to the
> > combo box value if there is a value, or to itself if there is no value in the
> > combo box. So when there is no selection, the comparison of ShipType to
> > itself will result in true for all records with one exception. That
> > exception is if ShipType can be null. Since comparing null to null results
> > in false, you will need to do something like the following when ShipType can
> > be null:
> >
> > where ... and Nz([Experience].[ShipType], "") = Nz([Forms]![Officers
> > onboard]![cbShipType], Nz([Experience].[ShipType], "")) and ...
> >
> > This will in convert the nulls to zero-length strings, which will be
> > equal and therefore result in all records.
> >
> > Hope that answers your question,
> >
> > Clifford Bass
> >
> > "inge" wrote:
> >
> > > Hi Clifford,..
> > >
> > > It is working perfectly.. Maaaaaany,many thanx.
> > >
> > > May i bother you with another question aswell....?
> > > There is one selection in the officers onboard form that may not be used
> > > every time.
> > > so i was wondering how i could make this "disappear" on the report when
> > > there is
> > > not any selected there.
> > >
> > > the query on for the report will be the same as earlier, datequery and the
> > > master query. only diff will be the names on the queries.
|
|
Hi Inge,
The design does make a huge difference. From what you are describing,
you would normally use a number of tables. So taking a step back is probably
a good idea. Are you using one table or several? How about posting your
table(s) and fields. You don't have to get too detailed. Things like the
various name and address fields can be lumped into a single "names and
address". Then either I can suggest a redesign or some of it will make more
sense to me. But do indicate if you are storing the same kind of thing in
multiple fields such as StartDate1, StartDate2, StartDate3...
Clifford Bass
"inge" wrote:
[Quoted Text] > Hi again, clifford,....
>
> it is because i have been trying here. and it is ok, accept for the
> datemaster thing.
> when single it is ok. But when i add to it, it is not. i have now made a
> query for every officer, but my report is funny. i've made a report, but all
> is comming on one page by it self. And i want to have everything on one page.
> text to left, and info right of that. On my selection. I only get my
> selection on a colom. and when i try to change, i only get to delete a col,
> or all. i want to move a part.... thats it....?
>
> i also have this, cause ive been reading a book called access for dummies,
> and maybe i should make a relation database instead of a plain database. i
> have prox 40 inputs to my database page. do you think it would be better if i
> make a relation database? Also for making the reporst?
>
> brgrds inge
>
> "inge" wrote:
>
> > Hi Clifford,..
> >
> > I think we forget the last question for now.....
> >
> > Cause i was wondering if i could get all records into 1 query.
> > i have tried this and all was ok, accept for the problem i had with
> > the [datemaster] thing. I get the same date for all the records
> > in the query. (offcourse i do). So i was wondering how i could make the query
> > use the correct formula there. i have tried to add another criteria
> > [datechoff] but then there was nothing. what i did is to put the
> > form.userform.combobox into the "name" field, in the OR field. The only thing
> > that is not correct is the formula
> > with the [datemaster] in. (it still works perfectly, when single.)
> >
> > Also, I was wondering about this search function. I have put in this search
> > function in the input form. But all i want to do is to find a record in the
> > database and display info in the form. But in the search form displaying you
> > can do more.
> > I used command button with the wizard. So how can i make it only search for
> > names, first name, lastname or both. i know it can do both but i want to
> > "loose" the rest if possible.
> >
> > Here's the query : i tried to delete many fields or else it will take some
> > pages to read.
> >
> > SELECT DateDiff("d",[datefirst],Date())/30/12 AS Years,
> > DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
> > [years]+[ywo] AS ywo1, Experience.ywo, [years]+[yir] AS yir1, Experience.yir,
> > [years]+[yottot] AS yottot1, Experience.yottot, [years]+[yoatot] AS yoatot1,
> > Experience.yoatot, Experience.[Months on vessel this tour of duty], FROM
> > Experience
> > WHERE (((Experience.Name)=[Forms]![Officers onboard]![master] Or
> > (Experience.Name)=[Forms]![Officers onboard]![Choff] Or
> > (Experience.Name)=[Forms]![Officers onboard]![Choff jr]));
> >
> > As you can see, i have made 3 criterias. The beloning text boxes will be
> > datechoff and datechoffjr.(accept for datemaster) I hope i didnt delete to
> > much of the info.
> >
> > Brgrds
> > Inge
|
|
Hi Clifford,..
My database is a plain database. (1 table) Fields would be like Names,
Different dates, numbers(diff experience, like 5,3 before this vessel), check
boxes(many,diff courses), And some is info about different certificates,
language, nationality etc.(by valuelist in the input form)
So i guess there is alot of "same" info but for different things.
The checkboxes is only if the person have this course or not
Some info would be like, how many times have you connected with a special
bow loading system or things like that.
one date is for when they came onboard first time, so i can make out the
experience after that.
others is for last time they had a special course.
i have 1 inputform for all the info they need to put in.
i have 1 form to select who's onboard and the date they came onboard this
time of duty.
else i have a query that fix the update for the reports(experience) and
working on a query that (i hope) to update a field in the database so i can
get
the last update. that would be the time onboard this tour of duty.
In that query i have only two fields where i select whos onboard and tries
to update
the date field i need to get the update in the table. This is the problem
now.(this would be the same as the datmaster thing i had, but im trying to
get several inputs. i need 11 inputs to make all officers. so i was thinking
if i could update the field in the table [dateduty] the rest is no problem.)
After this i have to sort out how its going to show, and can only use 1
field, and this is by rank. (or the captain gets soure.... hehe)
Later comes the report stuff :)
im doing this on my time off, so my company should pay me more... hehe.
brgrds
inge
Is this ok?
I dont know how to copy every thing in here.
"Clifford Bass" wrote:
[Quoted Text] > Hi Inge,
>
> The design does make a huge difference. From what you are describing,
> you would normally use a number of tables. So taking a step back is probably
> a good idea. Are you using one table or several? How about posting your
> table(s) and fields. You don't have to get too detailed. Things like the
> various name and address fields can be lumped into a single "names and
> address". Then either I can suggest a redesign or some of it will make more
> sense to me. But do indicate if you are storing the same kind of thing in
> multiple fields such as StartDate1, StartDate2, StartDate3...
>
> Clifford Bass
>
> "inge" wrote:
>
> > Hi again, clifford,....
> >
> > it is because i have been trying here. and it is ok, accept for the
> > datemaster thing.
> > when single it is ok. But when i add to it, it is not. i have now made a
> > query for every officer, but my report is funny. i've made a report, but all
> > is comming on one page by it self. And i want to have everything on one page.
> > text to left, and info right of that. On my selection. I only get my
> > selection on a colom. and when i try to change, i only get to delete a col,
> > or all. i want to move a part.... thats it....?
> >
> > i also have this, cause ive been reading a book called access for dummies,
> > and maybe i should make a relation database instead of a plain database. i
> > have prox 40 inputs to my database page. do you think it would be better if i
> > make a relation database? Also for making the reporst?
> >
> > brgrds inge
> >
> > "inge" wrote:
> >
> > > Hi Clifford,..
> > >
> > > I think we forget the last question for now.....
> > >
> > > Cause i was wondering if i could get all records into 1 query.
> > > i have tried this and all was ok, accept for the problem i had with
> > > the [datemaster] thing. I get the same date for all the records
> > > in the query. (offcourse i do). So i was wondering how i could make the query
> > > use the correct formula there. i have tried to add another criteria
> > > [datechoff] but then there was nothing. what i did is to put the
> > > form.userform.combobox into the "name" field, in the OR field. The only thing
> > > that is not correct is the formula
> > > with the [datemaster] in. (it still works perfectly, when single.)
> > >
> > > Also, I was wondering about this search function. I have put in this search
> > > function in the input form. But all i want to do is to find a record in the
> > > database and display info in the form. But in the search form displaying you
> > > can do more.
> > > I used command button with the wizard. So how can i make it only search for
> > > names, first name, lastname or both. i know it can do both but i want to
> > > "loose" the rest if possible.
> > >
> > > Here's the query : i tried to delete many fields or else it will take some
> > > pages to read.
> > >
> > > SELECT DateDiff("d",[datefirst],Date())/30/12 AS Years,
> > > DateDiff("d",[Forms]![Officers onboard]![datemaster],Date())/30 AS Months,
> > > [years]+[ywo] AS ywo1, Experience.ywo, [years]+[yir] AS yir1, Experience.yir,
> > > [years]+[yottot] AS yottot1, Experience.yottot, [years]+[yoatot] AS yoatot1,
> > > Experience.yoatot, Experience.[Months on vessel this tour of duty], FROM
> > > Experience
> > > WHERE (((Experience.Name)=[Forms]![Officers onboard]![master] Or
> > > (Experience.Name)=[Forms]![Officers onboard]![Choff] Or
> > > (Experience.Name)=[Forms]![Officers onboard]![Choff jr]));
> > >
> > > As you can see, i have made 3 criterias. The beloning text boxes will be
> > > datechoff and datechoffjr.(accept for datemaster) I hope i didnt delete to
> > > much of the info.
> > >
> > > Brgrds
> > > Inge
|
|
Hi Inge,
You system desparately needs to be redesigned before you do anything
else. One table for the kinds of things you are storing will lead to lots of
data problems down the road. Plus, updating the same rows to hold current
information means that you can lose the information that was there. That
could be a huge issue if half a year from now you need to know the status of
something in the past and it is gone. Better usually to add a new row of data
to indicate the current status when there is a status change. Although you
could have a row with a start date and an end date, leaving the end date
blank until the status actually changes. Of course you could have an
expected end date that is filled in right away and an actual end date that is
filled in when it actually happens.
In looking at what you are dealing with I can list a number of tables
that you probably should have:
1) A people table that lists stuff specific to people such as ID number,
names, birthdates, etc. It could include current address if you only ever
need the current address.
2) A vessels table.
3) Possibly a languages code table.
4) A person-languages table.
5) A courses table.
6) A person-courses table.
7) A certificates table.
8) A person-certificates table.
9) A countries table.
10) A person-nationalities table.
11) An experience type table.
12) A person-experience table.
13) If experience and rank are not the same, then you will need a separate
rank table.
14) Likewise a person-rank table.
There probably are other tables you will need. Basically, anything
that is the same for more than one record, such as a person, should be in its
own table. And anything that repeats, but is different, such as the
languages a person knows, should be in a child table.
Several places to start getting up-to-speed on database design would be
<http://www.mvps.org/access/resources/index.html>,
<http://www.accessmvp.com/JConrad/accessjunkie/resources.html> or the
database section of the computer section of your local bookstore.
It is unfortuate you are doing this on your off time. It really
involved some serious and significant work.
Hope this helps,
Clifford Bass
"inge" wrote:
[Quoted Text] > Hi Clifford,..
> My database is a plain database. (1 table) Fields would be like Names,
> Different dates, numbers(diff experience, like 5,3 before this vessel), check
> boxes(many,diff courses), And some is info about different certificates,
> language, nationality etc.(by valuelist in the input form)
>
> So i guess there is alot of "same" info but for different things.
>
> The checkboxes is only if the person have this course or not
> Some info would be like, how many times have you connected with a special
> bow loading system or things like that.
>
> one date is for when they came onboard first time, so i can make out the
> experience after that.
> others is for last time they had a special course.
>
> i have 1 inputform for all the info they need to put in.
>
> i have 1 form to select who's onboard and the date they came onboard this
> time of duty.
>
> else i have a query that fix the update for the reports(experience) and
> working on a query that (i hope) to update a field in the database so i can
> get
> the last update. that would be the time onboard this tour of duty.
> In that query i have only two fields where i select whos onboard and tries
> to update
> the date field i need to get the update in the table. This is the problem
> now.(this would be the same as the datmaster thing i had, but im trying to
> get several inputs. i need 11 inputs to make all officers. so i was thinking
> if i could update the field in the table [dateduty] the rest is no problem.)
> After this i have to sort out how its going to show, and can only use 1
> field, and this is by rank. (or the captain gets soure.... hehe)
>
> Later comes the report stuff :)
> im doing this on my time off, so my company should pay me more... hehe.
>
> brgrds
> inge
> Is this ok?
> I dont know how to copy every thing in here.
|
|
|