Combining Queries- i think!!

[frankis]

I'm trying to do a mail merge but am having trouble getting the info I need.Basically I am trying to run a query that will put the figures for the same grant ref into one line, ie that all items belonging to that grant will placed together.

GrantREf ItemType Yr1 Yr2 Yr3 Total
GG021 Salary 120 100 200 520
GG021 Course 50 50 50 150
Help me please!!
if this is easy i will be so peeved, its given me such a migraine!!

 

[Crowley16]

what, you mean like:

GG021, salary 120 100 200 520, course 50 50 50 150

I'm assuming both of these come from the same table?

use something like:
SELECT DISTINCT GrantRef, "ItemType - Salary", Yr1... , "ItemType - Course", Yr1...
FROM tblName as t1 JOIN tblName as t2 ON t1.GrantRef = t2.GrantRef

fidling needed...

--------------------
Procrastinate Now!

 

[frankis]

Crowley,
Thanks for that but I need spoonfeeding here, i'm just a newbie!!

 

[Crowley16]

that is spoonfeeding...

put that into a query, and experiment with the field values you select to see how to view/display the fields.

--------------------
Procrastinate Now!

 

[frankis]

SELECT DISTINCT GrantRef, "ItemType - Salary", Yr1... , "ItemType - Course", Yr1...
FROM tblName as t1 JOIN tblName as t2 ON t1.GrantRef = t2.GrantRef

"ItemType - Salary" What is this meant to do, its acting as an expression and appearing exactly as it is in the SQL

 

[Crowley16]

yes, it's just to label the y1, y2... fields that are salary or course. If you don't include these fields, then it's difficult to see which y1, y2 relate to which ItemType.

you can display it in various different ways depending on the intended use of the query...

--------------------
Procrastinate Now!

 

[frankis]

SORRY
when i try to run the suggested solution it brings up the same values for Salaries as it does for Consumables..

 

[PHV]

A starting point:
SELECT t1.*, t2.*
FROM tblName AS t1 INNER JOIN tblName AS t2 ON t1.GrantRef = t2.GrantRef
WHERE t1.ItemType = 'Salary' AND t2.ItemType = 'Course'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244