Overlapping Date Parameters - Dropping Data

[CanadianAccessUser]

Hello!

I'm trying to create a report that pulls from two tables [tblTelephony] and [tblSales]. All data in my query is limited to a date range entered through a form.

For every record in [tblSales] (showing the agent made a sale) there is a record in [tblTelephony] (showing all the stats for the agent's day worked). [tblTelephony] has one date for each record. [tblSales] has two dates for each record. The sales dates are the date the services were ordered (matches the date worked in [tblTelephony]) and the date the services were installed.

In order to get an agent's MTD Sales stats I have to query the date range on Install dates. MTD Telephony stats are run on the same date range on telephony date. Where I run into an issue is with the sales that are ordered before the date range in question and installed during it.

I've run a separate query to sum the sales installed during the date range and used that sales value in my Telephony query. In order to get my data to show as accurately as possible, I had to create a relationship between the Order Date and the Telephony date. I'm really hoping to find a way to force the sum of sales in sales query to show in the sales column in the telephony query, regardless of the telephony date range and without adding telephony data for dates outside the range.

Example:
Date Range = 2/1/14 - 2/24/14
Telephony Date = 2/3/14
Order Date = 2/3/14
Install Date = 2/14/14
Appears on report

Date Range = 2/1/14 - 2/24/14
Telephony Date = 1/31/14
Order Date = 1/31/14
Install Date = 2/3/14
Does not appear on report

:banghead:

Any ideas at all on how to get the sale example on the bottom to show without removing the relationship?

Thanks

 

[jdraw]

Write down in one or two lines exactly WHAT you are trying to do in business terms.

Don't get hung up on Access or database terms. Your relationships should represent some fact or rule that is inherent in your business.


eg 1 Customer can make 1 or many Orders
This represents a 1 to many relationship between Customer and Order

What is the structure of your tables

tblSales and tblTelephony?

See this link for a generic view of Overlapping times.

 

[CanadianAccessUser]

I am trying to create a report showing each agent's MTD Telephony data along with their MTD sales.

[tblTelephony] -------all fields required in report except RecordID (PK)
RecordID(PK)
TelephonyDate
AgentName
CallsAnswered
CallsTransferred
HeldCalls
AHT
ACDTime
ACW
HoldTime
StaffTime
AvailTime
AuxTotal
Aux0
Aux1
Aux2
Aux3
Aux4
Aux5
Aux6
Aux7
Aux8
Aux 9

[tblSales]
SalesEntry# (PK)
OrderDate
InstallDate
AgentName
RGUTotal
PSU ------Sales data required to show in report as a Sum
ANCTierA
ANCTierB
TotalANC
iNAv/IknowCompliance

Thank you for your time on this. Your help and advise is always appreciated.

 

[CanadianAccessUser]

According to your link, I believe the SQL I'm looking for is:
SELECT AgentName, OrderDate, InstallDate, PSU
FROM tblSales
WHERE OrderDate <= Forms!NavPage.Text8 And InstallDate >= Forms!NavPage.Text6
I'm going to try it... I'll let you know if it works.

 

[CanadianAccessUser]

I ended up putting the dates in queries with parameters for the date range then using another query without dates for my report and it worked wonderfully.