Query using checkboxes as parameter values?

M

mightymaggie

Is there any possible way to design a parameter query using checkboxes?
In a table containing, for example, Publication, Article, and then a
checkbox for each Issue Month, I can pick only the records where the
January checkbox = -1?

I would like my users to enter two values: "Which Publication" and
"Which Month". When they type in "January", the query would know to
return only records where the checkbox = -1.

I know that many checkboxes are lame, but I'm grasping at straws...

Thanks
Maggie
 
J

John Vinson

Is there any possible way to design a parameter query using checkboxes?
In a table containing, for example, Publication, Article, and then a
checkbox for each Issue Month, I can pick only the records where the
January checkbox = -1?
Click to expand...

This is a BADLY NON-NORMALIZED table structure. Storing data in
fieldnames is simply *wrong*. In this case it's doubly wrong: what
will you do with your January (2005) data in January 2006?

If you have a one (publication) to many (issues) relationship, you
need three tables:

Publications
PublicationID
PublicationName
<etc>

Issues
PublicationID > link to Publications
IssueDate > joint two-field primary key
<information about this issue>

Articles
PublicationID > link to Publications and/or Issues
PublicationDate > link to Issues
ArticleTitle

This would let you search easily for a specific date, a specific
month, a range of months, or whatever you like.
I would like my users to enter two values: "Which Publication" and
"Which Month". When they type in "January", the query would know to
return only records where the checkbox = -1.
Click to expand...

A criterion of TRUE or of -1 on the January field should do this just
fine. Isn't it??? If not, please post the SQL view of your query.
I know that many checkboxes are lame, but I'm grasping at straws...
Click to expand...

"When you find yourself in a deep hole, the first thing to do is quit
digging..."

Fix the table structure. It will be MUCH better in the long run.

John W. Vinson[MVP]
 
M

mightymaggie

Thanks John.

I actually have the 3 tables like you recommended above. The design is
great, but the form views are not ideal for my users. They want to see
all the months an ad runs at once- without having to scroll through a
datasheet or click through multiple subforms. This is why they were
using checkboxes. Makes entering the data easier for them too, but
nightmarish for me (as you so aptly described!) Saving the data by year
isn't terribly relevant for us (another field helps with that) and
we're a small operation, so I thought I would find out if checkboxes
would work. And when they type in "January" for the parameter, I would
want to see all the records where there is a -1 in the January column,
even though there will also be -1s in the Feb and March etc. columns. I
don't know how to do that without making each month checkbox a
parameter.

Anyway, I think I'm stuck telling them they have to deal without their
checkboxes. Thanks for your input.
 
J

John Vinson

Thanks John.

I actually have the 3 tables like you recommended above. The design is
great, but the form views are not ideal for my users. They want to see
all the months an ad runs at once- without having to scroll through a
datasheet or click through multiple subforms. This is why they were
using checkboxes. Makes entering the data easier for them too, but
nightmarish for me (as you so aptly described!) Saving the data by year
isn't terribly relevant for us (another field helps with that) and
we're a small operation, so I thought I would find out if checkboxes
would work. And when they type in "January" for the parameter, I would
want to see all the records where there is a -1 in the January column,
even though there will also be -1s in the Feb and March etc. columns. I
don't know how to do that without making each month checkbox a
parameter.

Anyway, I think I'm stuck telling them they have to deal without their
checkboxes. Thanks for your input.
Click to expand...

I'm confused.

You say "I have the 3 table like you recommended" and you also say
"the January column..." etc.

These seem contradictory.

What is the structure of your tables? Fieldnames, nature of the data
in those fields? What is the SQL of your query?

It IS possible to use unbound checkboxes on an unbound form to search
a properly normalized table... but I can't tell you how to do so right
now, because *I don't understand your data structure*. I can't see it;
you can. I'll be glad to get you a user-friendly interface to a
computer-friendly data structure - but I'll need a bit more
information to do so!

John W. Vinson[MVP]
 
M

mightymaggie

John-

I did have the 3 tables, but since I didn't know enough code to make my
forms do I what I wanted with the 3 tables, I was still trying to
figure out how I might do it the other way, hence the "January column"
remark. Anyway, I finally coded some unbound checkboxes to fill in the
right table and now it works fine- just a lot of code and a lot of
help.

thanks
 
Top