Complex Query

[amotto11]

I am very new to Access but I am needing to querry between two tables. This is my basic scenario:

Table 1


Policy ------ EffDate --------- Amount
1 ---------- 2/11 ---------- 5
2 ---------- 3/11 ---------- 6
3 ---------- 4/11 ---------- 7
4 ---------- 5/11 ---------- 8
5 ---------- 6/11 ---------- 9
6 ---------- 7/11 ---------- 4

In this table my Primary Key is Policy, the numbers never repeat

Table 2
Policy ------- RateDate -------- term
1 ------------ 2/11 ---------3
1 ------------ 3/11 ---------4
1 ------------ 3/11 ---------5
2 ------------ 3/11 ---------6
2 ------------ 4/11 ---------7
4 ------------ 5/11 ---------8
5 ------------ 6/11 ---------9

In this Table my Primary Key is Term, but i do not care about it. What I am looking to do is to gather the times that the Policy numbers are the same at the same time that the Dates are the same, it would look something like this:

Policy--------Date
1----------- 2/11
2 ---------- 3/11
4 ---------- 5/11
5 ---------- 6/11

Any help is very much appretiated. Thanks

[Rhino373]

The SQL looks something like this:

SELECT Table1.Policy, Table2.RateDate
FROM Table1 INNER JOIN Table2 ON (Table1.EffDate = Table2.RateDate) AND (Table1.Policy = Table2.Policy);

[Rhino373]

In query design you, you drag Policy to Policy to create a join and drag EffDate to RateDate to create a join.

[amotto11]

The SQL looks something like this:

SELECT Table1.Policy, Table2.RateDate
FROM Table1 INNER JOIN Table2 ON (Table1.EffDate = Table2.RateDate) AND (Table1.Policy = Table2.Policy);

I believe that this will work but I fogot to mention that my Dates on my Table1 are in Days like 8-11-2011, but my dates on Table2 are in days and Time, 8-11-2011 10:25:51.337. I there a way to just look at the first so many characters or do I need to do something else to get rid of the time aspect on Table2. I cannot just change the type of cell, that is not working.

[Rhino373]

Both need to be of the Date/Time data type. If they are not you'll need a query to convert them. So from table 1 you'll create a query that includes the Policy, Term, and for the third field you'll orient it like this: RDate: DateSerial(year(RateDate),month(RateDate),Day(Rate Date)). This will return a formatted date. If the datatype was text, you'll need to use the DateValue() function. You'll create a similar query for table 2 and then a third query to compare the 2 new helper queries you've created.

[amotto11]

Thank you very much