More Query Questions

J

JIT

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.
 
M

Michel Walsh

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
 
J

JIT

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
 
M

Michel Walsh

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
 
J

JIT

it tell me i have a syntax error when i place SELECT
SUM(rate*iif(toDate-fromDate>FreeDays, toDate-fromDate-freeDays, 0))
 
M

Michel Walsh

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
 
J

JIT

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
 
M

Michel Walsh

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
 
J

JIT

I am getting this ERROR: SYNTAX error in JOIN operation.

It then highlights the word RATES in the JOIN operation.

This is the following Query statement with the correct table names:

SELECT trailerID, SUM(rate*iif(Date()-ArrivalDate>FreeDays,
Date()-ArrivalDate-freeDays, 0)) As totalRate
FROM Archive INNER JOIN carrier ON archive.carrier=rates.carrier
GROUP BY trailerID;



Michel Walsh said:
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.
 
M

Michel Walsh

yes, indeed, should be:

FROM Archive INNER JOIN carrier ON archive.carrier=carrier.carrier


the syntax of the ON involving the tables we just mentionned in the INNER
JOIN, which are, it seems, Archive and Carrier.


Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top