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

Jumat, 15 Maret 2013

[MS_AccessPros] Comparison of two tables (other case)

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]