Selecting Criteria on a form to run a Query

[Gary W. Graley]

Hello folks, the database I have is in Access 2003 and what I am thinking
of is making a query form, with tick boxes that you can chose the criteria
you want to use when the query is run.

I started making up many queries for the various fields they/Engineering
will want to try and see. For example, the following items would be of interest
for them to query;

part number
machine name
project number
author
date
author and date range

as I have 4 tables of detail, for each type of drawing number, I would need
4 Query forms that would have tick marked selections for the query to run.

Is this something that is doable?

Limited...well lets say NO, VBA experience is what I have, so please keep that in mind on your replies.

Thanks in advance,
G2

ps figured this question was better placed here than in the Forms section, but
maybe it's more a Form type problem?

 

[pbaldy]

Let's start with the basics, which is do there have to be 4 tables. Do they contain the same fields and you just have a different table for each type, or do different types require different fields?

 

[Gary W. Graley]

Let's start with the basics, which is do there have to be 4 tables. Do they contain the same fields and you just have a different table for each type, or do different types require different fields?

Yep, good questions all, and they do need to be 4 different tables, as
they represent the 4 types of drawings, by paper size, old school,
A, B, C and D size drawings.

The database's primary use is to generate the next Drawing number to be used
as the Engineers used to use a book for each drawing size to draw out a new number.
BUT, now we have two locations of Engineers, several states apart and so the books
do not work for this any longer.
The database will reside on a server available to each office.

And the reason for 4 tables is I am using the DMax plus 1 to have it come up with
the next drawing number
plus they do not want to inflate one set of numbers, but let each 'type' of drawing
have it's own series of numbers, otherwise the number would grow too large too quickly.

Hope that spells out the situation, a simple database really, but cumbersome
to make all the queries for the various options they would like to be able to
view at any time.

Also, each of the Drawing tables are not required to be queried at the same time,
meaning that the Engineer would only be concerned with one table, so what ever I
end up making, I'll have 4 forms with query options on them to draw out the information.
So don't think of it as having to work with 4 tables at the same time.

Thanks,
G2

edited to say there are other tables within that are tied to each 'type'
Project's, Author's and Equipment tables, tied with an iD and the tables
have a field for a full description as well as a field for In Active.

 

[pbaldy]

And the reason for 4 tables is I am using the DMax plus 1 to have it come up with
the next drawing number
plus they do not want to inflate one set of numbers, but let each 'type' of drawing
have it's own series of numbers, otherwise the number would grow too large too quickly.

Those are easy issues to get around with one table. I would only have separate tables if the fields required for each type were significantly different.

That said, even with 4 tables, if the basic options were the same, I'd probably use one form. Are you saying they may choose different fields to view each time, or just the criteria they want to filter on may change?

 

[Gary W. Graley]

I'd love one table, but the reasoning is they want to keep the drawing numbering
'size' to each of the drawing types, not incrementing one number for all drawings.
basically replicating the individual books.

Plus you can have a 1234A as well as a 1234B...but that's enough on the reasoning
won't be changing that for them now.

As to your question, the fields will all be displayed, just the criteria would be different

Example, they may want to view just one particular Author, click and show me that
or they might want to search by the Machine name and view that.
I usually have it in the Like *&[enter blah blah blah]&* type so they don't have to
be exact to draw information out.

I would normally trudge through and create each of these scenarios and provide a
button to run that, but I was thinking there must be a Code way to do this a bit
slicker too, with option buttons maybe and the selection listed by each button.

Thanks,
G2

 

[pbaldy]

I was going to point you to a sample from another site, but it seems to be down right now. Here's the db itself. I wonder if something like this would work for you, with an additional combo to select which drawing type, which would change the table of the SQL. Basically you can input any of the criteria above, and the results would display below. I think the fields would have to be consistent (though you could code that into the SQL if they weren't).

 

[Gary W. Graley]

Thanks Paul, I think that will be the ticket, will digest that and
see if I can change the names to suit the code.

Much thanks again,
G2