473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Error: Not a Group By Expression

6
I am using 2 tables linked together by the vendor id.
I want the vendor to display along with the other fields and the vendor type from the second file.
I have a vendor that shows 2 lines b/c it has 2 types. I understand why it shows 2 times, but would like to know if it is possible to have those 2 types on the same line in the same column. Ex:
Type(s)
---------------
OTR,Dray

I was hoping the GROUP BY by might cover that but it doesn't like it.
Any help would be appreciated.

SELECT vndmt.VENDOR_ID,
vndmt.VENDOR_NAME,
vndmt.CITY_NAME,
vndmt.STATE_CODE,
vndmt.OTR_CONTRACT_FLAG,
vndmt.DRAYAGE_CONTRACT_FLAG,
vndmt.LTL_CONTRACT_FLAG,
vndmt.BROKER_CONTRACT_FLAG,
vndmt.SCAC_CODE,
vndmt.INACTIVATE_FLAG,
vndmt.HAZMAT_EXPIRY_DATE,
vndmt.IS_HOUSE_HOLD_GOODS,
vndtype.VENDOR_TYPE
FROM MIL_VENDOR_TYPE_ML vndtype RIGHT OUTER JOIN MIL_VENDOR_MT vndmt
ON vndmt.vendor_id = vndtype.vendor_id
GROUP BY vndmt.vendor_id
ORDER BY vndmt.vendor_id;

Heres the output
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BKR <--Type (1)
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BNB<-- Type (2)
Dec 26 '06 #1
1 2197
b1randon
171 Expert 100+
I am using 2 tables linked together by the vendor id.
I want the vendor to display along with the other fields and the vendor type from the second file.
I have a vendor that shows 2 lines b/c it has 2 types. I understand why it shows 2 times, but would like to know if it is possible to have those 2 types on the same line in the same column. Ex:
Type(s)
---------------
OTR,Dray

I was hoping the GROUP BY by might cover that but it doesn't like it.
Any help would be appreciated.

SELECT vndmt.VENDOR_ID,
vndmt.VENDOR_NAME,
vndmt.CITY_NAME,
vndmt.STATE_CODE,
vndmt.OTR_CONTRACT_FLAG,
vndmt.DRAYAGE_CONTRACT_FLAG,
vndmt.LTL_CONTRACT_FLAG,
vndmt.BROKER_CONTRACT_FLAG,
vndmt.SCAC_CODE,
vndmt.INACTIVATE_FLAG,
vndmt.HAZMAT_EXPIRY_DATE,
vndmt.IS_HOUSE_HOLD_GOODS,
vndtype.VENDOR_TYPE
FROM MIL_VENDOR_TYPE_ML vndtype RIGHT OUTER JOIN MIL_VENDOR_MT vndmt
ON vndmt.vendor_id = vndtype.vendor_id
GROUP BY vndmt.vendor_id
ORDER BY vndmt.vendor_id;

Heres the output
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BKR <--Type (1)
168 C S X INTERMODAL ATLANTA GA N N CSXO N 30-JUN-08 N BNB<-- Type (2)
The only way I can think of to get them into the same column is to use a stored procedure and iterate through your rows, appending the different types when all the other columns match. I don't think there's a way to do it with SQL, but I'm not quite a guru with that so someone else may know.
Jan 3 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
1
by: Rolan | last post by:
I created a function module base on a query to provide a group record total for a subform and which is posted to a field on the main form. Everything seems to work fine except that I consistently...
8
by: Dalan | last post by:
Please help - just take a quick look at the function code below. It probably just needs some minor tweaking. The function module is based on an intermediate query to provide a group record...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
7
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is...
3
by: ncsthbell | last post by:
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I hope someone can be so kind to guide me!!! I...
4
by: thebarefootnation | last post by:
Hi I have the following error message "Syntax Error (missing operator) in query expression" occurring when I am trying to update combo box within a form. My code is: Dim strSQL As String
3
by: ylekot88 | last post by:
Hi I am a student, I am trying to figure this out. I am trying to run a select statement that is asking for customer#, date, and total price of each order. ensure results are in chronological...
3
by: XolileWarren | last post by:
Hi Everyone I'm trying to create a stored procedure that will delete groups that are in-active for a month. Table description i have two table which are linked together using groupID, when...
7
by: Yesurbius | last post by:
I am receiving the following error when attempting to run my query. In my mind - this error should not be happening - its a straight-forward query with a subquery. I am using Access 2003 with all...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.