How to make a reference to another column in the same query?

[fluidmind]

Hey Guys!

I have a problem concerning the ability to make a reference to another column in the same query.

I have two columns. Column 1 works perfectly, but in column 2 I want the outcome of column1 (coachingdato1) inserted instead of #17-07-2006#.

How is that done???

Thanks in advance!

//JR

Column1:

coachingdato1: (SELECT Max(coaching.dato) FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr)

Column2:

coaching1: (SELECT coaching.[1] FROM coaching WHERE sælgerdata.sælgernr = coaching.sælgernr AND #17-07-2006#  = coaching.dato)

 

[Jibbadiah]

Because it is an aggregate function you may run into problems with a single query. It may be that the simplest solution is to keep the first query as a standalone query and reference the value of your first query in a 2nd query.

 

[fluidmind]

Okay. Thanks!

I will try that!

// JR

 

[fluidmind]

Still can't get it to work

Hi again!

Well, I just tried your idea, but I still can't get it to work. The error is allways the same: "You have tried to run a query, that doesn't include the given expression "" as a part of the aggregate function" (my translation from the danish version of Access).

I've attached a jpg showing the outcome of the query (maybe the names of the columns have changed a bit since i posted this thread, but please ignore that). Instead of the '?????' I want 'coaching.[1]' inserted where the date of the left field corresponds to the 'coaching.dato'.

I really hope you can help me on this one...

// JR

 

[Jibbadiah]

Don't quite know what you are after... but maybe try putting the following formula as an extra field in your query... and tell me if it is what you want?

test: IIf(IsNull([coaching1A] And [coachingdato2]<>""),[coachingdato2],[coaching1A])

Be sure to keep it as "group by" in the total field.

 

[fluidmind]

Didn't work!

Hi again!

No, that didn't do the trick. Now it opens a box where I can manually enter the date... I think I might be thinking it all wrong. So now I'll tell you what I want and you may come up with an idea

I've got two tables:

Table - Emplyeedata:
[employeenumber], [firstname], [lastname]

Table - Coaching:
[employeenumber], [Date],[1],[2],[3], [coach]

My query is beeing used for a report containing the employee's name, the date of his/her last coaching and the first, second and third priority issues ([1],[2],[3]) that was talked about during the last coaching!!!!. If the emplyee has never before been coached (as was the case in the attached image) the first, second and third priority issues must of course be left blank!

Is there another way to do this???

 

[Jibbadiah]

I'm not surprised it didn't work... all of your field names have changed from the jpeg that you posted... lol.

I have to go... so will give you a quick and easy.

Create Query 1. an aggregate query where you only select employee id and max of date.

Create Query 2. A Select query where you choose Name from employee table and all details from the coaching table where the following joins are made:
- Employee.employeeid = Query1.employeeid
- Coaching.date = Query1.maxOfDate
- Employee.employeeid = Coaching.Employeeid

This creates an inner join, so anyone that hasn't been coached should end up being ignored... presume that is what you want?!

Tell me how you get on.

 

[fluidmind]

Thanks so much

Hi there!

Sorry I didn't answer you before, but I just took a small vacation. I started my programming just 35 minutes ago, and I tried your suggestion right away... And it works!!

You don't know how many hours of blood, swet and tears I've spend on not comming up with a solution. But now I got it thanks to you!

Thank you, thank you, thank you...

// JR

 

[fluidmind]

Well, it almost works...

Hi again!

Now I've run into a very small but yet important problem, that I've spend some time on trying to fix:

The [employeenumber], [firstname] and [lastname] in my query are only showed if the employees have been coached at least once before. I've made a left-join from 'employeedata' to 'coaching', which in my opinion should fix the problem...

But it doesn't do the trick :-(

 

[Jibbadiah]

Suspect that this is because you have criteria of the max of the coaching date. You can't apply criteria to a resultset that doesn't include a value. Make a third query that has an outerjoin from the employee table to the 2nd query that you created earlier.

 

[fluidmind]

Thanks again...