New Query in Access Database

[dnlallred]

I need to join two tables in a query, MiscCharge Table and the RentalStatus Table. The ID of the Status table is a FPK on the MiscCharge Tabel
The query should take a date range and then filter the results to Status that occur during that time range

START[tab][tab]1/6/13|--------------------------------------------------------------|1/12/13 STOP
1/1/13|--------------|-----------------|1/6/13|-------------------------------------|------------------|1/14/13
1/1/13|--------------|----------------------------------------------------------------|-----------------------------------1/16/2013
1/8/13|------------------------------------------|1/10/13 |

If a status overlaps on either side it would need to cut off the status start date and status end date where they overlap.
The MiscCharge Date also has a bill date that would need to be between the start and stop dates in order to

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dnlallred]

Yes i am stuck on how to combine these tables and not miss any data. The report will be used to know what needs to be billed for a Date Range.

PARAMETERS [@sdate] DateTime, [@edate] DateTime, [@billtoid] Text ( 255 );
SELECT 
tAsset_Status.[Asset ID], 
SELECT [tAsset_Terminal].[Asset Terminal] AS Terminal FROM [tAsset_Terminal] WHERE (([tAsset_Status].[Asset ID] = [tAsset_Terminal].[Asset ID]) AND (([tAsset_Status].[Status Start Date] BETWEEN
[tAsset_Terminal].[Terminal Start Date] AND [tAsset_Terminal].[Terminal End Date]) OR ([tAsset_Status].[Status End Date] BETWEEN [tAsset_Terminal].[Terminal Start Date] AND [tAsset_Terminal].[Terminal End
Date])))) AS Terminal, 
tAsset_Status.[Status Type], 
tAsset_Status.[Customer ID], 
tAsset_Status.[Salesman ID], 
tAsset_Status.[Rental Rate], 
tAsset_Status.[Well Bill To], 
tAsset_Status.[Load Location], 
tAsset_Status.[Unload Location], 
tAsset_Status.State, 
IIf(IsNull(tMiscCharge.ChargeType),'',tMiscCharge.ChargeType) AS MiscChargeType, 
IIf(IsNull([tMiscCharge].[ChargeQty]),0) AS [Misc Charge Qty], 
IIf(IsNull([tMiscCharge].[ChargeRate]),0) AS [Misc Charge Rate], 
IIf(IsNull(tMiscCharge.BillDate),'') AS MiscBillDate, 
IIf(IsNull(tMiscCharge.Invoiced),'') AS [Invoiced Date], 
([Rental Rate]*(([End Date]-[Start Date])+1)+([Misc Charge Qty]*[Misc Charge Rate])) AS Total, 
IIf([Status Start Date]<=[@sdate] And [Status End Date]>=[@sdate],[@sdate[highlight #4E9A06]],[Status Start Date]) AS [Start Date], 
IIf([Status End Date]>=[@edate] And [Status Start Date]<=[@edate],[@edate],[Status End Date]) AS [End Date], 
tAsset_Status.[Status Start Date], tAsset_Status.[Status End Date]

FROM tAsset_Status LEFT JOIN tMiscCharge ON tAsset_Status.ID = tMiscCharge.StatusID
WHERE (((tAsset_Status.[Customer ID]) Like "*" & [@billtoid]) AND 
  ((tAsset_Status.[Status Start Date])<=[@edate]) AND ((tAsset_Status.[Status End Date])>=[@sdate]) AND 
  ((IIf(IsNull([tMiscCharge].[Invoiced]),''))="") AND ((IIf(IsNull([tMiscCharge].[BillDate]),''))>=[@sdate] And (IIf(IsNull([tMiscCharge].[BillDate]),''))<=[@edate]));

 

[PHV]

Seems like you don't know the Nz function, ie instead of this:
IIf(IsNull(tMiscCharge.ChargeType),'',tMiscCharge.ChargeType)
use this:
Nz(tMiscCharge.ChargeType,'')

I don't understand why you use a (syntaxically incorrect) subquery for Terminal

What is [highlight #4E9A06] ?

Lastly, what is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dnlallred]

I have solved this problem by using a Union. It allows me to get all rows from both tables so i have no repeating data.