ACCESS FEED: [MS_AccessPros] Re: Comparison of two tables (other case)

Jumat, 15 Maret 2013

[MS_AccessPros] Re: Comparison of two tables (other case)

Hi Hendra

Your English is very good. To compare each date to each range you set up your query with both tables but do not join them. The SQL would be:
SELECT tbl_DateToCompare.Date
, Tbl_DateRange.Start_Date
, Tbl_DateRange.End_Date
,
IIF
([Date]>=[Start_Date]
AND [Date]<=[End_Date],"InRange","Out Of Range"
)
AS Status
FROM tbl_DateToCompare
, Tbl_DateRange;

If you are going to ask for user input add a WHERE clause with a prompt like this one to find if a date is in the range starting on 1/1/2013
SELECT tbl_DateToCompare.Date
, Tbl_DateRange.Start_Date
, Tbl_DateRange.End_Date
,
IIF
([Date]>=[Start_Date]
AND [Date]<=[End_Date],"InRange","Out Of Range"
)
AS Status
FROM tbl_DateToCompare
, Tbl_DateRange
WHERE Tbl_DateRange.Start_Date=[Enter startdate];

Bill

--- In MS_Access_Professionals@yahoogroups.com, Agestha Hendra <agesthahendra@...> wrote:
>
> Hi Everyone... :)
>
> It's hard to describe what i mean in English,...but i try my best :
>
> I have two tables : Tbl_DateToCompare and Tbl_DateRange :
> * Tbl_DateToCompare has a field : [Date] with dd/mm/yyyy format.
> * Tbl_DateRange has two fields : [Start_Date] and [End_Date], both with dd/mm/yyyy format.
>
> For example i give records to that both tables :
> 1. Tbl_DateToCompare :
>     [Date]
> 05/01/2013
> 10/01/2013
> 17/01/2013
>
> 2. Tbl_DateRange :
> [Start_Date]    [End_Date]
> 01/01/2013      07/01/2013
> 08/01/2013      14/01/2013
> 15/01/2013      21/01/2013
>
> Recordcount in that both tables is depending of user input (not determined)..
>
> The point is i want to compare all records in Tbl_DateToCompare against/to all records in Tbl_DateRange one by one, the results in query or table should be like this :
>   [Date]           [Start_Date]    [End_Date]     [Status]
> 05/01/2013      01/01/2013      07/01/2013      In
> Range
> 05/01/2013      08/01/2013      14/01/2013      Out
> Of Range
> 05/01/2013      15/01/2013      21/01/2013      Out
> Of Range
> 10/01/2013      01/01/2013      07/01/2013      Out
> Of Range
> 10/01/2013      08/01/2013      14/01/2013      In
> Range
> 10/01/2013      15/01/2013      21/01/2013      Out
> Of Range
> 17/01/2013      01/01/2013      07/01/2013      Out
> Of Range
> 17/01/2013      08/01/2013      14/01/2013      Out
> Of Range
> 17/01/2013      15/01/2013      21/01/2013      In
> Range
>
>
> the [Status]'s Formula is : IIf([Date]>=[Start_Date] and [Date]<=[End_Date],"In Range","Out Of Range")
>
>
> Hope you can understand what i mean,...any explanation would be grateful....thank you very much...
>
> Regards
> Hendra Agestha
>
> [Non-text portions of this message have been removed]
>