How do I summarize data from one table based on dates in another table?

[TAM]

I have 3 tables:
1) a date table which has unique promotion start and end dates.
2) Rep table which has 1 record per rep with a file creation date.
3) History table which has one row of sales results by rep by report date going back to 2005

I would like to take the date table in file #2 and compare it to the rows in the date table. If the file creation date is between the program start and end dates, I want to summarize the sales between those dates from the history table.

For example:[U]
date table has the following first two rows of data
Row1: Program start_date of 6/24/15 and Program End date of 12/23/15; Accumulation start 12/24/2014 Accumulation End 12/22/1015
Row 2: Program Start of 12/23/2015 and program end date of 6/22/2016; accumulation start 6/24/2015 Accumulation end 6/21/2016

Rep Table: Creation Date 12/22/2015

If the Table Creation date is between 6/24/15 and 12/23/15, I would like to summarize the sales by Rep for the dates of 12/24/2014 - 12/22/2015.


Next Example: We are now in April and the file creation date is 4/12/15. I want to summarize the sales for the reps for the date of 6/24/2015 - 6/21/16.

How do I write iif statements to accomplish this?

 

[Micron]

I would not even want to attempt this with IIF statements.
Is there no relationship between your date table and the other two? If not, I think you will be stuck with a subquery. It can be a daunting exercise, but study carefully and you have a good chance of achieving a solution. I'm having a bit of trouble seeing how you arrived at the date parameters, but even so, subqueries make my head hurt so I'll post a couple of good links on the subject and allow you to see if you can envision a solution there.

Microsoft Access tips: Subquery basics
Microsoft Access tips: Surviving subqueries

 

[xenou]

Agree with Micron (sort of) that query criteria is appropriate here, not IIFs. But subqueries may or may not be needed since join clauses may also work fine. Relationships clearly exist between the tables, based on either dates, reps or both!

You said at the outset that:

1) a date table which has unique promotion start and end dates.

but then you say:

date table has the following first two rows of data
Row1: Program start_date of 6/24/15 and Program End date of 12/23/15; Accumulation start 12/24/2014 Accumulation End 12/22/1015

what are these accumulation dates? I don't understand them as they begin after the program end dates (?). So that makes it difficult to understand what the criteria is for these queries.

 

[Micron]

Agree with Micron (sort of) that query criteria is appropriate here, not IIFs. But subqueries may or may not be needed since join clauses may also work fine. Relationships clearly exist between the tables, based on either dates, reps or both!

The problem between the dates in the tables is that they will 'never' be equal, so an inner join will not work. Any outer join should result in a data mish-mash. I had to solve a similar issue when I needed data from a transaction table when there was no relationship between the stores transaction date and the repair department work order approval date. My solution was to get the transaction record for the First transaction date that was after the work order approval date using a sub query. In the OP's case, I looked for but couldn't realize a connection between the dates. I also found the explanation confusing.

 

[xenou]

It is not true that join clauses require equals. As an example:

SELECT T3.*
FROM Table3 T3
INNER JOIN Table4 T4
ON T3.TransDate >= T4.DateStart AND T3.TransDate <= T4.DateEnd
WHERE T4.ProgramName = 'C';

This gives me all the rows in Table3 that fall between the program dates defined in Table4.

 

[Micron]

It is not true that join clauses require equals

That's not what I said. I said an inner join won't work because they're not equal.

 

[xenou]

Okay. I thought that meant because the dates weren't equal. I wanted to show an inner join that works with dates that aren't equal.

 

[TAM]

I would not even want to attempt this with IIF statements.
Is there no relationship between your date table and the other two? If not, I think you will be stuck with a subquery. It can be a daunting exercise, but study carefully and you have a good chance of achieving a solution. I'm having a bit of trouble seeing how you arrived at the date parameters, but even so, subqueries make my head hurt so I'll post a couple of good links on the subject and allow you to see if you can envision a solution there.

Microsoft Access tips: Subquery basics
Microsoft Access tips: Surviving subqueries

Thanks!! I'll give these sites a review.