Join tables by dates

[Ryan]

I have 2 tables that I need to join. The first table contains the fields
PhoneNumber, FromDate, Todate, PlanCost. The second tables contains the
fields PhoneNumber, DateOfCall, CallCost.

There are multiple plans for each phone number in the first table as the
phone plans change over time. I want to add the phone plan, to the second
table, that relates to the date of the call so that I can check we were being
charged the correct amount. Any ideas?

 

[luz marina gil mejia]

I have 2 tables that I need to join. The first table contains the fields
PhoneNumber, FromDate, Todate, PlanCost. The second tables contains the
fields PhoneNumber, DateOfCall, CallCost.

There are multiple plans for each phone number in the first table as the
phone plans change over time. I want to add the phone plan, to the second
table, that relates to the date of the call so that I can check we were
being
charged the correct amount. Any ideas?

 

[Gina Whipp]

Ryan,

I'm having a difficult time figuring out what business this applies to...
Perhaps if you could give a little synopsis of the business and what you
want to track along with what other tables you have you can get a
comprehensive answer.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

I have 2 tables that I need to join. The first table contains the fields
PhoneNumber, FromDate, Todate, PlanCost. The second tables contains the
fields PhoneNumber, DateOfCall, CallCost.

There are multiple plans for each phone number in the first table as the
phone plans change over time. I want to add the phone plan, to the second
table, that relates to the date of the call so that I can check we were being
charged the correct amount. Any ideas?

SELECT Table1.PhoneNumber, Table1.PlanCost, Table2.CallCost
FROM Table1
INNER JOIN Table2
ON Table1.PhoneNumber = Table2.PhoneNumber
AND Table2.DateOfCall >= Table1.FromDate AND Table2.DateOfCall <=
Table1.ToDate;

should work, using a Non-Equi Join. If it doesn't, or isn't updateable, use

SELECT Table1.PhoneNumber, Table1.PlanCost, Table2.CallCost
FROM Table1
INNER JOIN Table2
ON Table1.PhoneNumber = Table2.PhoneNumber
WHERE Table2.DateOfCall >= Table1.FromDate AND Table2.DateOfCall <=
Table1.ToDate;

 

[KARL DEWEY]

I do not think a join is what you want but something like this for travel
expenses --
SELECT Travel.Name AS Expr1, Travel.Mileage AS Expr2,
[RateTable-Mileage].Rate AS Expr3, IIf([mileage] Between [MinMiles] And
[MaxMiles],[Rate],"Error") AS FRate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Travel
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

 

[Ryan]

Hi Karl
Thanks for the travel example. I copied your example exactly into a blank
database to trial the theory however it asks me for parmater values. Am I
missing something obvious?

I do not think a join is what you want but something like this for travel
expenses --
SELECT Travel.Name AS Expr1, Travel.Mileage AS Expr2,
[RateTable-Mileage].Rate AS Expr3, IIf([mileage] Between [MinMiles] And
[MaxMiles],[Rate],"Error") AS FRate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Travel
Name Mileage
joe 650
dd 100
ll 10000
p 10

[RateTable-Mileage]
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521
--
Build a little, test a little.

I have 2 tables that I need to join. The first table contains the fields
PhoneNumber, FromDate, Todate, PlanCost. The second tables contains the
fields PhoneNumber, DateOfCall, CallCost.

There are multiple plans for each phone number in the first table as the
phone plans change over time. I want to add the phone plan, to the second
table, that relates to the date of the call so that I can check we were being
charged the correct amount. Any ideas?

 

[John W. Vinson]

Hi Karl
Thanks for the travel example. I copied your example exactly into a blank
database to trial the theory however it asks me for parmater values. Am I
missing something obvious?

I do not think a join is what you want but something like this for travel
expenses --
SELECT Travel.Name AS Expr1, Travel.Mileage AS Expr2,
[RateTable-Mileage].Rate AS Expr3, IIf([mileage] Between [MinMiles] And
[MaxMiles],[Rate],"Error") AS FRate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Hrm?

Yes, it asks for parameters. Anything in square brackets that Access doesn't
recognize as a table or fieldname or a form reference is treated as a
parameter, and you'll get prompted.

DId you also create the *table* that Karl based his query upon? A query by
itself cannot do anything.

 

[Ryan]

Hi John
Yes, I created the tables. I have played around with my example and have
managed to get it to work. Thanks for your help.

Hi Karl
Thanks for the travel example. I copied your example exactly into a blank
database to trial the theory however it asks me for parmater values. Am I
missing something obvious?

I do not think a join is what you want but something like this for travel
expenses --
SELECT Travel.Name AS Expr1, Travel.Mileage AS Expr2,
[RateTable-Mileage].Rate AS Expr3, IIf([mileage] Between [MinMiles] And
[MaxMiles],[Rate],"Error") AS FRate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

Hrm?

Yes, it asks for parameters. Anything in square brackets that Access doesn't
recognize as a table or fieldname or a form reference is treated as a
parameter, and you'll get prompted.

DId you also create the *table* that Karl based his query upon? A query by
itself cannot do anything.