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

Jumat, 15 Maret 2013

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

Hendra-

Like this:

SELECT Tbl_DateToCompare.[Date], Tbl_DateRange.Start_Date,
Tbl_DateRange.End_Date, IIf([Date]>=[Start_Date] and [Date]<=[End_Date],"In
Range","Out Of Range") As Status

FROM Tbl_DateToCompare, Tbl_DateRange

ORDER BY Tbl_DateToCompare.[Date], Tbl_DateRange.Start_Date;

You are basically creating a Cartesian Product of the two tables so that you
can compare every row in DateToCompare with DateRange.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Agestha Hendra
Sent: Friday, March 15, 2013 9:31 PM
To: ms_access_professionals@yahoogroups.com
Subject: [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]

[Non-text portions of this message have been removed]