Query ignoring 'zero' catch sets

[CraigDolphin]

I have a db with two tables linked by a Set_ID field. One table characterizes set information (date, time, location etc) and the other table has records for groups of fish caught in the set. The fish_table has (amongt others) a field for species (text), clip-status (yes/no), coded wire tag status (yes/no), and 'count' (number). The count field is necessary to allow input of groups of fish en-masse, or individually, depending on the amount of accessory information obtained.

Obviously, some sets catch no fish and so no record is entered into the fish table for those sets.

When I design a query, I want to produce a table that sums up the count field for each set, and produces subtotals for each species (and for the 4 variations of the clip/tag status fields).

My efforts so far are only partially succesful in that I can produce the correct subtotals, but only for sets where something was caught. Sets with no corresponding fish_table record are ignored instead of treated as zeroes.

Is it necessary to manually enter a 'zero' count for each species of interest for each set that we do? (Massively time consuming) Or is there some other way to query the db that forces the query to equate no fish record with a zero value?

Any thoughts? I've searched the forum, and googled, but haven't come across anything I could recognise as analogous to my dilema.

 

[Big2]

Write a simple IIF statement on the criteria for number of fish caught

=IIF(IsNull(fieldfishcaught), 0)

i think you can just enter a true value, but just search for IIF statements in the help. But what you are looking for is if that field IsNull, which means if there is no value in the field then put a 0 in the field else let it be the total amount of fish caught.

Another way of doing this is when the number of fish is input, if no fish have been caught then simple search for null value and put 0 in its place, or set the default value of the field to 0.

 

[CraigDolphin]

Thanks for the tip. I will do some reading on IIF statements.

Just to clarify, no record is entered into the Fish_Table if nothing is caught in a set, so setting a default value for the count field would not solve the problem unless I decided to manually enter a record with count=0 for every species not caught in each set. Would prefer to avoid this since there are literally thousands of species that I don't catch in each set

Still, I may have to manually enter a record for a few species of interest, even if they're not caught, each time I enter a new set if I can't figure this out. Seems like there should be a more elegant solution somehow. Perhaps the IIF method research will light my way.

Thanks again.

 

[pbaldy]

If you have a table that has the various species listed, joining against that table should produce a record for every species. A sample db might help people here.

 

[CraigDolphin]

Hi Paul,

I'll try to get a copy of the db posted once I figure out how to get it zipped small enough to post. It's about 4.2 Mb uncompressed right now. I would simply install a file zip program but our IT department has our computers so hamstrung with a combination of user permissions (everything denied) and citrix that I'm lucky to even have Access locally!

Thanks
Craig

 

[CraigDolphin]

Hi again,

okay...asked around and found someone who has the zip utility. Have attached the db. The query I'm working on is called Query 1. I have it set to show results from the Lampera net work we did in 2005 for the species 'Chinook'. There are some tables and forms that are 'hidden' so you can look at them if you like.

My main data input/browsing form is set to open automatically when opening the db. This is my first db.

I'm currently looking into the IIF/NoData function but am unsure how/where to apply it to the query.

I appreciate any help offered as the whole DB is fairly useless without the ability to report zero counts. Hopefully the data structure is not inherently screwed up!

Thanks.
Craig

 

[CraigDolphin]

Thanks for the help. I have resolved my problem by using an outside left-join for my query.

Still, it was well worth learning about iif statements and am utilizing them elsewhere in my db! Thanks again.
Craig

 

[pbaldy]

Sorry Craig, I got sidetracked by a work project. Glad you sorted it out.

 

[ray147]

Craig,

I have sort of the same problem in my DB...can you clarify how you did your outside left join in your case?

Thanks