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

Jumat, 15 Maret 2013

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

Or you could do it John's way.

-Bill

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> 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]
> >
>