Query help (using access but can do SQL)

I have a request from a user for a dataset and I am not sure how to get that information. She has asked for "All students with SUB, UNSUB, PLUS, only (no other sources of aid)"

So it is easy enough to get all students with those three items (Like "SUB" or Like "UNSUB" or Like "PLUS") but how can I craft a query that brings in students who only have one of those three? There are dozens of other values in that field. A student can have SUB and one of the other ones (for example).

I can imagine making two queries one with students who got one of those three, and one where I say "Not Like" so I get all the students who got something else. Then I could make a table of everyone from table A who does not appear in table B. Would that work?

Does that even make sense?

Any better ideas?

It's been a long time since I used access but

Select DISTINCT(studentId) from TABLENAME where fieldNameAid in ('SUB', 'UNSUB', 'PLUS');

should get you close

Distinct will only return the value once.

Tried that and got the same number of results as the original query.

My thinking -

Student A *is* only in there once with 'SUB' however they might be in there with 'PELL' for example. So the query by already saying WHERE field is 'SUB' already only bring in each kid one time - for sub, but it doesn't tell me that they are not also in there again as 'PELL' since that wouldn't have been returned by my query anyway.

Does that make sense?

BTW thanks. I have no one here in the office who knows more than me so I have no one to bounce ideas off of.

Ah sorry. I missed that they must NOT have any others. I'll think on it in a bit but in the meantime you might cross post it here:
http://www.gamerswithjobs.com/node/5...

That's a pretty active thread with a lot of eyes on it for programming.

I imagine there's a simpler way, but I bet you could filter using a subquery where the count(aid) for the student equals 1.

Rahmen wrote:

select DISTINCT(studentId) from TABLENAME where fieldNameAid in ('SUB', 'UNSUB', 'PLUS');

Assuming Oracle SQL syntax:

select studentId from TABLENAME where fieldNameAid in ('SUB', 'UNSUB', 'PLUS') group by studentId having count(*) = 1;

Oh, ack, that's a freeform text field? That's one of the classic database design flaws, putting more than one piece of information in a field. This was a horrible design blunder, and it's going to cost you.

Worse, they chose their freeform values poorly. You've got a serious logic problem, because you're searching a field for a text value, and the text values are not mutually exclusive. SUB is a substring of UNSUB, so you can't pull all your students out that have UNSUB, and then exclude the ones with SUB, because you will end up with zero records. You're kinda hosed, and it's going to take repair work to get what you need.

What you have there is a many-to-many relationship. That's where two types of entities can be related to one another, but not uniquely. Any student can have zero or more types of aid, and any type of aid can have zero or more students. That's the hardest kind of relationship to model, and it's where amateurs most often get it wrong in database design. Doing what they did is the most common error: putting a freeform text field in. This is a really bad idea. You're discovering one of the reasons: one of the other ones is because of typos. Are there students in the database that have "UNSB", and you don't know it? Do you have any, heh, SOB stories in those fields? With a freeform text field like that, you can end up with bad data in all kinds of fascinating ways. If someone is inconsistent about spacing or commas or whatever you used as a field separator, your queries can subtly break, and you may have no way to know.

So, how to do it properly? There should be a table of all the possible aid types, mapped to an ID value. SUB, for instance, might be ID 1. UNSUB could be ID 2, and PLUS could be ID 3. (The actual IDs don't matter, they just need to be unique.) And then you need a joining table, reflecting the many-to-many relationship, probably with just two columns: student ID, and Aid ID. If a student has multiple forms of aid, they will have several lines in that table, one for each kind of aid they have. If your student IDs start with 1000, your table might look like this:

1000 1
1000 2
1001 1
1002 2
1003 1
1004 3
1004 2

and so on. You query on the join of student plus aid, and you get back multiple rows if they have more than one kind.

So, basically, the database really needs to be fixed. If you absolutely cannot do this, then you MIGHT be able to construct your queries by using whatever spacing character they used as a search term. That is, if the field is formatted "BLAH, SUB, UNSUB, PLUS", then you could try to search for the leading space before SUB. But any records where that field STARTS with SUB won't be excluded properly.

Another approach would be to do a search-and-replace on UNSUB -- change it to something that doesn't have any other possible substring in it. And then you could successfully write a query, one that looks for $NEWVALUE but excludes SUB and PLUS. But this is a bandaid over a gaping wound.

A many-to-many join table is the right way to do it. If you actually want consistent answers out of your system, without lurching from crisis to crisis while the database quality steadily degrades, then it's going to need to be fixed. And there will probably be more tables that need fixing, because amateurs, they love them some freeform text fields.

Thank you all for your help.

I will keep playing with it for now I have a pretty simple way. I just join on the ID then bring over SUB from table one, then Anything from table 2 where the grant field is NULL. This gives me the the ID that only got a SUB grant (otherwise the grant field in table 2 wouldn't be empty)

At least that seems to work.

I will keep work on some of the other suggestions people gave so I can get better at manipulating data though!

This may be oversimplifying - not sure what data is in the field that you're querying on, but if you are looking for isolated values in that field why not use = instead of like? Exact match vs partial match...

SELECT * FROM TABLE WHERE FIELD = 'SUB' OR FIELD = 'UNSUB' OR FIELD = 'PLUS'

Of course this won't work if there is other unrelated data in the field also...

Thanks. More to play with.

merphle wrote:
Rahmen wrote:

select DISTINCT(studentId) from TABLENAME where fieldNameAid in ('SUB', 'UNSUB', 'PLUS');

Assuming Oracle SQL syntax:

select studentId from TABLENAME where fieldNameAid in ('SUB', 'UNSUB', 'PLUS') group by studentId having count(*) = 1;

This should work, except you really need to have "count(*) >= 1" since there might be multiple records per student (if I'm understanding the data correctly).

Farley3k's request was to show all records in the table for which a given studentId appeared in exactly 1 row, for the given fieldNameAids... so, I think count(*) = 1 would be most appropriate.

merphle wrote:

Farley3k's request was to show all records in the table for which a given studentId appeared in exactly 1 row, for the given fieldNameAids... so, I think count(*) = 1 would be most appropriate. :)

Ah, missed the part where he wanted students with only one of the three. My bad.