Improper Join Problem

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

Hello:

I am trying to run the following query:

SELECT tblBids_JobID, tblBids_BidDate AS BidYear, Sum
(tblBids_BidAmount) AS BidSum
FROM tblBids INNER JOIN (Select tblBids_1_JobID,
tblBids_1_EstimatorID, Min(tblBids_1_BidAmount) as MinBid
FROM tblBids_1 Group By tblBids_1_JobID,
tblBids_1_EstimatorID as M)
ON tblBids_JobID = m.JobID
AND tblBids_EstimatorID = m.EstimatorID
AND tblBids_BidAmount = m.MinBid
GROUP BY tblBids.tblBids_JobID,FORMAT
(tblBids_BidDate, "yyyy")


When I run it, I get the following error:

Syntax Error in JOIN operation

The cursor then moves to the word sum in the 1st line of
code. Any idea what I'm doing wrong

Thanks,
Brennan
 
Well, I think you have quit a few errors or possible errors in your SQL. In
addition, I am confused as to whether or not tblBids_JobId is a field in TblBids
or is supposed to represent the Table and field name. If the latter then you
would need to replace all the underscores with periods so that tblBids_JobId
becomes tblBids.JobId. Also, the same confusion seems to be there with
Tbl_bids_1_JobID vice tbl_bids_1.JobID as a field name.

Also, you need to move the parentheses so that As M in outside the subquery.
The restructured query might look something like:

SELECT tblBids_JobID,
Format(tblBids_BidDate,"YYYY") AS BidYear,
Sum(tblBids_BidAmount) AS BidSum
FROM tblBids INNER JOIN
(Select tblBids_1_JobID,
tblBids_1_EstimatorID,
Min(tblBids_1_BidAmount) as MinBid
FROM tblBids_1
Group By tblBids_1_JobID,
tblBids_1_EstimatorID) as M
ON tblBids_JobID = m.tblBids_1_JobID
AND tblBids_EstimatorID = m.TblBids_1_EstimatorID
AND tblBids_BidAmount = m.MinBid
GROUP BY tblBids.tblBids_JobID,
FORMAT(tblBids_BidDate, "yyyy")

Note that I formatted BidYear in the SELECT clause as well as in the GROUP BY
clause.
 
Are tblBids and tblBids_1 the same table with a self join?

To you really have fields tblBids_JobID and tblBids_BidDate or do you
really mean tblBids.JobID and tblBids.BidDate? You are using
different syntax for the two tables in the join, so this is confusing.
For example, you reference M.JobID, but the field in the subquery is
tblBids_1_JobID. This is what causes me to think you have inserted _
(underscore) where you mean . (dot)

My guess is that you really want.

SELECT B.JobID
, FORMAT(B.BidDate, "yyyy") AS BidYear
, Sum(B.BidAmount) AS BidSum
FROM tblBids B
INNER JOIN (SELECT B1.JobID
, B1.EstimatorID
, Min(B1.BidAmount) as MinBid
FROM tblBids_1 B1
Group By B1.JobID
, B1.EstimatorID) as M
ON B.JobID = m.JobID
AND B.EstimatorID = m.EstimatorID
AND B.BidAmount = m.MinBid
GROUP BY B.JobID
, FORMAT (B.BidDate, "yyyy")

--
HTH

Dale Fye


Hello:

I am trying to run the following query:

SELECT tblBids_JobID, tblBids_BidDate AS BidYear, Sum
(tblBids_BidAmount) AS BidSum
FROM tblBids INNER JOIN (Select tblBids_1_JobID,
tblBids_1_EstimatorID, Min(tblBids_1_BidAmount) as MinBid
FROM tblBids_1 Group By tblBids_1_JobID,
tblBids_1_EstimatorID as M)
ON tblBids_JobID = m.JobID
AND tblBids_EstimatorID = m.EstimatorID
AND tblBids_BidAmount = m.MinBid
GROUP BY tblBids.tblBids_JobID,FORMAT
(tblBids_BidDate, "yyyy")


When I run it, I get the following error:

Syntax Error in JOIN operation

The cursor then moves to the word sum in the 1st line of
code. Any idea what I'm doing wrong

Thanks,
Brennan
 
Is there an echo in here?

--
HTH

Dale Fye


Well, I think you have quit a few errors or possible errors in your
SQL. In
addition, I am confused as to whether or not tblBids_JobId is a field
in TblBids
or is supposed to represent the Table and field name. If the latter
then you
would need to replace all the underscores with periods so that
tblBids_JobId
becomes tblBids.JobId. Also, the same confusion seems to be there
with
Tbl_bids_1_JobID vice tbl_bids_1.JobID as a field name.

Also, you need to move the parentheses so that As M in outside the
subquery.
The restructured query might look something like:

SELECT tblBids_JobID,
Format(tblBids_BidDate,"YYYY") AS BidYear,
Sum(tblBids_BidAmount) AS BidSum
FROM tblBids INNER JOIN
(Select tblBids_1_JobID,
tblBids_1_EstimatorID,
Min(tblBids_1_BidAmount) as MinBid
FROM tblBids_1
Group By tblBids_1_JobID,
tblBids_1_EstimatorID) as M
ON tblBids_JobID = m.tblBids_1_JobID
AND tblBids_EstimatorID = m.TblBids_1_EstimatorID
AND tblBids_BidAmount = m.MinBid
GROUP BY tblBids.tblBids_JobID,
FORMAT(tblBids_BidDate, "yyyy")

Note that I formatted BidYear in the SELECT clause as well as in the
GROUP BY
clause.
 
Isss there there there an an echo echo in in in here here.....

The next question is did we solve the posters problem????
 
Back
Top