preparing to do something difficult

[AccessIdiot]

Hello all,

A while ago I got a lot of help on building a bunch of forms tied to a somewhat complicated db. Now I'm back with a search form question.

The guys that I built the db for - they asked me to create a bunch of tables based on questions they would ask of the db. This required me to create a bunch of queries - none too difficult but they accessed a number of tables. I also built some crosstab queries based on the results of other queries.

Now they would like me to build them some kind of search form so that they can ask their questions of the data without having to go through me. So I need to build some kind of easy-to-use search form (I'm assuming utilizing a bunch of listboxes) that will spit out some tables that can be exported to excel (because that's what they are familiar with using for formatting for reports, etc).

So my question comes to this: given a limited knowledge of Access (I cut my teeth on this project) is something like this doable? I know it's doable for an expert, I'm asking if its doable for a beginner to intermediate who can understand code and alter given code she finds on the internet, but isn't a programmer from scratch.

Examples of tables: general trawl data (date, trawl number, staff, location)
Catch data (trawl number, specimen id, number caught, disposition at time of capture, disposition at time of release)
Specimen data (specimen id, specimen name, scientific name, genus)

Examples of questions:
Number of specimens ordered by number caught and name grouped by location
Number of specimens ordered by number caught and name grouped by date by location
Number of specimens ordered by number caught and name grouped by date by location and release dispostion

Thanks for any advice (including "give it up now sister"), insight, and/or links to examples!!

[ADezii]

Hello all,

A while ago I got a lot of help on building a bunch of forms tied to a somewhat complicated db. Now I'm back with a search form question.

The guys that I built the db for - they asked me to create a bunch of tables based on questions they would ask of the db. This required me to create a bunch of queries - none too difficult but they accessed a number of tables. I also built some crosstab queries based on the results of other queries.

Now they would like me to build them some kind of search form so that they can ask their questions of the data without having to go through me. So I need to build some kind of easy-to-use search form (I'm assuming utilizing a bunch of listboxes) that will spit out some tables that can be exported to excel (because that's what they are familiar with using for formatting for reports, etc).

So my question comes to this: given a limited knowledge of Access (I cut my teeth on this project) is something like this doable? I know it's doable for an expert, I'm asking if its doable for a beginner to intermediate who can understand code and alter given code she finds on the internet, but isn't a programmer from scratch.

Examples of tables: general trawl data (date, trawl number, staff, location)
Catch data (trawl number, specimen id, number caught, disposition at time of capture, disposition at time of release)
Specimen data (specimen id, specimen name, scientific name, genus)

Examples of questions:
Number of specimens ordered by number caught and name grouped by location
Number of specimens ordered by number caught and name grouped by date by location
Number of specimens ordered by number caught and name grouped by date by location and release dispostion

Thanks for any advice (including "give it up now sister"), insight, and/or links to examples!!

What you are describing could become very complex in that you would have to create some kind of Criteria Form then dynamically build a SQL String involving multiple Tables and Joins, Sort Orders, Grouping Levels, etc. reflecting those options made. The easy way out would be to have 'them' submit a list of proposed questions, enter then into a Table, create a Combo Box listing these questions, then create Stored Queries reflecting each question. Depending on the selection, the appropriate Query would be executed, Exported to Excel, etc. It would also be easy to Add/Remove questions and their associated Queries. The alternative, I'm afraid, could be very complex. Wait and see what some of the other Experts/Moderators have to say on the Subject before you make a decision.

[FishVal]

Wow.

Looks like you are charged to design super-userfriendly query builder feature.
Would it be easier to train that guys to use Access query builder than to code logic translating questions in English to answers in SQL?

Regards,
Fish

[NeoPa]

Hi M :)

Those specimens still active up there on the North-West coast?

What you're considering is like a query design engine. It's possible, but a little strong for someone at your level.
Don't get me wrong, as I racall you were certainly not stupid, but not vastly experienced on the db side either.
As I see it, a task like this would rely very heavily on your ability to understand and code (very) logically. Many otherwise good programmers can't do engines simply because the logic requirement is too much.
Think of the requirement to build SQL strings in VBA which are then run through the SQL interpreter. Many struggle with understanding which parts need to be processed before adding to the string and which are added to the string for later processing by the interpreter. Designing an engine involves similar (but obviously more complex still) issues.

[AccessIdiot]

Thanks to all who replied. :-)

It is as I guessed - probably a bit over my head at this stage. I like the idea of offering some canned queries in a list. This project is something like a 10 year deal where they go out and do the same stuff for a few months every year. So I have 2006 data, they are now working on 2007 data, etc. I know that some of the questions will be the same every year, so the canned queries is great, but I know that based on the answers they will want some custom queries as well.

Maybe a training is in order, though they are biologists and I don't think have the time or desire to learn Access. Maybe I just go for job security and convince them that they need to go through me to get the data they want. :-)

Again, thanks for your honest answers and creative solutions!

Cheers,
Melissa

[AccessIdiot]

The easy way out would be to have 'them' submit a list of proposed questions, enter then into a Table, create a Combo Box listing these questions, then create Stored Queries reflecting each question. Depending on the selection, the appropriate Query would be executed, Exported to Excel, etc.

Okay so I've talked to some folks and we've decided that this is, at the least, a good place to start (we've also proposed getting me some advanced Access training - yay!).

QUESTION 1: I imagine its as simple as creating a table with an autonumber pk and a memo field that holds the questions yes? Then I make a form with a combo box that holds the questions and each item in the combo box is linked to a query I have already built?

QUESTION 2: Okay so if the above is correct here's another question - would it be possible/not too hard to have checkboxes or dynamic listboxes for what fields to show in the query?

That is, say they want to know number and type of specimens by locations. I have a query for that but they want to see some attributes from the table like, which ones are fish, which ones are native species etc. Would it be possible to generate that on the fly? Or is that the same type of very advanced stuff that I was alluding to in my original question?

Thanks for any help!

[AccessIdiot]

Okay, thinking more about this. If in my canned query I have a "select all" and it spits the result out into a results page. Could that results page then have controls on it that would allow the user to show/hide certain fields before pressing a button that spits the table out to Excel?

That's not too hard right?

Thanks again all. :-D

[NeoPa]

You could certainly have a form with CheckBoxes that determine which controls are visible and which hidden. These same CeckBoxes could be used in the Export coding to determine which fields to include in the Export.
This is getting a little "Engine"y, as the SQL string would need to be updated on the fly and inserted into a temp or template QueryDef, but quite do-able.

[ADezii]

Okay so I've talked to some folks and we've decided that this is, at the least, a good place to start (we've also proposed getting me some advanced Access training - yay!).

QUESTION 1: I imagine its as simple as creating a table with an autonumber pk and a memo field that holds the questions yes? Then I make a form with a combo box that holds the questions and each item in the combo box is linked to a query I have already built?

QUESTION 2: Okay so if the above is correct here's another question - would it be possible/not too hard to have checkboxes or dynamic listboxes for what fields to show in the query?

That is, say they want to know number and type of specimens by locations. I have a query for that but they want to see some attributes from the table like, which ones are fish, which ones are native species etc. Would it be possible to generate that on the fly? Or is that the same type of very advanced stuff that I was alluding to in my original question?

Thanks for any help!

QUESTION 1: I imagine its as simple as creating a table with an autonumber pk and a memo field that holds the questions yes? Then I make a form with a combo box that holds the questions and each item in the combo box is linked to a query I have already built?

Correctamundo!

QUESTION 2: Okay so if the above is correct here's another question - would it be possible/not too hard to have checkboxes or dynamic listboxes for what fields to show in the query?

Definately possible with a fair degree of difficulty.

That is, say they want to know number and type of specimens by locations. I have a query for that but they want to see some attributes from the table like, which ones are fish, which ones are native species etc. Would it be possible to generate that on the fly?

It all can be generated on the fly, but you are getting so ever closer to your original request.

[AccessIdiot]

It all can be generated on the fly, but you are getting so ever closer to your original request.

Yeah that's what I thought. :-\

Okay, baby steps then. :-)

I feel really stupid for asking this but I think I just need a refresher - how do I attach the queries I've already built to the items in my combo box? Do I use the onchange event to fire off the sql code depending on the query id?

//pseudocode: if queryid = 1 run query1; if queryid = 2 run query 2; etc
 

I'm sure there must be a better way to assign the query to the value?

thanks!

[NeoPa]

You store the name of the query in a field in your table (QueryName).
When your record is selected on the form, you execute code that calls :

DoCmd.OpenQuery(Me.QueryName, ...)

to see the results or :

DoCmd.TransferSpreadsheet(Me.QueryName, ...)

to export the results to an Excel spreadsheet.

[AccessIdiot]

You store the name of the query in a field in your table (QueryName).
When your record is selected on the form, you execute code that calls :

DoCmd.OpenQuery(Me.QueryName, ...)

to see the results or :

DoCmd.TransferSpreadsheet(Me.QueryName, ...)

to export the results to an Excel spreadsheet.

Thanks NeoPa, but I'm afraid I'm totally lost. I added a text field in the table that stores the name of the query for each question asked. But you know how you get auto hints when you start typing code in the VB builder? The name of my field didn't show up and when I type it in manually and run it I get an error that it isn't a property.

So I'm stuck. :-(

[NeoPa]

If the field is called [QueryName] then you could have a ComboBox called [cboQueryName] which lists the queries from the [QueryName] field. [cboQueryName] would be what you need to execute.

DoCmd.TransferSpreadsheet(Me.cboQueryName, ...)

Does that make more sense?

[AccessIdiot]

It totally does and I'm not sure why I'm having so much trouble. When I create the combo box it wants to show each row in a new record, instead of having all the values available on one drop down.

And when I try the code on an After Update event it tells me there is no method named Query_Name.

[NeoPa]

It totally does and I'm not sure why I'm having so much trouble. When I create the combo box it wants to show each row in a new record, instead of having all the values available on one drop down.

I'm not really sure what you're trying to describe here I'm afraid.

And when I try the code on an After Update event it tells me there is no method named Query_Name.

I'm not sure where Query_Name came in. I assume it's either the field or the control, but without knowing it's hard to guide you.
However, to get the code in the right place, simply :

1. Select the control in the form design.
2. Open up the Properties pane if not already open.
3. Go down to After Update and select [Event Procedure] (exactly that) from the dropdown there.
4. Click on the elipsis (...) button immediately to the right.

This should take you to the code window with the stub of a (correctly named) procedure to fill up. Copy and paste your existing code into here.