Join two tables with an expression?

M

Melo

Hi!

I've created a query that is based on a couple of tables. They are linked
together with simple join links.
I would like insert in my query a colomn "Financial_period" wich comes from
a Financial_period_calendar table.
The structure looks like this:
Start_date (date type)
End_date (date type)
Financial_Period (number)

In my main table, I have a field called "activity_date" and I would like to
add to my report in what Financial_period that
activity has occured... I know I could use a temporary table, but was
wondering if there was anyway I could do it in one
single step?

Thanks for your help!

Mel
 
D

Douglas J. Steele

If I understand what you're asking (you have want to pick the
Financial_Period from Financial_Period_Calendar that Activity_Date falls
within), pretend, for a minute, that you can join the two tables on
Activity_Date and Start_Date and set the query up that way.

Then, go into the SQL associated with your query (you can select SQL View
from the View menu when the query's open).

You should see something along the lines of:

ON MyTable.Activity_Date = Financial_Period_Calendar.Start_Date

Change that to

ON MyTable.Activity_Date BETWEEN Financial_Period_Calendar.Start_Date AND
Financial_Period_Calendar.End_Date

Note that if Activity_Date also includes time, you may want to use
(Financial_Period_Calendar.End_Date + 1)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top