Extracting data for 54 days prior to a particular date

[sssu]

Hi,

I am trying to extract some data from a table.
Table A has
Company Name dates EPS value
(approx 3400). This is annual data

Table B has
Company Names dates Daily return
This is daily data (approx 70000 records)

I need to match up the company names and dates in A and B. This is fine. However, I want to return the values for the previous 54 days as well for that company.

Can this be done?

Thank you

 

[JonFer]

Use a criteria like this:

[YourDate] >= Now()-54 (or 53 if you count the current day)

Now() returns the current date and subtracting 54 from it returns the first day you want to include.

 

[sssu]

I don't think that will work.
I need to make sure that the date I get corresponds to the same company. I also want the data for all the 54 days.
Doesn't this mean that I need to specify the company?

 

[JonFer]

That was just the date criteria. It sounded like that was all you needed. Here's more detail...

Create a query with the query design window, drop in the two tables, create a join line on the company name, pull down the desired fields from the tables to the bottom pane, and THEN add the date criteria.

If you need more help with the SQL, post the full table structures (or at least the fields we're dealing with), a few sample records from each, and the desired output.

 

[sssu]

I don't think I'm explaining myself properly.

Table A= Earnings Announcements
Company Name Dates EarningsValue
A 31/03/1993 1.04
B 01/04/1993 1.04
(Annual data- A company releases earnings annually)
Table B= Returns
Company Name Dates EarningsValue
A 31/03/1993 1.04
A 01/04/1993 1.04
A 02/04/1993 1.04
B 01/08/1993 1.04
B 02/08/1993 1.04
(Returns data.This is daily)

For each earnings figure I should have a corresponding returns figure. But i want to create a table which will have the returns of not only the earnings date but also the returns for the 54 days before that date(These return dates do not have a corresponding earnings figure- the earnings table only has 3200 entries whilst the returns table has 70000. This means that I need more than a match up query).
Note not all the companies have data for 54 days before the earnings date.
Is this better? Can it be done?

Thanks once again.

 

[JonFer]

That helps. I still don't know what you want the output to look like but I'll assume you want one row for each returns date that meets your criteria.

Select Earnings.[Company Name],
Earnings.Dates,
Earnings.Value,
Returns.Dates,
Returns.Value
From Earnings Inner Join Returns on (Earnings.[Company Name]=Returns.[Company Name])
Where Returns.Dates Between (Earnings.Dates-54) And Earnings.Dates)