Expression for Calculated field (with Dates)

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

I've got a calculated field that I would like to include
in a report, so I'm wonding if anyone knows how to build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval, which
is an integer between 0 and 12, that equals the number of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
 
You can use a subquery to retrieve the most date a machine was last
serviced. Type something like this into a fresh column of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID = MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])
 
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a machine was last
serviced. Type something like this into a fresh column of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID = MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
I've got a calculated field that I would like to include
in a report, so I'm wonding if anyone knows how to build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval, which
is an integer between 0 and 12, that equals the number of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...


.
Click to expand...
 
Yes, the expression goes into the Field row (assuming you want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the LastServiced date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be two records for the same
piece of machinery on the same date, the subquery would return two records
which would cause an error. To prevent this, give Access some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table in the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and you have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a machine was last
serviced. Type something like this into a fresh column of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID = MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
I've got a calculated field that I would like to include
in a report, so I'm wonding if anyone knows how to build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval, which
is an integer between 0 and 12, that equals the number of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...
Click to expand...
Click to expand...
 
I'm also going to have to stick a WHERE clause into this
subquery, and have no idea as to where to put it. I
should only be performing the next scheduled maintenance
calculation against a maintenance type that has the value
of 9, 10, or 11 (if I calculate against dates for any
other type, I'll be getting the wrong information).

-----Original Message-----
Yes, the expression goes into the Field row (assuming you want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the LastServiced date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be two records for the same
piece of machinery on the same date, the subquery would return two records
which would cause an error. To prevent this, give Access some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table in the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and you have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a machine was last
serviced. Type something like this into a fresh column of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID = MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've got a calculated field that I would like to include
in a report, so I'm wonding if anyone knows how to build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval, which
is an integer between 0 and 12, that equals the
Click to expand...
number
of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...
Click to expand...


.
Click to expand...
 
Here's what I tried to use as a new subquery, but it
doesn't work:

SELECT TOP 1 ServiceDate FROM tblServiceRecords WHERE
tblServiceRecords.ServiceTypeID = 9 Or
tblServiceRecords.ServiceTypeID = 10 Or
tblServiceRecords.ServiceTypeID = 11 And
tblServiceRecords.EqID = tblEquipment.EqID ORDER BY
ServiceDate DESC, [EqID]

Am I getting any warmer?
-----Original Message-----
Yes, the expression goes into the Field row (assuming you want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the LastServiced date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be two records for the same
piece of machinery on the same date, the subquery would return two records
which would cause an error. To prevent this, give Access some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table in the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and you have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a machine was last
serviced. Type something like this into a fresh column of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID = MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've got a calculated field that I would like to include
in a report, so I'm wonding if anyone knows how to build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval, which
is an integer between 0 and 12, that equals the
Click to expand...
number
of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...
Click to expand...


.
Click to expand...
 
The order by clause needs a comma between the fields. The brackets don't
quite match. You have a field in the subquery table that has the same name
as one in the main query table, so try:
ORDER BY ServiceDate DESC, tblServiceRecords.EqID

You may be able to use [LastMaintained] in another field, but you may need
to repeat the expression.

If a machine has no previous record, the subquery will return Null. Use Nz()
to specify a value where none was returned. It's always worth explicitly
typecasting calculated values, so you will finish up with something like
this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m", [Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE tblServiceRecords.EqID = tblEquipment.EqID
ORDER BY ServiceDate DESC, tblServiceRecords.EqID ) ), Date()))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
That's great. I've now got an expression that returns the
date the machine was last maintained:

LastMaintained: (SELECT TOP 1 ServiceDate FROM
tblServiceRecords WHERE tblServiceRecords.EqID =
tblEquipment.EqID ORDER BY ServiceDate DESC (EqID] )

and am able to return that field for the query. This is
one of the fields I need in the query, and it's working
fine.

I still need to calculate the next scheduled maintenance
date, and am having problems with the DateAdd expression.
I figured now that I am returning the last maintenance
date in the query (using the above expression), that I
could just create a new field and enter the expression:

NextScheduledMaintenance: DateAdd ("m", [Interval],
[LastMaintained])

with LastMaintained being the value returned by the
expression in the preceeding field.

I'm getting a syntax error, and don't know whether it's
because I've typed something wrong, or whether I need to
replace the [LastMaintained] part with something else.

Help?
-----Original Message-----
Yes, the expression goes into the Field row (assuming you want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the LastServiced date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be two records for the same
piece of machinery on the same date, the subquery would return two records
which would cause an error. To prevent this, give Access some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table in the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and you have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows how to
build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which
are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval,
which
is an integer between 0 and 12, that equals the number
of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...


.
Click to expand...
Click to expand...
 
Where you have AND and OR clauses mixed, use brackets.

I think you want the record where (it's 9 or 10 or 11) AND ALSO it matches
the record in the parent query, so you could use:
WHERE (xx=9 OR xx=10 OR xx=11) AND ...

Note that if ServiceTypeID is a Text type field (not Number), you need quote
marks around the values:
WHERE (xx='9' OR xx='10' OR xx='11') AND ...

The IN operator might be easier. Something like this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m", [Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE (tblServiceRecords.EqID = tblEquipment.EqID)
AND (tblServiceRecords.ServiceTypeID IN (9, 10, 11))
ORDER BY ServiceDate DESC, tblServiceRecords.EqID ) ), Date()))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Here's what I tried to use as a new subquery, but it
doesn't work:

SELECT TOP 1 ServiceDate FROM tblServiceRecords WHERE
tblServiceRecords.ServiceTypeID = 9 Or
tblServiceRecords.ServiceTypeID = 10 Or
tblServiceRecords.ServiceTypeID = 11 And
tblServiceRecords.EqID = tblEquipment.EqID ORDER BY
ServiceDate DESC, [EqID]

Am I getting any warmer?
-----Original Message-----
Yes, the expression goes into the Field row (assuming you want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the LastServiced date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be two records for the same
piece of machinery on the same date, the subquery would return two records
which would cause an error. To prevent this, give Access some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table in the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and you have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows how to
build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which
are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub table of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled maintenance date
by comparing it to the equipment service interval,
which
is an integer between 0 and 12, that equals the number
of
months after a piece of equipment is maintained that it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had much luck
with as of yet, so if you have any pointers there, that
would be great.

Any suggestions?

Scott
Click to expand...
Click to expand...
Click to expand...
 
Allen,

Thanks so much for following along with this. I've been
able to get the LastMaintained field to return the
correct data in the query, but the
NextScheduledMaintenance is still killing me. I've tried
adding and removing every comma, parens, you name it, but
still get a syntax error.

I've had so much trouble with this one that I've
simplified the table structure - creating a new
MaintenanceRecords table to eliminate having to select
matches based on the type field. I don't even care if the
field is null or not - if the query doesn't return a
value, I'm going to want to calculate the date off of
another field anyway...

I just know there has to be a way to get this to work
right, but I have no experience with SQL!

Right now I'm working with this statement:

NextScheduledMaintenance: DateAdd ("m",
[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate FROM
tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID ) )

I have done several variations on this one and get a
syntax error each time (missing operand/operator,
character/comma, or quotation mark...)

wondering what is wrong...

do I need to designate the table MaintenanceInterval
comes from (tblEquipment)? Is there something wrong with
the type of quotation marks I'm using? I've tried just
about everything - help?

Scott
-----Original Message-----
The order by clause needs a comma between the fields. The brackets don't
quite match. You have a field in the subquery table that has the same name
as one in the main query table, so try:
ORDER BY ServiceDate DESC, tblServiceRecords.EqID

You may be able to use [LastMaintained] in another field, but you may need
to repeat the expression.

If a machine has no previous record, the subquery will return Null. Use Nz()
to specify a value where none was returned. It's always worth explicitly
typecasting calculated values, so you will finish up with something like
this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m", [Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE tblServiceRecords.EqID = tblEquipment.EqID
ORDER BY ServiceDate DESC, tblServiceRecords.EqID ) ), Date()))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
That's great. I've now got an expression that returns the
date the machine was last maintained:

LastMaintained: (SELECT TOP 1 ServiceDate FROM
tblServiceRecords WHERE tblServiceRecords.EqID =
tblEquipment.EqID ORDER BY ServiceDate DESC (EqID] )

and am able to return that field for the query. This is
one of the fields I need in the query, and it's working
fine.

I still need to calculate the next scheduled maintenance
date, and am having problems with the DateAdd expression.
I figured now that I am returning the last maintenance
date in the query (using the above expression), that I
could just create a new field and enter the expression:

NextScheduledMaintenance: DateAdd ("m", [Interval],
[LastMaintained])

with LastMaintained being the value returned by the
expression in the preceeding field.

I'm getting a syntax error, and don't know whether it's
because I've typed something wrong, or whether I need to
replace the [LastMaintained] part with something else.

Help?
-----Original Message-----
Yes, the expression goes into the Field row (assuming
Click to expand...
you
want a calculated
field as a result). You can place the DateAdd() around the subquery
expression if you don't need to display the
Click to expand...
LastServiced
date.
The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate field. That's how it
picks the most recent record. If there happen to be
Click to expand...
two
records for the same
piece of machinery on the same date, the subquery
Click to expand...
would
return two records
which would cause an error. To prevent this, give
Click to expand...
Access
some way to
differentiate between the two and decide which one to return. A simple way
to do that is to include the primary key of the table
Click to expand...
in
the subquery as the
2nd field of the ORDER BY clause: since the p.k. is guaranteed to be unique,
Access can now choose just one record to return, and
Click to expand...
you
have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Allen,

Thanks for the response.

Where do I put the expression that adds the months. Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows how to
build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which
are
in a related table, associated with an equipment table,
many to one, or [DeliveryDate], which is a sub
Click to expand...
table
of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled
Click to expand...
maintenance
date
by comparing it to the equipment service interval,
which
is an integer between 0 and 12, that equals the number
of
months after a piece of equipment is maintained
Click to expand...
that
it
should be maintained again. I'm assuming this involves
returning the month part, which I haven't had
Click to expand...
much
luck
with as of yet, so if you have any pointers
Click to expand...
there,
that
would be great.

Any suggestions?

Scott


.
Click to expand...
Click to expand...


.
Click to expand...
 
Scott, I've just created your tables, and it works perfectly.

What I have is:
tblEquipment:
-------------
EqID AutoNumber primary key
MaintenanceInterval Number number of months.

tblMaintenanceRecords
-----------------------
MaintenanceRecordsID AutoNumber p.k.
EqID Number foreign key.
MaintenanceDate Date/Time when serviced.

Query:
------
SELECT tblEquipment.EqID, tblEquipment.MaintenanceInterval,
DateAdd("m",[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate
FROM tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID )) AS NextScheduledMaintenance
FROM tblEquipment;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks so much for following along with this. I've been
able to get the LastMaintained field to return the
correct data in the query, but the
NextScheduledMaintenance is still killing me. I've tried
adding and removing every comma, parens, you name it, but
still get a syntax error.

I've had so much trouble with this one that I've
simplified the table structure - creating a new
MaintenanceRecords table to eliminate having to select
matches based on the type field. I don't even care if the
field is null or not - if the query doesn't return a
value, I'm going to want to calculate the date off of
another field anyway...

I just know there has to be a way to get this to work
right, but I have no experience with SQL!

Right now I'm working with this statement:

NextScheduledMaintenance: DateAdd ("m",
[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate FROM
tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID ) )

I have done several variations on this one and get a
syntax error each time (missing operand/operator,
character/comma, or quotation mark...)

wondering what is wrong...

do I need to designate the table MaintenanceInterval
comes from (tblEquipment)? Is there something wrong with
the type of quotation marks I'm using? I've tried just
about everything - help?

Scott
-----Original Message-----
The order by clause needs a comma between the fields. The brackets don't
quite match. You have a field in the subquery table that has the same name
as one in the main query table, so try:
ORDER BY ServiceDate DESC, tblServiceRecords.EqID

You may be able to use [LastMaintained] in another field, but you may need
to repeat the expression.

If a machine has no previous record, the subquery will return Null. Use Nz()
to specify a value where none was returned. It's always worth explicitly
typecasting calculated values, so you will finish up with something like
this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m", [Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE tblServiceRecords.EqID = tblEquipment.EqID
ORDER BY ServiceDate DESC, tblServiceRecords.EqID ) ), Date()))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
That's great. I've now got an expression that returns the
date the machine was last maintained:

LastMaintained: (SELECT TOP 1 ServiceDate FROM
tblServiceRecords WHERE tblServiceRecords.EqID =
tblEquipment.EqID ORDER BY ServiceDate DESC (EqID] )

and am able to return that field for the query. This is
one of the fields I need in the query, and it's working
fine.

I still need to calculate the next scheduled maintenance
date, and am having problems with the DateAdd expression.
I figured now that I am returning the last maintenance
date in the query (using the above expression), that I
could just create a new field and enter the expression:

NextScheduledMaintenance: DateAdd ("m", [Interval],
[LastMaintained])

with LastMaintained being the value returned by the
expression in the preceeding field.

I'm getting a syntax error, and don't know whether it's
because I've typed something wrong, or whether I need to
replace the [LastMaintained] part with something else.

Help?
-----Original Message-----
Yes, the expression goes into the Field row (assuming you
want a calculated
field as a result). You can place the DateAdd() around
the subquery
expression if you don't need to display the LastServiced
date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate
field. That's how it
picks the most recent record. If there happen to be two
records for the same
piece of machinery on the same date, the subquery would
return two records
which would cause an error. To prevent this, give Access
some way to
differentiate between the two and decide which one to
return. A simple way
to do that is to include the primary key of the table in
the subquery as the
2nd field of the ORDER BY clause: since the p.k. is
guaranteed to be unique,
Access can now choose just one record to return, and you
have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Allen,

Thanks for the response.

Where do I put the expression that adds the months.
Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does at the
end of the subquery - I've never seen that kind of thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows how to
build
the following as an expression, hopefully that I can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent [ServiceDate], which
are
in a related table, associated with an equipment
table,
many to one, or [DeliveryDate], which is a sub table
of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another
calculated
field.)

So once I have that value, which will either be the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled maintenance
date
by comparing it to the equipment service interval,
which
is an integer between 0 and 12, that equals the
number
of
months after a piece of equipment is maintained that
it
should be maintained again. I'm assuming this
involves
returning the month part, which I haven't had much
luck
with as of yet, so if you have any pointers there,
that
would be great.

Any suggestions?

Scott


.
Click to expand...


.
Click to expand...
Click to expand...
 
Tried it out and I got this error when I tried to run the
query:

You have written a subquery that can return more than one
field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field.

I'm entering the query in a new field box in the query
designer like this: NextMaintenanceDate: (your statment)

It's the only time I've been able to tab out of the field
box without getting a syntax error, but I can't run the
query at all.

Is there somewhere else I should be entering this
statement?



-----Original Message-----
Scott, I've just created your tables, and it works perfectly.

What I have is:
tblEquipment:
-------------
EqID AutoNumber primary key
MaintenanceInterval Number number of months.

tblMaintenanceRecords
-----------------------
MaintenanceRecordsID AutoNumber p.k.
EqID Number foreign key.
MaintenanceDate Date/Time when serviced.

Query:
------
SELECT tblEquipment.EqID, tblEquipment.MaintenanceInterval,
DateAdd("m",[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate
FROM tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID )) AS NextScheduledMaintenance
FROM tblEquipment;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks so much for following along with this. I've been
able to get the LastMaintained field to return the
correct data in the query, but the
NextScheduledMaintenance is still killing me. I've tried
adding and removing every comma, parens, you name it, but
still get a syntax error.

I've had so much trouble with this one that I've
simplified the table structure - creating a new
MaintenanceRecords table to eliminate having to select
matches based on the type field. I don't even care if the
field is null or not - if the query doesn't return a
value, I'm going to want to calculate the date off of
another field anyway...

I just know there has to be a way to get this to work
right, but I have no experience with SQL!

Right now I'm working with this statement:

NextScheduledMaintenance: DateAdd ("m",
[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate FROM
tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID ) )

I have done several variations on this one and get a
syntax error each time (missing operand/operator,
character/comma, or quotation mark...)

wondering what is wrong...

do I need to designate the table MaintenanceInterval
comes from (tblEquipment)? Is there something wrong with
the type of quotation marks I'm using? I've tried just
about everything - help?

Scott
-----Original Message-----
The order by clause needs a comma between the fields. The brackets don't
quite match. You have a field in the subquery table
Click to expand...
that
has the same name
as one in the main query table, so try:
ORDER BY ServiceDate DESC, tblServiceRecords.EqID

You may be able to use [LastMaintained] in another field, but you may need
to repeat the expression.

If a machine has no previous record, the subquery will return Null. Use Nz()
to specify a value where none was returned. It's
Click to expand...
always
worth explicitly
typecasting calculated values, so you will finish up with something like
this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m", [Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE tblServiceRecords.EqID = tblEquipment.EqID
ORDER BY ServiceDate DESC,
Click to expand...
tblServiceRecords.EqID ) ),
Date()))
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

That's great. I've now got an expression that
Click to expand...
returns
the
date the machine was last maintained:

LastMaintained: (SELECT TOP 1 ServiceDate FROM
tblServiceRecords WHERE tblServiceRecords.EqID =
tblEquipment.EqID ORDER BY ServiceDate DESC (EqID] )

and am able to return that field for the query.
Click to expand...
This
is
one of the fields I need in the query, and it's working
fine.

I still need to calculate the next scheduled maintenance
date, and am having problems with the DateAdd expression.
I figured now that I am returning the last maintenance
date in the query (using the above expression), that I
could just create a new field and enter the expression:

NextScheduledMaintenance: DateAdd ("m", [Interval],
[LastMaintained])

with LastMaintained being the value returned by the
expression in the preceeding field.

I'm getting a syntax error, and don't know whether it's
because I've typed something wrong, or whether I
Click to expand...
need
to
replace the [LastMaintained] part with something else.

Help?
-----Original Message-----
Yes, the expression goes into the Field row
Click to expand...
(assuming
you
want a calculated
field as a result). You can place the DateAdd() around
the subquery
expression if you don't need to display the LastServiced
date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate
field. That's how it
picks the most recent record. If there happen to be two
records for the same
piece of machinery on the same date, the subquery would
return two records
which would cause an error. To prevent this, give Access
some way to
differentiate between the two and decide which one to
return. A simple way
to do that is to include the primary key of the
Click to expand...
table
in
the subquery as the
2nd field of the ORDER BY clause: since the p.k. is
guaranteed to be unique,
Access can now choose just one record to return,
Click to expand...
and
you
have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Allen,

Thanks for the response.

Where do I put the expression that adds the months.
Does
it also go in the Field row, or does it go into another
field.

I'm also curious as to what the DESC, ID ) does
Click to expand...
at
the
end of the subquery - I've never seen that kind
Click to expand...
of
thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows
Click to expand...
how
to
build
the following as an expression, hopefully
Click to expand...
that I
can
include in the query...

I need to create values for a Calculated field called
[MaintenanceNextScheduled]. It represents the most
recent of either the most recent
Click to expand...
[ServiceDate],
which
are
in a related table, associated with an equipment
table,
many to one, or [DeliveryDate], which is a sub table
of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value from a
series of records in the same table, as I'll actually
have to do that to retrieve data for another
calculated
field.)

So once I have that value, which will either
Click to expand...
be
the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled maintenance
date
by comparing it to the equipment service interval,
which
is an integer between 0 and 12, that equals the
number
of
months after a piece of equipment is
Click to expand...
maintained
that
it
should be maintained again. I'm assuming this
involves
returning the month part, which I haven't had much
luck
with as of yet, so if you have any pointers there,
that
would be great.

Any suggestions?

Scott


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Emailed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Tried it out and I got this error when I tried to run the
query:

You have written a subquery that can return more than one
field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field.

I'm entering the query in a new field box in the query
designer like this: NextMaintenanceDate: (your statment)

It's the only time I've been able to tab out of the field
box without getting a syntax error, but I can't run the
query at all.

Is there somewhere else I should be entering this
statement?



-----Original Message-----
Scott, I've just created your tables, and it works perfectly.

What I have is:
tblEquipment:
-------------
EqID AutoNumber primary key
MaintenanceInterval Number number of months.

tblMaintenanceRecords
-----------------------
MaintenanceRecordsID AutoNumber p.k.
EqID Number foreign key.
MaintenanceDate Date/Time when serviced.

Query:
------
SELECT tblEquipment.EqID, tblEquipment.MaintenanceInterval,
DateAdd("m",[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate
FROM tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID )) AS NextScheduledMaintenance
FROM tblEquipment;


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Scott A said:
Allen,

Thanks so much for following along with this. I've been
able to get the LastMaintained field to return the
correct data in the query, but the
NextScheduledMaintenance is still killing me. I've tried
adding and removing every comma, parens, you name it, but
still get a syntax error.

I've had so much trouble with this one that I've
simplified the table structure - creating a new
MaintenanceRecords table to eliminate having to select
matches based on the type field. I don't even care if the
field is null or not - if the query doesn't return a
value, I'm going to want to calculate the date off of
another field anyway...

I just know there has to be a way to get this to work
right, but I have no experience with SQL!

Right now I'm working with this statement:

NextScheduledMaintenance: DateAdd ("m",
[MaintenanceInterval],(SELECT TOP 1 MaintenanceDate FROM
tblMaintenanceRecords WHERE tblMaintenanceRecords.EqID =
tblEquipment.EqID ORDER BY MaintenanceDate DESC,
tblMaintenanceRecords.EqID ) )

I have done several variations on this one and get a
syntax error each time (missing operand/operator,
character/comma, or quotation mark...)

wondering what is wrong...

do I need to designate the table MaintenanceInterval
comes from (tblEquipment)? Is there something wrong with
the type of quotation marks I'm using? I've tried just
about everything - help?

Scott

-----Original Message-----
The order by clause needs a comma between the fields.
The brackets don't
quite match. You have a field in the subquery table that
has the same name
as one in the main query table, so try:
ORDER BY ServiceDate DESC, tblServiceRecords.EqID

You may be able to use [LastMaintained] in another
field, but you may need
to repeat the expression.

If a machine has no previous record, the subquery will
return Null. Use Nz()
to specify a value where none was returned. It's always
worth explicitly
typecasting calculated values, so you will finish up
with something like
this:

NextScheduledMaintenance: CDate(Nz(DateAdd ("m",
[Interval],
(SELECT TOP 1 ServiceDate FROM tblServiceRecords
WHERE tblServiceRecords.EqID = tblEquipment.EqID
ORDER BY ServiceDate DESC, tblServiceRecords.EqID ) ),
Date()))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

That's great. I've now got an expression that returns
the
date the machine was last maintained:

LastMaintained: (SELECT TOP 1 ServiceDate FROM
tblServiceRecords WHERE tblServiceRecords.EqID =
tblEquipment.EqID ORDER BY ServiceDate DESC (EqID] )

and am able to return that field for the query. This
is
one of the fields I need in the query, and it's working
fine.

I still need to calculate the next scheduled
maintenance
date, and am having problems with the DateAdd
expression.
I figured now that I am returning the last maintenance
date in the query (using the above expression), that I
could just create a new field and enter the expression:

NextScheduledMaintenance: DateAdd ("m", [Interval],
[LastMaintained])

with LastMaintained being the value returned by the
expression in the preceeding field.

I'm getting a syntax error, and don't know whether it's
because I've typed something wrong, or whether I need
to
replace the [LastMaintained] part with something else.

Help?
-----Original Message-----
Yes, the expression goes into the Field row (assuming
you
want a calculated
field as a result). You can place the DateAdd() around
the subquery
expression if you don't need to display the
LastServiced
date.

The ORDER BY clause of the subquery is:
ServiceDate DESC, ID
The DESC specified descending order on the ServiceDate
field. That's how it
picks the most recent record. If there happen to be
two
records for the same
piece of machinery on the same date, the subquery
would
return two records
which would cause an error. To prevent this, give
Access
some way to
differentiate between the two and decide which one to
return. A simple way
to do that is to include the primary key of the table
in
the subquery as the
2nd field of the ORDER BY clause: since the p.k. is
guaranteed to be unique,
Access can now choose just one record to return, and
you
have avoided the
error.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

Allen,

Thanks for the response.

Where do I put the expression that adds the months.
Does
it also go in the Field row, or does it go into
another
field.

I'm also curious as to what the DESC, ID ) does at
the
end of the subquery - I've never seen that kind of
thing
before.

Thanks,

Scott
-----Original Message-----
You can use a subquery to retrieve the most date a
machine was last
serviced. Type something like this into a fresh
column
of the query design
grid (Field row):
LastServiced: ( SELECT TOP 1 ServiceDate
FROM MyRelatedTable
WHERE MyRelatedTable.MachineID =
MyMainTable.MachineID
ORDER BY ServiceDate DESC, ID )

To add on a number of months:
DateAdd("m", [ServicePeriod], [MyDate])

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

I've got a calculated field that I would like to
include
in a report, so I'm wonding if anyone knows how
to
build
the following as an expression, hopefully that I
can
include in the query...

I need to create values for a Calculated field
called
[MaintenanceNextScheduled]. It represents the
most
recent of either the most recent [ServiceDate],
which
are
in a related table, associated with an equipment
table,
many to one, or [DeliveryDate], which is a sub
table
of
the equipment table (1:1).

(I'm particularly interested in how I build the
expression that requests the most recent value
from a
series of records in the same table, as I'll
actually
have to do that to retrieve data for another
calculated
field.)

So once I have that value, which will either be
the
delivery date or the last date maintenance was
performed,
I need to calculate the next scheduled
maintenance
date
by comparing it to the equipment service
interval,
which
is an integer between 0 and 12, that equals the
number
of
months after a piece of equipment is maintained
that
it
should be maintained again. I'm assuming this
involves
returning the month part, which I haven't had
much
luck
with as of yet, so if you have any pointers
there,
that
would be great.

Any suggestions?

Scott


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top