Access SQL: tricky embedded subquery

[JeenLeen]

I'm writing a query to put together a test item database (like for standardized tests like SAT or ACT).

I have two tables. ItemStatus (shortened to Status in the below), which is the primary table being used for this query. I also have a table called ItemStatistics (shortened to Stats in the below), from which I want to draw two values whenever Status.UIN (Universal ID Number) = Stats.ItemID. (There should just be one row per item in Status. It is likely some UINs in Status do not have a corresponding ItemID in Stats. UIN is the primary key for Status. Stats has a automated ID number for its primary key.)

Also, Stats can have multiple rows with the same ItemID. I only want to draw the most recent values. For this, I can use the TestAdmin field, which contains SP09, SP10, SP11, SP12, or SP13 (Spring 2009 up to Spring 2013).

My difficulty is twofold:
1) whether it's better to use a subquery in the opening SELECT line OR to use do a JOIN between Status and Stats and/or a WHERE .... IN to specify the link. Currently, I think a subquery works better.
2) how to get the subquery to return the value for the current UIN I'm looking at.

My current code is below. The first bolded part (for the first value, ItemDiff) is what isn't working. The second bolded part (for the second value, RaschDiff) just has some filler right now since I reckon the code will be identical as that for ItemDiff, just for a different value.

I can run the code, but I get an error message of "At most one record can be return by this subquery."

Any ideas what would work?


SELECT Status.Subject, Status.Grade, Status.[Status Level], Status.[Primary Stnd], Status.UIN, Status.[Time Stamp] AS [Accurate as of],
(SELECT
Stats.ItemDiff
FROM ItemStatistics AS Stats
WHERE Stats.TestAdmin =
(SELECT First(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
GROUP BY Stats.TestAdmin
ORDER BY Stats.TestAdmin
)
) AS [ItemDiff],

(SELECT ????
FROM ItemStatistics AS Stats
) AS [RaschDiff]

FROM ItemStatus AS Status
WHERE (( Status.Subject=UCase([Enter your subject (ELA, MATH, SCI, SOC, or WRI): ]) ))
ORDER BY Status.Subject, Status.Grade, Status.[Status Level] DESC , Status.[Primary Stnd], Status.UIN;

[Razanir]

Can you post a UML diagram? Also, what exactly are you querying for?

[Manga Shoggoth]

It isn't very clear what you are trying to do, but the problem is that your subquery is returning more than one row. I suggest you try the following:

WHERE Stats.TestAdmin =
(SELECT max(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
)


You don't need the ORDER and GROUP clauses, and given the format of your TestAdmin field, MAX() should give you the most recent value.

[JeenLeen]

This is edited to have two three replies. This is the first reply.


Can you post a UML diagram? Also, what exactly are you querying for?

I'll try to get one together & post it. I don't currently have one or a place to upload images to post it by. Here's something close to it, I think:



ItemStatus ItemStatistics
Subject ID
Grade Subject
Status Level Grade
Primary Stnd TestAdmin
UIN************one-to-many**************************ItemID
Time Stamp ItemDiff
RaschDiff

Both tables have other fields as well, but they aren't needed here.
UIN and ItemID are the same thing, just different labels. UIN is Primary Key for ItemStatus. ID is Primary Key for ItemStatistics.

I want to make a report which has every UIN in ItemStatus as well as the most recent (as determined by TestAdmin) ItemDiff and RaschDiff from ItemStatistics. (The WHERE clause at the bottom of the query is to only do one subject area (math, English, etc.) at a time.)

If it helps to give the idea, I did find a work-around. It doesn't tell the user which item difficulty is the most recent, but it does give the max & min it has ever been.

SELECT Status.Subject, Status.Grade, Status.[Status Level], Status.[Primary Stnd], Status.UIN, Status.[Time Stamp] AS [Accurate as of], MIN(Stats.ItemDiff) AS , MAX(Stats.ItemDiff) AS [ItemDiff, High], MIN(Stats.RaschDiff) AS [RaschDiff, Low], Max(Stats.RaschDiff) AS [RaschDiff, High]
FROM ItemStatus AS Status LEFT JOIN ItemStatistics AS Stats ON Status.UIN = Stats.ItemID
WHERE (( Status.Subject=UCase([Enter your subject (ELA, MATH, SCI, SOC, or WRI): ]) ))
GROUP BY Status.Subject, Status.Grade, Status.[Status Level], Status.[Primary Stnd], Status.UIN, Status.[Time Stamp]
ORDER BY Status.Subject, Status.Grade, Status.[Status Level] DESC , Status.[Primary Stnd], Status.UIN;


Second

I think I figured it out. Thank you both of you for your time, and thank you Manga Shoggoth for your advice. It helped me figure out the below.
[I]I could use a hand making the code more efficient, though. It took a long time to run and Access went to 'Not Responding' for a while.

But here's what works (well, pretty sure works. Only did a spot check on ItemDiff to confirm it gave the most recent values.):

SELECT Status.Subject, Status.Grade, Status.[Status Level], Status.[Primary Stnd], Status.UIN, Status.[Time Stamp] AS [Accurate as of],
(SELECT
Stats.ItemDiff
FROM ItemStatistics AS Stats
WHERE Stats.ID =
(SELECT Stats.ID
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN AND Stats.TestAdmin =
(SELECT Max(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
)
)
)
AS ItemDiff,

(SELECT
Stats.RaschDiff
FROM ItemStatistics AS Stats
WHERE Stats.ID =
(SELECT Stats.ID
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN AND Stats.TestAdmin =
(SELECT Max(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
)
)
)
AS RaschDiff

FROM ItemStatus AS Status
WHERE (( Status.Subject=UCase([Enter your subject (ELA, MATH, SCI, SOC, or WRI): ]) ))
ORDER BY Status.Subject, Status.Grade, Status.[Status Level] DESC , Status.[Primary Stnd], Status.UIN;


Third, and final

Figured out that finding the Stats.ID was not needed. Final code, MUCH faster (and think it fixed a bug, too)

SELECT Status.Subject, Status.Grade, Status.[Status Level], Status.[Primary Stnd], Status.UIN, Status.[Time Stamp] AS [Accurate as of], (SELECT
Stats.ItemDiff
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN AND Stats.TestAdmin =
(SELECT Max(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
)
) AS ItemDiff, (SELECT
Stats.RaschDiff
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN AND Stats.TestAdmin =
(SELECT Max(Stats.TestAdmin)
FROM ItemStatistics AS Stats
WHERE Stats.ItemID = Status.UIN
)
) AS RaschDiff
FROM ItemStatus AS Status
WHERE (( Status.Subject=UCase([Enter your subject (ELA, MATH, SCI, SOC, or WRI): ]) ))
ORDER BY Status.Subject, Status.Grade, Status.[Status Level] DESC , Status.[Primary Stnd], Status.UIN;


Thanks again!

[Manga Shoggoth]

One more optimisation: You are doing the same join twice in the SELECT part of the clause, which isn't going to be brilliant (it constitutes two correlated subqueries). You may get a better response with something like:


SELECT Status.Subject,
Status.Grade,
Status.[Status Level],
Status.[Primary Stnd],
Status.UIN,
Status.[Time Stamp] AS [Accurate as of],
Stats.ItemDiff,
Stats.RaschDiff
FROM ItemStatus AS Status
LEFT OUTER JOIN ItemStatistics Stats on Stats.ItemID = Status.UIN
WHERE Status.Subject=UCase([Enter your subject (ELA, MATH, SCI, SOC, or WRI): ])
AND Stats.TestAdmin = (SELECT Max(Stats2.TestAdmin)
FROM ItemStatistics AS Stats2
WHERE Stats2.ItemID = Status.UIN)
ORDER BY
Status.Subject,
Status.Grade,
Status.[Status Level] DESC ,
Status.[Primary Stnd],
Status.UIN;

This assumes I have the join the right way and the rest of the logic works - it changes the plan to an outer join and one correlated subquery. Check and test if you want to use it...

EDIT: Also updated my answer to your earlier question...

[JeenLeen]

Manga Shoggoth, I'm not sure why, but that code isn't working. The 'final code' I put in my post above gave 1 result for each row in ItemStatus with the appropriate subject. (In this case, Math of 3657.)

When I ran the query you wrote, it gave me 1397 results.
After looking at it, the query you wrote leaves out any rows without a ItemDiff.

Would it make sense to believe that, while the JOIN includes all rows in Status but only those in Stats where UIN = ItemID, having Stats be referenced in the WHERE clause excludes any rows where there is a UIN but not a corresponding ItemID?

(On a side note, I did not know that you could use a subquery in WHERE which refers to the current field... I'm having trouble phrasing this well... but I didn't know you could have Stats.TestAdmin = (SELECT something based on what the current Status.UIN is). So, thank you!)

[Manga Shoggoth]

Quite possibly, I'm afraid - I'm not in a position to set up a testbed at home (no Access at home any more...), so unfortunately I can't test the things.

[JeenLeen]

Quite possibly, I'm afraid - I'm not in a position to set up a testbed at home (no Access at home any more...), so unfortunately I can't test the things.

I'm pretty sure that's why it was excluding the values. I think that would have fine if my data were neater (if there were an ItemStatistic ItemID for each ItemStatus UIN.)
The query runs pretty quickly as it is now, so even if it's not completely optimal, I think it works well enough for the size of this database.

Thanks again, especially for showing me that you can use SELECTs in the WHERE like that. And thanks to the others who replied as well; just bouncing ideas around helped me to figure it out.