Assistance required, search records

[David Eagar]

Scenario: A group of 6 (but can grow) animal species with records giving results of sightings.

Requirement: Search records and find where any 1, 2, 3, 4, 5 or 6 of these species where found.

I have a solution - a whole stack of individual queries which are combined to give the report. This is extremely cumbersome & high maintenance if new species are added to the group

Is there a VBA technique to obtain these results more dynamically and simply?

Thanks in advance

ps I'm a novice in VBA and rapidly learning the difference between dipping one's toes & being way over one's head is very narrow

 

[wiklendt]

you could make just ONE query to display ALL species and ALL their sightings, then make a report based off of that query. in the report, GROUP by species and sort by location of sighting. you can even sum the total number of sightings (if you have a number value) or you can count up locations also.

alternatively, you could create a corsstab query, which is similar to pivottables in excel... that is, you can make the query display the species down one column, then as separate columns have locations and the data in between will be number of sightings. this can be tricky to set up manually if you're not familiar with the way access uses the input you give it - so try using a query wizard to set this up in the first instance. also do searches in this forum for crosstab queries, as the wizard can be notoriously un-intuitive.

there's a start for you. let us know if you need more assistance with this.

 

[David Eagar]

Thanks for the tips, but maybe I'm not clear enough in my explanation.

What I'm after is eg any 2 of the species are found

Just using standard queries, I always end up with way too much data, or way too many queries

 

[wiklendt]

in that case. make one query with ALL the data. make one report to house all that data. then create a form that you can filter whatever you want from the available data.

FIRST OF ALL - i think your table structure COULD possibly be non-normalised, from your statement about being forced to make a new report each new species that is added - you shouldn't need to do this with a proper setup. you haven't mentioned what tables you have and how they relate to each other (a pic would help too). i think this might need to be addressed in the first instance.

however, on the off-chance that your tables are fine and all we're missing is a filtration system, here's one of mine that i'm still working on:

so, basically, you have queries not selecting various things, but one query, then you filter it. i do this all frequently.

i am working on a database for a particular department at work. i will post it here to show you what i mean. it's not finished yet, so not all the buttons and checkboxes work.

first off, choose any user to login, it doesn't matter - it won't ask for a password and it's not for security, it's for auditing. if it makes you feel any better, i made those names up - they're not real people (as i'm sure you'll realise as soon as you reach the login screen).

on the next form after you login, click on the first button "1. Queue Orders". have a quick look at this form - press the first drop down - you will see a list of ALL the items available for ordering. don't select any, you don't need to (and if you do, just don't select an item without a price - i hvaen't fixed that yet - it errors out).

however, when you click on "Print Queued Items", it shows you two listboxes - one of these is 'suppliers' - now, the database has about 40-odd(?) suppliers in it BUT this listbox is only displaying the 5(?) or so that have been associated with the queued items.

even more cool than that - if you select just ONE of those in the list, you can filter the report to display JUST THAT ONE (or a selection - the list allows you to select many of them!) in the report (which, when run on its own will print ALL the queued items.

this is quite a complex example of what i mean for you, but it just shows you what's possible. there are much simpler ways to get just one or two things, and that's handled quite easily in the DoCmd.OpenReport comand, and is usually only one line, but of course, can be limited.

let me know what solution you think might suit you best, and we'll go through it step by step.

if you'll like me to look over your table structure, zip up YOUR database and post it here. we'll get it sorted!

 

[David Eagar]

Thanks for this, I will investigate. Database attached - To explain, all info required is in this one table, and objective is:
For AnimalGroup=1 on any given SightDate, were any 2 (or 3 or 4 etc) Species (so could be 1 and 2, or 1 and 3 or 4 and 5 etc) Sighted>1 (1 = 0 sighted) for a given lake (sl)

 

[wiklendt]

this ought to get you started. open the form "frmSearch"

is this what you were thinking? at least, as a start?

 

[David Eagar]

Thanks for that. In a moment of 'inspired genius' I was able to meet the requirements and got down to 4 nested queries (down from about 10!) using a combination of count and a few search options

 

[wiklendt]

Thanks for that. In a moment of 'inspired genius' I was able to meet the requirements and got down to 4 nested queries (down from about 10!) using a combination of count and a few search options

have you had a look at the version of your database that i have posted back for you - it uses just one "embedded" query (so you won't actually see ANY in the object pane on the left, but if you really want it as a stand-alone query you can "save as" from query design view) and no code at all.

if you actually want counts of the sightings (which you didn't say you wanted before), then that can be easily added.

edit: sorry, in my eagerness i forgot to realise that the combo boxes in my example are also based on embedded queries - as such, these combos can be 'cascaded' (i did not have time to do this to the sample database for you, but it's easily done) that is to say, when you select an animal group in the first combo, it would filter out any dates that do not have that animal group in it and then the date combo filters the lake combo in the same manner - the upshot of which is that you don't have unnecessary 'zero' hits in any of the combinations you choose.

edit2: for example, the non-cascading combos yeild no results in the subform for many combinations. but if you know that the following combination yeilds results, you can see them in the subform:

animalgroup: 1
sighteddate: 20/04/2009
lake: 136

with a cascade, you wouldn't have the frustration of not getting any hits in the query.