G
Guest
I am creating a query that returns me a total cost accrued from two tables
that give me the amount of days (Date ()-Arrival Date) times a specified
rate. It also shows me how many trailers are at any given PDC (city) that I
choose. However, I cannot get the report to show me more than seven
trailers. I have as many as forty at any single City, but it returns the
number as 7 every time?
here is the SQL
SELECT Archive.PDC, Count(Archive.[Trailer ID]) AS [CountOfTrailer ID],
Sum([Rates]![Rate/Day]*IIf(Date()-[Archive]![Arrival Date]>[Rates]![Free
Days],Date()-[Archive]![Arrival Date]-[Rates]![Free Days],0)) AS totalRate
FROM Archive, Rates
GROUP BY Archive.PDC, Archive.[Departure Date], Archive.[Trailer ID]
HAVING (((Archive.PDC)=[Type in PDC]) AND ((Archive.[Departure Date]) Is
Null) AND ((Archive.[Trailer ID]) Between [Type the beginning date:] And
[Type the end date:]));
that give me the amount of days (Date ()-Arrival Date) times a specified
rate. It also shows me how many trailers are at any given PDC (city) that I
choose. However, I cannot get the report to show me more than seven
trailers. I have as many as forty at any single City, but it returns the
number as 7 every time?
here is the SQL
SELECT Archive.PDC, Count(Archive.[Trailer ID]) AS [CountOfTrailer ID],
Sum([Rates]![Rate/Day]*IIf(Date()-[Archive]![Arrival Date]>[Rates]![Free
Days],Date()-[Archive]![Arrival Date]-[Rates]![Free Days],0)) AS totalRate
FROM Archive, Rates
GROUP BY Archive.PDC, Archive.[Departure Date], Archive.[Trailer ID]
HAVING (((Archive.PDC)=[Type in PDC]) AND ((Archive.[Departure Date]) Is
Null) AND ((Archive.[Trailer ID]) Between [Type the beginning date:] And
[Type the end date:]));