Query Trouble

  • Thread starter Thread starter Guest
  • Start date Start date
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:]));
 
JIT,

I see two potential sources of trouble here.

First, you have a parameter query that prompts for the entry of dates,
and applies this as criteria to the Trailer ID field. Shouldn't these
parameter criteria be in the Arrival Date field?

Second, you are Counting the Trailers, and also Grouping By the Trailer
ID. This doesn't seem right?
 
Back
Top