How to use certain criteria in a query...

[liqwid253]

This is probably so easy, but the forum search was down sorry.

I have a table organized by jobs, each record has info as to what person did the job and the position they served (which has several columns for different types).

So I want to create a report that is organized by person and listing the jobs that they are qualified on.

But it currently organizes the report by job:

Name Supervisor Lead Trainee
Bill Job1 Tom
Tom Job2 Bill Larry
Larry Tom

I need:

Name Supervisor Lead Trainee
Bill Job1
Bill Job2
Tom Job3
Tom Job1
Larry Job2


I hope this was clear
Thanks in advance

 

[GingerR]

Can you please tell us how the data in the table is formatted?

Your examples above are confusing, maybe because the column headings are not lined up with the data. In the first example I do not see a column heading for Job? How does Job3 get into the second example? I do not see a Job3 in the first example.

Thanks.

 

[liqwid253]

I'm sorry, it seems to have lost the formatting.

The table looks like this:

Job#|Date|Supervisor|Lead|
1 |2/3 |Jim |Tom|
2 |2/5 |Tom |Jim |
3 |2/6 |Larry |Tom|
4 |2/7 |Jim |Tom|

The report on personnel and their qualifications is currently ordered by first by name, then by Job:

|Supervisor|Lead|Job|Date
|Jim |Tom |1 |2/3
|Jim |Tom |4 |2/7
|Larry |Tom |3 |2/6
|Tom |Jim |2 |2/5

I need to pull every instance of a person and their qualification no matter where in the table and organize just by their name:

|Name|Position|Job|Date
|Jim |Supv. |1 |2/3
|Jim |Supv. |4 |2/7
|Jim |Lead |2 |2/5
Larry |Supv. |3 |2/6
|Tom |Supv |2 |2/5
|Tom |Lead |1 |2/3
|Tom |Lead |3 |2/6
|Tom |Lead |4 |2/7

 

[PHV]

Something like this ?
SELECT Supervisor As Name, 'Supv.' AS Position, [Job#] As Job, [Date] FROM tblJobs
UNION SELECT Lead, 'Lead', [Job#], [Date] FROM tblJobs
ORDER BY 1;

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

 

[liqwid253]

Thanks PHV!

Thats real close, but it seems to aggregate my position fields into one field. I need the names in one, but not the positions. Heres my sql without the union select:

(The AWO/WD and AWO/WD Inst/Eval are the two job columns containing names) I need the query to search and organize by name from both columns, but also show which column or job the name is in.

SELECT [Mission Log Table (LIVE)].[MSN Number Id], [Mission Log Table (LIVE)].Date, [Mission Log Table (LIVE)].[Mission Type], [Mission Log Table (LIVE)].[AWO/WD], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval], [Mission Log Table (LIVE)].[Live Int Accomplished], [Mission Log Table (LIVE)].[Live Int requirements], Sum(([Live Int requirements])-([Live Int Accomplished])) AS [Live Int Needed], [Mission Log Table (LIVE)].[Live AAR Accomplished], [Mission Log Table (LIVE)].[Live AAR requirements], Sum([Live AAR requirements]-[Live AAR Accomplished]) AS [Live AAR Needed], [Mission Log Table (LIVE)].[Live AADS Accomplished], [Mission Log Table (LIVE)].[Live AADS requirements], Sum([Live AADS requirements]-[Live AADS Accomplished]) AS [Live AADS Needed], [Mission Log Table (LIVE)].[Live PP Accomplished], [Mission Log Table (LIVE)].[Live PP requirements], Sum([Live PP requirements]-[Live PP Accomplished]) AS [Live PP Needed], [Mission Log Table (LIVE)].[Live CC Accomplished], [Mission Log Table (LIVE)].[Live CC Requirements], Sum([Live CC requirements]-[Live CC Accomplished]) AS [Live CC Needed]
FROM [Mission Log Table (LIVE)]
GROUP BY [Mission Log Table (LIVE)].[MSN Number Id], [Mission Log Table (LIVE)].Date, [Mission Log Table (LIVE)].[Mission Type], [Mission Log Table (LIVE)].[AWO/WD], [Mission Log Table (LIVE)].WDT, [Mission Log Table (LIVE)].SD, [Mission Log Table (LIVE)].SDT, [Mission Log Table (LIVE)].[AWO/WD Inst/Eval], [Mission Log Table (LIVE)].[WDT Inst/Eval], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval 1], [Mission Log Table (LIVE)].[WDT Inst/Eval 1], [Mission Log Table (LIVE)].[SD Inst/Eval], [Mission Log Table (LIVE)].[SDT Inst/Eval], [Mission Log Table (LIVE)].[Live Int Accomplished], [Mission Log Table (LIVE)].[Live Int requirements], [Mission Log Table (LIVE)].[Live AAR Accomplished], [Mission Log Table (LIVE)].[Live AAR requirements], [Mission Log Table (LIVE)].[Live AADS Accomplished], [Mission Log Table (LIVE)].[Live AADS requirements], [Mission Log Table (LIVE)].[Live PP Accomplished], [Mission Log Table (LIVE)].[Live PP requirements], [Mission Log Table (LIVE)].[Live CC Accomplished], [Mission Log Table (LIVE)].[Live CC Requirements]
HAVING ((([Mission Log Table (LIVE)].[Mission Type])="live"));

 

[GingerR]

Hi Liqwid--Your last statement is a bit confusing. You say you do not want the 'Positions' all in one column, yet that is how you display the results you are looking for in the post before PHV's post?

 

[liqwid253]

OMG Ginger, I'm sorry everyone!

what it should look like is this:

|AWO/WD|AWO/WD Inst/Eval|Mission|Date
|Jim | |1 |2/3
|Jim | |4 |2/7
| |Jim |2 |2/5
|Larry | |3 |2/6
|Tom | |2 |2/5
| |Tom |1 |2/3
| |Tom |3 |2/6
| |Tom |4 |2/7


I tried to simplify it down to just the concept but didn't show the disired results correctly, sorry!

 

[GingerR]

don't know if this is the slickest, but i enhanced PHV's union query to:

a) put the person's name in one column all the way to the left so it will sort by their name;

b) create a calculated field so that the items in the [AWO/WD] field will sort before items in the [AWO/WD Inst/Eval] field by making my calculated field called "PersonOrder" be = 1 if the person's name is in the [AWO/WD] field and a 2 if the person's name is in the [AWO/WD Inst/Eval] field;

c) separate the names into [AWO/WD] and [AWO/WD Inst/Eval] fields per your last request.

Just sub in the name of your table as Table2:

SELECT Table2.[AWO/WD] as Person,1 as PersonOrder,Table2.[AWO/WD], Null AS [AWO/WD Inst/Eval], Table2.Mission, Table2.Date
FROM Table2 UNION SELECT Table2.[AWO/WD Inst/Eval] as Person, 2, null AS [AWO/WD], [AWO/WD Inst/Eval], Table2.Mission, Table2.Date
FROM Table2;

my result is this:

           [b]AWO/WD   AWO/WD Inst/Eval  Mission	Date[/b]
Denny   1    Denny                     3        1/3/2004
Mark    2    Mark                      2        1/5/2004
Steve   1    Steve                     2        1/5/2004
Steve   2                Steve         1        1/1/2004
Steve   2                Steve         3        1/3/2004
Tom     1    Tom                       1        1/1/2004

 

[liqwid253]

Thank you so much Ginger and PHV!

slick = 'WORKS!'

 

[liqwid253]

Hey,

Ginger's solution works great, but only without my sum statements. You can see them above. If I'm doing the Union Select, where do I plug in the sum functions so it doesn't try to aggregate those? I keep getting the: "you tried to execute a query that does not include the specified expression "person" as part of an aggregate function."

Thanks !!

 

[lespaul]

once you add in your sum statements, you need to add a GROUP BY clause with each field name that ISN'T aggregated:

SELECT SUM(FIELD1), FIELD2 FROM tblName GROUP BY FIELD2

if you add a field, you have to add that field to the Group By:

SELECT SUM(FIELD1), FIELD2, FIELD3 FROM tblName GROUP BY FIELD2, FIELD3

Leslie

 

[liqwid253]

Thanks Leslie!

Forgive me for my abundance of dumb questions but, does that mean that I should include the sum statements in both the SELECT and UNION SELECT portions of my query and then use the GROUP BY to protect them. Or just add them to one statement, or in their own statement???

 

[lespaul]

what is the actual query you are trying to run, with the SUM fields included, which gives you the aggregate error?

Leslie

 

[liqwid253]

Here is the query I'm trying to run, the whole things works when I take out all the sum and group by statements. When I run this one I get the "you tried to execute a query that does not include the specified expression "person" as part of an aggregate function."


SELECT [Mission Log Table (LIVE)].[AWO/WD] as Person, 1 as PersonOrder, [Mission Log Table (LIVE)].[AWO/WD], Null AS [AWO/WD Inst/Eval], [Mission Log Table (LIVE)].[MSN Number Id], [Mission Log Table (LIVE)].Date, [Mission Log Table (LIVE)].[Mission Type], [Mission Log Table (LIVE)].WDT, [Mission Log Table (LIVE)].SD, [Mission Log Table (LIVE)].SDT, [Mission Log Table (LIVE)].[WDT Inst/Eval], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval 1], [Mission Log Table (LIVE)].[WDT Inst/Eval 1], [Mission Log Table (LIVE)].[SD Inst/Eval], [Mission Log Table (LIVE)].[SDT Inst/Eval], [Mission Log Table (LIVE)].[Live Int Accomplished], [Mission Log Table (LIVE)].[Live Int requirements], Sum(([Live Int requirements])-([Live Int Accomplished])) AS [Live Int Needed], [Mission Log Table (LIVE)].[Live AAR Accomplished], [Mission Log Table (LIVE)].[Live AAR requirements], Sum([Live AAR requirements]-[Live AAR Accomplished]) AS [Live AAR Needed], [Mission Log Table (LIVE)].[Live AADS Accomplished], [Mission Log Table (LIVE)].[Live AADS requirements], Sum([Live AADS requirements]-[Live AADS Accomplished]) AS [Live AADS Needed], [Mission Log Table (LIVE)].[Live PP Accomplished], [Mission Log Table (LIVE)].[Live PP requirements], Sum([Live PP requirements]-[Live PP Accomplished]) AS [Live PP Needed], [Mission Log Table (LIVE)].[Live CC Accomplished], [Mission Log Table (LIVE)].[Live CC Requirements], Sum([Live CC requirements]-[Live CC Accomplished]) AS [Live CC Needed]
FROM [Mission Log Table (LIVE)]
UNION SELECT [Mission Log Table (LIVE)].[AWO/WD Inst/Eval] as Person, 2, null AS [AWO/WD], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval], [Mission Log Table (LIVE)].[MSN Number Id], [Mission Log Table (LIVE)].Date, [Mission Log Table (LIVE)].[Mission Type], [Mission Log Table (LIVE)].WDT, [Mission Log Table (LIVE)].SD, [Mission Log Table (LIVE)].SDT, [Mission Log Table (LIVE)].[WDT Inst/Eval], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval 1], [Mission Log Table (LIVE)].[WDT Inst/Eval 1], [Mission Log Table (LIVE)].[SD Inst/Eval], [Mission Log Table (LIVE)].[SDT Inst/Eval], [Mission Log Table (LIVE)].[Live Int Accomplished], [Mission Log Table (LIVE)].[Live Int requirements], Sum(([Live Int requirements])-([Live Int Accomplished])) AS [Live Int Needed], [Mission Log Table (LIVE)].[Live AAR Accomplished], [Mission Log Table (LIVE)].[Live AAR requirements], Sum([Live AAR requirements]-[Live AAR Accomplished]) AS [Live AAR Needed], [Mission Log Table (LIVE)].[Live AADS Accomplished], [Mission Log Table (LIVE)].[Live AADS requirements], Sum([Live AADS requirements]-[Live AADS Accomplished]) AS [Live AADS Needed], [Mission Log Table (LIVE)].[Live PP Accomplished], [Mission Log Table (LIVE)].[Live PP requirements], Sum([Live PP requirements]-[Live PP Accomplished]) AS [Live PP Needed], [Mission Log Table (LIVE)].[Live CC Accomplished], [Mission Log Table (LIVE)].[Live CC Requirements], Sum([Live CC requirements]-[Live CC Accomplished]) AS [Live CC Needed]
FROM [Mission Log Table (LIVE)]
GROUP BY [Mission Log Table (LIVE)].[MSN Number Id], [Mission Log Table (LIVE)].Date, [Mission Log Table (LIVE)].[Mission Type], [Mission Log Table (LIVE)].[AWO/WD], [Mission Log Table (LIVE)].WDT, [Mission Log Table (LIVE)].SD, [Mission Log Table (LIVE)].SDT, [Mission Log Table (LIVE)].[AWO/WD Inst/Eval], [Mission Log Table (LIVE)].[WDT Inst/Eval], [Mission Log Table (LIVE)].[AWO/WD Inst/Eval 1], [Mission Log Table (LIVE)].[WDT Inst/Eval 1], [Mission Log Table (LIVE)].[SD Inst/Eval], [Mission Log Table (LIVE)].[SDT Inst/Eval], [Mission Log Table (LIVE)].[Live Int Accomplished], [Mission Log Table (LIVE)].[Live Int requirements], [Mission Log Table (LIVE)].[Live AAR Accomplished], [Mission Log Table (LIVE)].[Live AAR requirements], [Mission Log Table (LIVE)].[Live AADS Accomplished], [Mission Log Table (LIVE)].[Live AADS requirements], [Mission Log Table (LIVE)].[Live PP Accomplished], [Mission Log Table (LIVE)].[Live PP requirements], [Mission Log Table (LIVE)].[Live CC Accomplished], [Mission Log Table (LIVE)].[Live CC Requirements];

Thanks!!!
Lon

 

[lespaul]

I'm pretty sure you'll need the group by in BOTH statements, not just the last one.


Leslie

 

[liqwid253]

Yeah, I think you're right because it makes you put it that way. But any ideas as to why I keep getting the error about Person as an aggregate function? Can anybody see what's wrong with this??

Lon

 

[lespaul]

So you're saying that if you add the GROUP BY to BOTH statements like below, you get the error?

SELECT 
A.[AWO/WD] as Person, 1 as PersonOrder, A.[AWO/WD], Null AS [AWO/WD Inst/Eval], A.[MSN Number Id], A.Date, 
A.[Mission Type],  A.WDT, A.SD, A.SDT,  A.[WDT Inst/Eval], A.[AWO/WD Inst/Eval 1], A.[WDT Inst/Eval 1], 
A.[SD Inst/Eval], A.[SDT Inst/Eval], A.[Live Int Accomplished], A.[Live Int requirements], 
Sum(([Live Int requirements])-([Live Int Accomplished])) AS [Live Int Needed], 
A.[Live AAR Accomplished], A.[Live AAR requirements], 
Sum([Live AAR requirements]-[Live AAR Accomplished]) AS [Live AAR Needed], A.[Live AADS Accomplished], 
A.[Live AADS requirements], Sum([Live AADS requirements]-[Live AADS Accomplished]) AS [Live AADS Needed], 
A.[Live PP Accomplished], A.[Live PP requirements], 
Sum([Live PP requirements]-[Live PP Accomplished]) AS [Live PP Needed], A.[Live CC Accomplished], 
A.[Live CC Requirements], Sum([Live CC requirements]-[Live CC Accomplished]) AS [Live CC Needed]

FROM [Mission Log Table (LIVE)] As A

GROUP BY
A.[AWO/WD], PersonOrder, A.[AWO/WD], [AWO/WD Inst/Eval], A.[MSN Number Id], A.Date, 
A.[Mission Type], A.WDT, A.SD, A.SDT,  A.[WDT Inst/Eval], A.[AWO/WD Inst/Eval 1], A.[WDT Inst/Eval 1], 
A.[SD Inst/Eval], A.[SDT Inst/Eval], A.[Live Int Accomplished], A.[Live Int requirements],  
A.[Live AAR Accomplished], A.[Live AAR requirements], A.[Live AADS Accomplished], A.[Live AADS requirements], A.[Live PP Accomplished], A.[Live PP requirements], A.[Live CC Accomplished], A.[Live CC Requirements]

UNION 

SELECT 
B.[AWO/WD Inst/Eval], 2, null AS [AWO/WD], B.[AWO/WD Inst/Eval], B.[MSN Number Id], B.Date, 
B.[Mission Type],  B.WDT, B.SD, B.SDT,  B.[WDT Inst/Eval], B.[AWO/WD Inst/Eval 1], B.[WDT Inst/Eval 1], 
B.[SD Inst/Eval], B.[SDT Inst/Eval], B.[Live Int Accomplished], B.[Live Int requirements], 
Sum(([Live Int requirements])-([Live Int Accomplished])), B.[Live AAR Accomplished], 
B.[Live AAR requirements], Sum([Live AAR requirements]-[Live AAR Accomplished]), B.[Live AADS Accomplished], B.[Live AADS requirements], 
Sum([Live AADS requirements]-[Live AADS Accomplished]), B.[Live PP Accomplished], 
B.[Live PP requirements], Sum([Live PP requirements]-[Live PP Accomplished]), 
B.[Live CC Accomplished], B.[Live CC Requirements], 
Sum([Live CC requirements]-[Live CC Accomplished])

FROM [Mission Log Table (LIVE)] As B

GROUP BY B.[MSN Number Id], B.Date, B.[Mission Type], B.[AWO/WD], B.WDT, B.SD, B.SDT, B.[AWO/WD Inst/Eval], 
B.[AWO/WD Inst/Eval], PersonOrder, B.[AWO/WD], B.[AWO/WD Inst/Eval], B.[MSN Number Id], B.Date, 
B.[Mission Type],  B.WDT, B.SD, B.SDT,  B.[WDT Inst/Eval], B.[AWO/WD Inst/Eval 1], B.[WDT Inst/Eval 1], 
B.[SD Inst/Eval], B.[SDT Inst/Eval], B.[Live Int Accomplished], B.[Live Int requirements], B.[Live AAR Accomplished], B.[Live AAR requirements], B.[Live AADS Accomplished], B.[Live AADS requirements], B.[Live PP Accomplished], B.[Live PP requirements], B.[Live CC Accomplished], B.[Live CC Requirements]

ps - I used an alias in both queries for your table name, a little easier to read!!! You also don't need the "As Something" for the calculated fields in the Second query.

Leslie