Query on record for two items

[captsamm]

I have a customer database that includes the customer and his/her significant other and their birthdays. Field names are fname1, lname1, birthday1, fname2, lname2, and birthday2. (yeah I know it's not very original ;-} )
Would someone help me write a query that would output a single list of names and birthdays occuring in a two week period starting one week from date(). I imagine it would have to be a query that would search twice and append the names to a temporary db with different field names, but I don't know how to go about it.

Regards,

Samm

 

[Sam92Legacy]

I think the best way to do this is to make a union query.

The first half should include the columns fname: fname1, lname: lname1, and birthday: birthday1, with birthday1's criteria being >=Date() And <=Date()+14. The second half should include fname: fname2, lname: lname2, and birthday: birthday2, with birthday2's criteria being >=Date() And <=Date()+14.

If you've never made a union query before, the easiest way is to make the first half in the QBE grid, switch to SQL view, copy the SQL code and paste it into a Word document. Next, go back to design view, make the second half of the query, switch back to SQL view, type UNION after the last line of code, and paste the first half after UNION. As long as your columns have the same names, it should work. (Hence renaming your columns for the query.)

Sam