Ambiguous Column Names Error

Question

1.00/5 (1 vote)

See more:

Hi, I'm trying to select the Max of a column that stores an Id of reunions so it can pass that value always when submitting in other forms Attendance, names of people etc

Here's the code

VB

("SELECT MAX(ASAM_ID_ASAMBLEA) AS ASAM_ID_ASAMBLEA FROM tblCOOPERATIVA " & _
                                                      "JOIN tblASAMBLEA  ON tblASAMBLEA.ASAM_ID_ASAMBLEA = tblCOOPERATIVA.ASAM_ID_ASAMBLEA", conn)

Please Help. Thank you, if you need any more info let me know

The main column name is ASAM_ID_ASAMBLEA then on the other tables I have the same Column name

I just want to pass that first ASAM_ID_ASAMBLEA value into the other and always looking the MAX (or last one register) into my other tables

Posted 5-Apr-13 3:01am

TesterJSR

Updated 5-Apr-13 3:46am

v5

Add a Solution

Comments

CHill60 5-Apr-13 9:22am

Does table tblCOOPERATIVA actually have a column called MaxId?

TesterJSR 5-Apr-13 9:36am

no

TesterJSR 5-Apr-13 9:39am

I have changed it but it is still ambiguous

("SELECT MAX(ASAM_ID_ASAMBLEA) AS ASAM_ID_ASAMBLEA FROM tblCOOPERATIVA " & _
"JOIN tblASAMBLEA ON tblASAMBLEA.ASAM_ID_ASAMBLEA = tblCOOPERATIVA.ASAM_ID_ASAMBLEA", conn)

CHill60 5-Apr-13 9:42am

Could you use the Improve question link to post the table schema for these tables so I can see if I can point you in the right direction

TesterJSR 5-Apr-13 9:44am

Sure

TesterJSR 5-Apr-13 9:47am

Improved

CHill60 5-Apr-13 9:55am

Ok - you're problem is that you were trying to join on a non-existant column. If I've got you right then you want to get details from some other tables for the last row added to tblASAMBLEA ...

You could try

SELECT * FROM tblCOOPERATIVA -- substitute appropropriate table name

where ASAM_ID_ASAMBLEA = (select MAX(ASAM_ID_ASAMBLEA) FROM ASAM_ID_ASAMBLEA)or

SELECT TOP 1 * FROM tblCOOPERATIVA -- substitute appropropriate table name

ORDER BY ASAM_ID_ASAMBLEA DESC

TesterJSR 5-Apr-13 10:24am

you solution is working but I'm getting a 0 on the column when it supposed to get the MAX which is 29 right now

Plz help thank again

CHill60 5-Apr-13 10:42am

Strange! Have you seen Guy Thiebaut's comment below ... if you can do that then at least you'll have two of us looking at it! In the meantime is there any chance that one of the rows has NULL in ASAM_ID_ASAMBLEA? If so then use MAX(ISNULL(ASAM_ID_ASAMBLEA, 0)). Also if you got an email with my comment in then I amended it immediately after to put DESC on the order by, which won't have been emailed to you

GuyThiebaut 5-Apr-13 10:13am

I posted a solution but on reading your question again I realised that I did not understand what it is you are trying to do.

Could you post an example with example data from both tables and the result you expect(do not post a query), then it should be fairly straightforward to show you the query required.

Thanks

ZurdoDev 5-Apr-13 11:06am

You just need to add which table to SELECT MAX(ASAM_ID_ASAMBLEA), right? This seems trivial. What am I missing?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Answer 1 · 2013-04-06T11:48:00

Solution 2

The MAX(ASAM_ID_ASAMBLEA) part of your query does not now from which table to take the field. You need to specify the table name in front of the field.

So either use

SQL

MAX(blASAMBLEA.ASAM_ID_ASAMBLEA)

or

SQL

MAX(tblCOOPERATIVA.ASAM_ID_ASAMBLEA)

Posted 6-Apr-13 11:48am

André Kraak

Comments

Maciej Los 6-Apr-13 17:56pm

+5!