computer geek help

[tanaise]

I'm writing a report for my sub tracker, which is an access database. I need a report which lists stories which are currently available to send, i.e., not at any market. Each submission gets a separate line in a table. It links to two other tables--a stories table and a market table. So a sub will have a line that looks like this:

SubmissionID StoryID MarketID S_When R_When extra
43 17 19 1/27/03 5/1/03 sale!

Where storyID 17 is "Wounds" and MarketID 19 is "Polyphony"

What I need the report to do is to look at all lines of the sub table and if there are any where R_when isnull, not report any records with that storyID. I've almost got it, I think. At least, I'm pretty sure I've got all the language I need, I'm just not putting it in the right place.

It needs to say "iff (IsNull([submissions]![R_when]), don't use that story at all.)" I just can't figure out how to translate 'don't use that story at all' into SQL, and I don't know where to put this command. Do I need a separate query? I can make it exclude that record, no problem, but I can't make it exclude all records with that storyID. I can do queries in reports, but I've never done a separate query, so I'm not entirely sure how they work.

And I know there are sub trackers out there that would do all of this for me. I like doing it myself. It teaches me how to program in SQL, and it serves as a good break from writing--it's still creative, but it's a different part of my brain, so I end up jostling something loose, and it's all good.

Comments

[andyhat.livejournal.com]

I don't do Microsoft, but in standard SQL, you want:

select * from stories a where not exists
(select storyid from submissions b
where a.storyid = b.storyid
and b.r_when is null);