where else did people apply? 1

[BigRed1212]

I don't know how to even set this up.

I have data that look like this:

fname	ident	place	applied	accepted
bill	345	a	0	0
bill	345	b	0	0
bill	345	c	0	0
jane	234	d	1	1
jimmy	456	u	1	1
jimmy	456	f	1	1
jimmy	456	a	1	1
joe	123	g	1	1
joe	123	h	1	0
joe	123	b	0	0
jorge	678	e	1	0
tom	567	u	1	0
tom	567	i	1	0
tom	567	a	1	0

This is application data. In this data set, Tom applied to 3 places, and Joe to 2.

What I want to be able to do is to say in it's simplest form is: Of those who applied to place a, where else did they apply?
A little more complex it is: Of those who applied to place a, what are the top 3 places those people also applied to (maybe even in percentage terms)?

For this dataset, we have 2 applicants to place a (Bill did not apply). I want to be able to show that those two applicants also both applied to place u and that places f and i round out the top 3 choices.

My results presentation would look like:

Place applied to: a
Number of people that applied: 2
Most popular place that these people also applied to: u
Number that also applied to u: 2 (100%)
2nd most pop additional place: f
Number that also applied to f: 1 (50%)
3rd most pop additional place: i
Number that also applied to u: 1 (50%)

I think

SELECT top 3 place, count(place) as counter
from table1
where applied = 1
group by place
order by 2 desc

shows me the top places applied to by everybody, but I want this by place and people. The top 3 places for those who applied to place a, the top three for those who applied to place b, the top 3 for those who applied to place c, etc. I have 18,000 records but I only have 12 places so I'll run a working query 12 times (once for each place) if needed. I think some sort of self join is probably in order but I don't really get those or how to do it.

Let me know if something isn't clear or you have questions. Thanks for reading this far and thanks in advance if you can give me a suggestion or two on how to proceed.

 

[lameid]

qry Other Places:

Select FT.Place as Place, ST.Place as Other_Place_Appplied, Sum(ST.Place) as Applications_To_Other_Place
From Table1 as FT Inner Join Table1 as ST ON FT.ident = ST.ident
Where FT.Ident <> ST.Ident and ST.APPLIED <> 0
Group By FT.Place, ST.Place

qry Places:

Select Place, Sum(Applied) as Applications
From Table1
Group by Place

Qry Final:

Select [qry Places].Place, [qry Places].Applications, [qry Other Places].Other_Place_Appplied, [qry Other Places].Applications_To_Other_Place, [qry Other Places].Applications_To_Other_Place / [qry Places].Applications as Factor_Applied_To_Other_Place, 1 as Counter

From [qry Places] Left Join [qry Other Places] On [qry Places].Place = [qry Other Places].Place

Order By [qry Places].Place, [qry Other Places].Applications_To_Other_Place Desc

Make a report based on qry Final. Technically you can take the order by out of the query because you have to put it in the report.

Group By the place. Use Running sum on Counter field to determine first second etc.

I think I got all the elements.

 

[BigRed1212]

Thanks for the response.

qryPlaces:

Select Place, Sum(Applied) as Applications
From Table1
Group by Place

This one I get and it works. Returns a list of places and the number of apps to each place. Good.


qryOtherPlaces:

Select FT.Place as Place, ST.Place as Other_Place_Appplied, Sum(ST.Place) as Applications_To_Other_Place
From Table1 as FT Inner Join Table1 as ST ON FT.ident = ST.ident
Where FT.Ident <> ST.Ident and ST.APPLIED <> 0
Group By FT.Place, ST.Place

This one I don't get and it returns an empty recordset (but throws no errors).

So the only thing that shows up in qryFinal is the data in qryPlaces.

Ideas?

 

[lameid]

Oops, didn't pay close enough attention when I wrote that...

Select FT.Place as Place, ST.Place as Other_Place_Appplied, Sum(ST.Place) as Applications_To_Other_Place
From Table1 as FT Inner Join Table1 as ST ON FT.ident = ST.ident
Where [red]FT.Place <> ST.Place[/red] and ST.APPLIED <> 0
Group By FT.Place, ST.Place

 

[BigRed1212]

That throws "Data type mismatch in criteria expression".

 

[BigRed1212]

I changed sum to count and that may be something. Still looking.

 

[lameid]

I got tripped up on the alignement of the data... I meant sum of applied not place... but conisidering the criteria it looks like count of place should work just the same.

Weird nothing is jumping out at me. Are you replacing anything? What does your actual SQL look like?

 

[BigRed1212]

That is very very close.

Except that it picks up everything.

Looking at those who applied to place a, I should see 2 apps and qryPlaces shows that.

In the dataset in the original post, Bill did not apply to a, so I should not b or c as places where people who applied to a also applied and I think it is showing those.

I added "and ft.applied <> 0" to qryOtherPlaces and I think that has done it.

Still checking.

 

[BigRed1212]

Are you replacing anything? What does your actual SQL look like?

Yes, I'm replacing everything. The actual data is real names, real social security numbers, and real institutions so this is a dummy data set.

 

[BigRed1212]

Okay. I think that is correct.

Your id may be lame, but your help was not. Here's a star.

THANK YOU.