Query - Multiple Dates

[jeff_sco]

Hi Everyone,

I am relatively new to Access (and databases) and learning as I got along (not got into any SQL as yet). I have a query which I can't quite get my head around which I'd like some assistance with.

I want to run a query using the following information (in multiple tables) names, work location (a ship), date they got onboard and date they got off, start date vessel outside uk waters, end date outside uk waters.

I have a query (lots of stacked iifs and diffdates) which is probably horrific in elegance to the experienced, which tells me how many days personnel have been outside uk waters during the period of their trip. i.e. Person A has done x number of days outside uk waters during their trip.

What I am after assistance with is a query which uses the information above, which gives me the same information (number of days outside uk waters) but only within a certain specified month. i.e. Person A has done x number of days outside uk waters in december.

If anyone can even point me in the right direction as to how I should be solving this I'd be very grateful. Thanks in advance.

Jeff

 

[recyan]

Till some one comes along, just a bit of off the cuff thinking :

have a column which checks if Month and Year of "end date outside uk waters" is later than Month December and Year 2012,
if Yes
EndDateForCalculation = 31st December 2012
else
EndDateForCalculation = end date outside uk waters
end if
Then,
Perhaps in the query, we could set the criteria for "start date vessel outside uk waters" as Month December and Year 2012,

Thanks

 

[jeff_sco]

Thanks for the reply. I shall start the brain ticking and go consider how to use that.

 

[recyan]

At first glance, there appears to be a bug in my last sentence in the previous post.
Try fiddling with it the way we fiddle with the End Date.
something more to look at meanwhile :

SELECT 
	myTable.VesselID, 
	myTable.startDateVesselOutsideUKwaters, 
	myTable.endDateVesselOutsideUKwaters, IIf([startDateVesselOutsideUKwaters]<#12/1/2012#,#12/1/2012#,[startDateVesselOutsideUKwaters]) AS StartDateForCalculation,
	IIf([endDateVesselOutsideUKwaters]>#12/31/2012#,#12/31/2012#,[endDateVesselOutsideUKwaters]) AS EndDateForCalculation,
	DateDiff("d",[StartDateForCalculation],[EndDateForCalculation]) AS NoOffDays
FROM 
	myTable;

There is a post by Brian which deals with Dates, which perhaps might be useful here, even otherwise, it's a beauty. I'll try to post the link.

Sorry, it's by pbaldy ( by mistake, i remembered it as Brian ). Do take a look at it.
http://www.baldyweb.com/OverLap.htm

Thanks

 

[jeff_sco]

Thanks again, I'll go have a look.