Any excel . . . er, Access . . . genuises online?

[Entity]

I've got a question...kinda hard to explain. Basically I've got this huge dataset with student information in -- names, ID numbers, course name, year taken, etc. I'm trying to isolate all the kids who took their first class in 2000 (the data goes back to 1988 and up to 2003) and pull the records from that. I think I can do this in access, but I'm wondering if there would be any way to do it in excel. Any ideas?

LastName FirstName ID CourseName Teacher CourseID Year CreditsEarned Term

That's what the column setup looks like.

Ok, I've imported it into Access now. Ideas?

Rob

 

[KEV1N]

Custom Filter!

 

[Entity]

Originally posted by: KEV1N
Custom Filter!

Yeah, I've guessed that much, just not having much luck with it. It isn't even my job to be doing it, I'm just helping a friend out.

It's a fun puzzle for me at the least. I'm bored and watching Futurama.

Rob

 

[DannyBoy]

umm, I would recommend access for this.

You can export the data from excel to access.

 

[DannyBoy]

Originally posted by: Entity

Originally posted by: KEV1N
Custom Filter!

Yeah, I've guessed that much, just not having much luck with it. It isn't even my job to be doing it, I'm just helping a friend out.

It's a fun puzzle for me at the least. I'm bored and watching Futurama.

Rob

How can you be bored watching futurama :Q :Q

 

[Cfour]

Yea, seems like a good job for Access.

 

[InverseOfNeo]

Access

 

[Entity]

Hmm....ok, imported into Access now. Trying to figure out the steps I'd go through to do this; I can narrow it easily to people who have taken classes in 2000, but not people whose first class was in 2000. Any ideas?

Rob

 

[amdskip]

I'd personally just leave it in excel and do the custom filter. I don't know how to do it in access, sorry.

 

[Entity]

How would you do it in excel? Seems a bit complex. -- I can find records of everyone who took classes in 2000, but not their first class in 2000.

Rob

 

[Entity]

Grrr...this is frustrating.

 

[calpha]

Simplest way to do it in access is with a sub-query, written not using the query design wizard, but from the sql designer, writing the sql by hand

Select * from <table name> where Year = 2000 and id not in (select id from <table name> where year < 2000).

You'll have to use brackets around the column names and table name
select * from [mytable] where [mytable].[year] = '2000' and [mytable].[id] not in (select [mytable].[id] from [mytable] where [mytable].[year] = '2000')
if you've used dates as your datatype for year the 2000 will be enclosed by # signs, and should be a between #1/1/2000# and #12/31/2000#
If you've used text as your datatype for the year, and if it's still MM/DD/yyyy you'll use ' instead of #s.

Alternatively, if each student has the same id for every year they took a class, you don't need a subquery. You can use the having clause.

Select student_id from <table name> group by student_id having min(year) = '2000'

 

[Entity]

Didn't even think about the min clause. Thanks.

Rob

 

[T2T III]

Originally posted by: Entity
How would you do it in excel? Seems a bit complex. -- I can find records of everyone who took classes in 2000, but not their first class in 2000.

Rob

You could select the column sort order. First, sort on the column to sort the years. Then, perform a 2nd level sort to group those who took their first class in that year. This is using Excel.

Granted, you are getting several different pieces of advice - but, there are literally 10 different ways to do this - hence, the different advice.

 

[Entity]

Originally posted by: Tiles2Tech

Originally posted by: Entity
How would you do it in excel? Seems a bit complex. -- I can find records of everyone who took classes in 2000, but not their first class in 2000.

Rob

You could select the column sort order. First, sort on the column to sort the years. Then, perform a 2nd level sort to group those who took their first class in that year. This is using Excel.

Granted, you are getting several different pieces of advice - but, there are literally 10 different ways to do this - hence, the different advice.

Thanks. The cheif problem I was/am having is that I don't know Excel very well, and this was the format my friend was given the file in. I'm going to sleep now and will attack it again in the morning. It seems like I'm getting either too many or not enough results, depending on how I narrow down the data.

I figured out what I think should be the logical heuristic for it, I just don't know how to apply it:

1. Select students who took classes in 2000.
2. Subtract students who took classes before 2000 from that dataset.
3. Extract the full records of the remaining students.

That should be it, right? That way I'm only getting the results (data) from students who took classes in 2000, but not before.



Sleepytime. I've got to get up in 5 hours and this isn't even my problem.

Rob

 

[Entity]

Just tried this, but seem to be getting too many results (is it returning a cartesian product?):

SELECT *
FROM AllMath
WHERE (((AllMath.Year)=2000)) AND (((AllMath.ID) NOT IN (SELECT ID FROM AllMath WHERE (YEAR<2000))));

Rob

 

[Entity]

Holy nested queries batman. I finally got it.

SELECT *
FROM AllMath
WHERE (((AllMath.ID) IN (
SELECT DISTINCT ID
FROM AllMath
WHERE (((AllMath.Year)=2000) AND ((AllMath.ID NOT IN (SELECT ID FROM AllMath WHERE ((AllMath.Year)<2000))))))));

Thanks for all the help, guys.

Rob