One to many issues (1 Viewer)

[GODZILLA]

Hello,

I have 2 tables.

Table 1 contains details of accounts that are due for renewal.

Table 2 has accounts that have renewed.

The only link i have is the customer name.

My problem is that i can have 1 record in table 1 and 3 records in table 2.

So i start with 100 and finish with 120 records. What i want to see is all 100 have renewed.

Has anyone worked with data like this before?

Any advice would be great. Thanks

Current SQL:

SELECT [I_REN_01_NO_100%].I_REN_CLIENT_NAME
,[I_REN_01_NO_100%].I_REN_CLIENT_NO
,[I_REN_01_NO_100%].I_REN_RENEWAL_DATE
,BW_KEY_POLICIES.BW_EX_DATE

FROM [I_REN_01_NO_100%] LEFT JOIN BW_KEY_POLICIES ON [I_REN_01_NO_100%].I_REN_CLIENT_NAME = BW_KEY_POLICIES.BW_FULLNAME

WHERE ((([I_REN_01_NO_100%].I_REN_RENEWAL_DATE) Between #2/1/2011# And #2/28/2011#))

ORDER BY [I_REN_01_NO_100%].I_REN_RENEWAL_DATE;

 

[Fuga]

Not sure if I understand, but don´t you need some kind of unique identifier of the accounts to identify them in the "already renewed" table?

Fuga.

 

[GODZILLA]

Not sure if I understand, but don´t you need some kind of unique identifier of the accounts to identify them in the "already renewed" table?

Fuga.

Hello,

In an ideal world that would be possible. However this was overlooked by my predecessors and I have to work with what i have.

The only uniqish data is the name, which isnt actually unique. Sure if i had more access to data i could work it better, howeve I do not.

My biggest problem is that if i have 2 records in table 1 and 2 in table 2. Both have renewed. Access returns 4 possible outcomes.

Table_1_Record 1 - Table_2_Record_1
Table_1_Record 1 - Table_2_Record_2
Table_1_Record 2 - Table_2_Record_1
Table_1_Record 2 - Table_2_Record_2

Any ideas?