Query for multiple due dates

K

Ktowner

I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
 
K

KARL DEWEY

Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.
 
K

Ktowner

Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




KARL DEWEY said:
Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


Ktowner said:
I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
Click to expand...
 
J

John Spencer

You can't have the same alias for two different fields (columns). Also, I am
confused by your logic. I think you need something more like the following.

SELECT [Equip Info].*
, IIf([Last Wheel Bearing Inspection] < DateAdd("m",11,Date())
And [Wheel Pack 12 or 24 Months]= "12","Due","") AS [Wheel Pack 12 Months]
, IIf([Last Wheel Bearing Inspection] < DateAdd("M",23,Date())
And [Wheel Pack 12 or 24 Months]= "24","Due","") AS [Wheel Pack 24 Months]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS")
AND (([Equip Info].[Last Wheel Bearing Inspection])<Date()-335));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




KARL DEWEY said:
Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


Ktowner said:
I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 24
Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));

--
Build a little, test a little.


Ktowner said:
Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




KARL DEWEY said:
Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


Ktowner said:
I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
Click to expand...
Click to expand...
 
K

Ktowner

Thanks Karl

When I try to run your posted query I get an error (invalid bracketing of
name 'Wheel Pack 24 Months').

KARL DEWEY said:
Try this --
SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 24
Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));

--
Build a little, test a little.


Ktowner said:
Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




KARL DEWEY said:
Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


:

I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
Click to expand...
Click to expand...
 
K

Ktowner

Thanks John

I have copied the query as you suggested. However when I run the query it
pulls everything, 24 month criteria included, that is within 30 days due from
12 months of last service.

Don K.

John Spencer said:
You can't have the same alias for two different fields (columns). Also, I am
confused by your logic. I think you need something more like the following.

SELECT [Equip Info].*
, IIf([Last Wheel Bearing Inspection] < DateAdd("m",11,Date())
And [Wheel Pack 12 or 24 Months]= "12","Due","") AS [Wheel Pack 12 Months]
, IIf([Last Wheel Bearing Inspection] < DateAdd("M",23,Date())
And [Wheel Pack 12 or 24 Months]= "24","Due","") AS [Wheel Pack 24 Months]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS")
AND (([Equip Info].[Last Wheel Bearing Inspection])<Date()-335));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




KARL DEWEY said:
Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


:

I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
Click to expand...
.
Click to expand...
 
K

KenSheridan via AccessMonster.com

Don:

Try this:

SELECT *
FROM [Equip Info]
WHERE [Unit Location] <> "OOS"
AND [Wheel Pack 12 or 24 Months] <> "N/A"
AND [Last Wheel Bearing Inspection] <=
DATEADD("d", 30,DATEADD("m",
VAL([Wheel Pack 12 or 24 Months])*-1,DATE()));

Group the report by [Wheel Pack 12 or 24 Months] if you want to separate the
upcoming 12 and 24 month services in the report.

Ken Sheridan
Stafford, England
Thanks Karl

When I try to run your posted query I get an error (invalid bracketing of
name 'Wheel Pack 24 Months').
Try this --
SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Click to expand...
[quoted text clipped - 58 lines]
Click to expand...
 
J

John Spencer

Change the where clause to return only records that are in the proper time
frame and have the proper [Wheel Pack 12 or 24 Months] value


SELECT [Equip Info].*
, IIf([Last Wheel Bearing Inspection] < DateAdd("m",11,Date())
And [Wheel Pack 12 or 24 Months]= "12","Due","") AS [Wheel Pack 12 Months]
, IIf([Last Wheel Bearing Inspection] < DateAdd("M",23,Date())
And [Wheel Pack 12 or 24 Months]= "24","Due","") AS [Wheel Pack 24 Months]
FROM [Equip Info]
WHERE [Equip Info].[Unit Location]<>"OOS"
AND
((
[Equip Info].[Last Wheel Bearing Inspection]<DateAdd("m",11,Date())
AND [Wheel Pack 12 or 24 Months] = "12"
)
OR
(
[Equip Info].[Last Wheel Bearing Inspection]<DateAdd("m",23,Date())
AND [Wheel Pack 12 or 24 Months] = "24"
))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John

I have copied the query as you suggested. However when I run the query it
pulls everything, 24 month criteria included, that is within 30 days due from
12 months of last service.

Don K.

John Spencer said:
You can't have the same alias for two different fields (columns). Also, I am
confused by your logic. I think you need something more like the following.

SELECT [Equip Info].*
, IIf([Last Wheel Bearing Inspection] < DateAdd("m",11,Date())
And [Wheel Pack 12 or 24 Months]= "12","Due","") AS [Wheel Pack 12 Months]
, IIf([Last Wheel Bearing Inspection] < DateAdd("M",23,Date())
And [Wheel Pack 12 or 24 Months]= "24","Due","") AS [Wheel Pack 24 Months]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS")
AND (([Equip Info].[Last Wheel Bearing Inspection])<Date()-335));

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for the reply. I plugged my info into your brackets and get this
error when I run the query, Duplicate output alias 'Wheel Pack 12 or 24
Months'.

I added <> OOS (Out Of Service) to the location field so as to only include
active equipment.

The [Last Wheel Bearing Inspection] is the date the service was last
completed.
The [Wheel Pack 12 or 24 Months] is a field that hold the text "12" "24" or
"N/A".

Here is what I have after I subed my info into the brackets.

SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Between Date()-335 And Date()-700) And [Equip Info].[Last Wheel Bearing
Inspection]<Date()-335,"Due","") AS [Wheel Pack 12 or 24 Months], IIf([Equip
Info].[Last Wheel Bearing Inspection]<Date()-700,"Due","") AS [Wheel Pack 12
or 24 Months], [Equip Info].[Unit Location]
FROM [Equip Info]
WHERE ((([Equip Info].[Unit Location])<>"OOS") AND (([Equip Info].[Last
Wheel Bearing Inspection])<Date()-335));


Thanks Again

Don K.




:

Try this --
SELECT [YourTable].*, IIF(([YourTable].[AquisitionDate] Between Date()-335
AND Date()-700) AND [YourTable].[AquisitionDate] <Date()-335, "Due", "") AS
[12 Month], IIF([YourTable].[AquisitionDate] <Date()-700, "Due", "") AS [24
Month]
FROM [YourTable]
WHERE [YourTable].[AquisitionDate] <Date()-335;

You may need to tweak the Between Date()-335 AND Date()-700 a little.

--
Build a little, test a little.


:

I am tracking the service on my equipment fleet with Access. The older
equipment has a 12 month service interval and the newer a 24 month service
interval. In my queries, I am using the <Date()-335 to find the 12 months
service that is within 30 days of being due. I am using a separate query
with the <Date()-700 to find the 24 months service that is within 30 days of
being due. I use a separate field to indicate 12 or 24 month service
interval for a piece of equipment. I am running 2 separate reports with the
2 queries. How can I write one query to show both 12 and 24 month service,
30 days of being due for a single report?

Thanks In Advance

Don K.
Click to expand...
.
Click to expand...
Click to expand...
 
K

Ktowner

Works great, thanks for the help Ken!

Have a great day!

Don K.

KenSheridan via AccessMonster.com said:
Don:

Try this:

SELECT *
FROM [Equip Info]
WHERE [Unit Location] <> "OOS"
AND [Wheel Pack 12 or 24 Months] <> "N/A"
AND [Last Wheel Bearing Inspection] <=
DATEADD("d", 30,DATEADD("m",
VAL([Wheel Pack 12 or 24 Months])*-1,DATE()));

Group the report by [Wheel Pack 12 or 24 Months] if you want to separate the
upcoming 12 and 24 month services in the report.

Ken Sheridan
Stafford, England
Thanks Karl

When I try to run your posted query I get an error (invalid bracketing of
name 'Wheel Pack 24 Months').
Try this --
SELECT [Equip Info].*, IIf(([Equip Info].[Last Wheel Bearing Inspection]
Click to expand...
[quoted text clipped - 58 lines]
Don K.
Click to expand...
Click to expand...

--



.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Help 1
Countdown macro - display settings 2
Update Query 1
TYPE CONVERSION 1
Update End Date 5
Page numbering with Merged Document and IF statements 1
Conditional formula's in excel-calculating with dates / age andaffiliation date calculus 3
Query to return latest dates 5

Top