Show present and missing records in subform - where not exists?

[gemoon]

I have a subform that displays species and count for a specific Depth. I am trying to get a subform to display all species with counts that are either present or missing. I want to do this so counts can be entered for each species when a new record is added for depth. It seems to me that this can be done using the "where not exists", but im not sure how to implement this within a subform.

the relavent subforms are sfmSpeciesCount which is a child of sfrmDepth.
the linkage in sfrmSpeciesCount is
link childepthID
link master:[sfrmDepth].Form![DepthID]

the relavent relationships are
1:many tblSpecies:tblCount (SpeciesID is linked field)
1:many tblDepth:tblCount (DepthID is linked field)


sfrmSpeciesCount is based on the following query:

SELECT tblCount.DepthID, tblSpecies.Species, tblCount.Count, *
FROM tblSpecies INNER JOIN tblCount ON tblSpecies.SpeciesID = tblCount.SpeciesID
WHERE NOT EXISTS
(SELECT * FROM tblDepth
WHERE DepthID = tblCount.DepthID AND tblSpecies.SpeciesID=tblCount.SpeciesID);

 

[TheAceMan1]

How are ya gemoon . . . . .

[blue]I have a subform that displays species and [purple]count for a specific Depth[/purple].[/blue]

What does [purple]count for a specific Depth[/purple] mean?

[blue]I am trying to get a subform to display all species with counts that are either [purple]present or missing[/purple].[/blue]

I'm confused here with [purple]present or missing[/purple]. As I see it a species has a count or it doesn't. Normally, [blue]showing all records[/blue] will show both. I can't see you having a count in between [blue]has count[/blue] & [blue]doesn't[/blue]. Be more specific about this!

See Ya! . . . . . .

 

[gemoon]

TheAceMan1,
Thanks for the reply,

What I should have said was to display all species from tblSpecies with either the present count number or an empty field awaiting a count number to be entered.

For example:
There are 100 species in tblSpecies. And in tblCount for depth 10cm there are count values for 80 species. The subform should display the 80 species with their associated counts and the 20 missing species with empty count fields so the counts can be entered.

Does this make better sense?

Thanks for the help,
Ed.

 

[TheAceMan1]

How are ya gemoon . . . . .

Not sure if this can be done. May take me more time than I think. For the quickest answer, make a post in the [blue]Microsoft: Access Queries and JET SQL[/blue] forum. The guru's for this hang out there.

I'll work on it and post back there. Chances are someone will beat me too it . . . . .

See Ya! . . . . . .