Ah, there is a small problem. We have, here, TWO queries, each one starting
with its own SELECT keyword.
For debugging, each query can be written alone. So, make a new query for
SELECT trailerID, SUM(rate*iif(toDate-fromDate>FreeDays,
toDate-fromDate-freeDays, 0)) As totalRate
FROM tailers INNER JOIN carriers ON trailers.carrier=carriers.carrier
GROUP BY trailerID
(remember to use your real field names, those I supplied are just kind of
'place holder'.)
Once that query works fine, and the first one too, I assume you want to
'glue' the two results, vertically? If so, try:
SELECT PDC, [CountOfTrailer ID]
FROM firstSavedQuery
UNION ALL
SELECT trailerID, totalRate
FROM secondSavedQuery
to so merge the two queries, or, if you prefer:
SELECT "count" as category, [CountOfTrailer ID]
FROM firstSavedQuery
UNION ALL
SELECT "totalRate", trailerID, totalRate
FROM secondSavedQuery
where the first field describe from which query it comes from.
Hoping it may help,
Vanderghast, Access MVP
JIT said:
Here it is, thank you so much for your help. This detention database is
crucial to my company.
SELECT Archive.PDC, Count(Archive.[Trailer ID]) AS [CountOfTrailer ID]
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:]))
SELECT trailerID, SUM(rate*iif(toDate-fromDate>FreeDays,
toDate-fromDate-freeDays, 0))
FROM tailers INNER JOIN carriers ON trailers.carrier=carriers.carrier
GROUP BY trailerID
Michel Walsh said:
Can you post the entire SQL statement (available if you switch into the
SQL
view, rather than the data view, or the design view, in the query
designer)
?
Vanderghast, Access MVP
it tell me i have a syntax error when i place SELECT
SUM(rate*iif(toDate-fromDate>FreeDays, toDate-fromDate-freeDays, 0))
FROM tailers INNER JOIN carriers ON trailers.carrier=carriers.carrier
in
the field.
:
That may be the field you mentionned as the 'trailer name'. The
resulting
expression will be 'by trailer'. If you just want the total:
SELECT SUM(rate*iif(toDate-fromDate>FreeDays,
toDate-fromDate-freeDays,
0))
FROM tailers INNER JOIN carriers ON trailers.carrier=carriers.carrier
but then, you can hardly check that the sum is right.
Having no explicit GROUP BY clause, as in this last query, while an
aggregate is used is equivalent to have a single group, accepting all
records left after the evaluation of the WHERE clause.
Note that if your post is the continuation of some other posts, be
aware
that I have no knowledge of what was discussed in these previous
posts.
Vanderghast, Access MVP
You will have to excuse me,
I do not know what you mean by this. Do i add Trailer ID to the
field
list in the query, and then write
SUM(rate*iif(toDate-fromDate>FreeDays,
toDate-fromDate-freeDays, 0)) in the criteria field? Also, FROM
tailers
INNER JOIN carriers ON trailers.carrier=carriers.carrier GROUP BY
trailerID. What does this mean and where to I place it in the
query?
Thank you
:
SELECT trailerID, SUM(rate*iif(toDate-fromDate>FreeDays,
toDate-fromDate-freeDays, 0))
FROM tailers INNER JOIN carriers ON
trailers.carrier=carriers.carrier
GROUP BY trailerID
where I assumed your table Trailers has relevant fields {
trailerID,
fromDate, toDate, carrier }
and your table Carriers the relevant fields { carrier, rate,
freeDays }
Hoping it may help,
Vanderghast, Access MVP
This one is very detailed. I am trying to run a query that gives
me
results
that contain the distribution center's name, the total number of
parked
trailers there, and then the total cost that those trailers have
accrued
for
that destribution center. The following is the Rates table that
I
am
pulling
from:
Carrier Rate/Day WKEND Free Days
Triple Crown $50.00 None 2
Alliance $50.00 Included 4
B&W $50.00 None 0
Fourteenth Avenue $35.00 None 3
Tom Donahue $25.00 None 0
JDC Logistics $25.00 None 5
Schneider $25.00 None 5
I also am pulling from an archive table. The table contains the
DC's
name,
trailer numbers, and Carrier(i.e. alliance, Schneider). I need
to
know
how
to get a calculated field in a query by matching the Carrier from
the
Rates
table with the Carrier from the archive table. Then multiplying
that
carriers Rate/Day by the total days that each trailer in the
Archive
table
has been at that DC. Then, I need to add all the trailers
accrued
charges
so
that I can just report a total Accrual per DC.
Thank You.
