How do I sum fields in a query with different date criteria from other table?

[TAM]

Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date

Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)

Each rep has different start and end dates.

Thanks!!

[ranman256]

join the two tbls/qrys on RepID.

I dont see the need to limit the sales data by date,...why would a sales rep continue to sell if he doesnt work there anymore?
Therefor their data will never go outside of the rep.end_date.

So just sum all data by rep.

[TAM]

You're making judgments on the data when you don't know the reason I'm summing. The reps still work here. There are contests with different start and enddates by person because their incentives are unique. Is this the correct way to sum the data? In my sales column in the query I have the following criteria:

'where[start_date]">=[ReportDate] And [end_date<=[ReportDate]

[June7]

Post the entire query SQL statement.

What do you mean by 'in the sales column'? That criteria cannot be under the sales column.

[TAM]

Post the entire query SQL statement.

What do you mean by 'in the sales column'? That criteria cannot be under the sales column.

Good to know. Remember I am a beginner and I have not used the Query Wizard.

The criteria would be placed in the Week_date Column?


Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)

[June7]

I would use:

WHERE week_date BETWEEN start_date AND end_date

Post the query full SQL statement for analysis.

[TAM]

I'm using the Query Wizard. Where would I find the SQL that is generated to post for analysis?

[June7]

Switch the builder to SQL View.