multiple criteria

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hi,
i have a table called receipts in which i store the past
12 receipts a client has paid. i want a way to find any
receipt from a particular month (say for example, july
2004). i tried it using the following criteria in a query
in all 12 date fields:
Between #07/01/04# And #07/31/04#

Now of course, since i put that in all 12 date fields it
didnt work since someone doesnt have all 12 receipts for
july.

My question is How do i get the query to search in all of
the date boxes and pull any of the records with a july
date?

thanks,
Rob
 
Rob,
I'm pretty sure that someone will tell you that your
table isn't structured well.

To accomplish what you want, you want to stagger your
criteria like this:

column date1 date2 date3
criteria between...
between..
between..

This means date1 between or date2 between, etc.
What you entered was date1 between and date2 between, etc.

HTH
 
Sounds like you need to connect your WHERE clauses with OR instead of AND.

Without a table layout its difficult to know what you are talking about.
 
is there anyway to use an And/Or in SQL?

-----Original Message-----
Rob,
I'm pretty sure that someone will tell you that your
table isn't structured well.

To accomplish what you want, you want to stagger your
criteria like this:

column date1 date2 date3
criteria between...
between..
between..

This means date1 between or date2 between, etc.
What you entered was date1 between and date2 between, etc.

HTH

.
Click to expand...
 
Oh, sorry, didn't sound like you were using SQL. It would
be like:

WHERE (other criteria, if you have any) and
((Date1 Between #07/01/04# And #07/31/04#) or
(Date2 Between #07/01/04# And #07/31/04#) or...
(Date12 Between #07/01/04# And #07/31/04#));

HTH

Original Message-----
 
is there anyway to use an And/Or in SQL?
Click to expand...

Yes, of course.
column date1 date2 date3
criteria between...
between..
between..
Click to expand...
In this example the SQL would look like

WHERE ([Column] = criteria AND (Date1 BETWEEN [start] AND [end] OR
Date2 BETWEEN [start] AND [end] OR Date3 BETWEEN [start] AND [end])

The problems you are having are the main reason that YOU SHOULD NOT
STRUCTURE YOUR TABLE IN THIS WAY.

If you have a one to many relationship, don't create twelve fields in
one table - use *two tables* in a one to many relationship, with one
receipt in each *record* in the many side table. You'll have no end of
trouble using your current "spreadsheet" design (not least: what will
you do at the end of the year?)
 
John,
thanks for the response but be sure that i have multiple
tables already. i am doing a lawyer referral database
which is all done and working fine with the exception of
the receipts part which is what i asked for help on. i
thank you for your suggestion to use two tables, but i am
way past that. i am doing one simple table for receipts
for money earned. all i want to do is search the 12 date
fields for july dates so i can run monthly reports.

thank you,
rob
-----Original Message-----
is there anyway to use an And/Or in SQL?
Click to expand...

Yes, of course.
column date1 date2 date3
criteria between...
between..
between..
Click to expand...
In this example the SQL would look like

WHERE ([Column] = criteria AND (Date1 BETWEEN [start] AND [end] OR
Date2 BETWEEN [start] AND [end] OR Date3 BETWEEN [start] AND [end])

The problems you are having are the main reason that YOU SHOULD NOT
STRUCTURE YOUR TABLE IN THIS WAY.

If you have a one to many relationship, don't create twelve fields in
one table - use *two tables* in a one to many relationship, with one
receipt in each *record* in the many side table. You'll have no end of
trouble using your current "spreadsheet" design (not least: what will
you do at the end of the year?)



.
Click to expand...
 
John,
thanks for the response but be sure that i have multiple
tables already. i am doing a lawyer referral database
which is all done and working fine with the exception of
the receipts part which is what i asked for help on. i
thank you for your suggestion to use two tables, but i am
way past that. i am doing one simple table for receipts
for money earned. all i want to do is search the 12 date
fields for july dates so i can run monthly reports.
Click to expand...

I'm sorry, but YOU ARE MISTAKEN. If your database has 163 tables it
needs 164. A table with twelve date fields is not in third normal
form, and will be the source of unending problems (not least when you
get 13 receipts...)
 
again thank you for your reply, but this reply doesnt help
me at all. i thought that as an "MVP" it was your job to
help people. i appreciate the constructive criticism, but
can you please offer a suggestion instead of insulting my
design.

thank you,
Rob
 
Rob,
Apparently you don't want to take solid advice. Many of us don't usually
like to suggest solutions that aren't in the best interest of good practice
application development.

After reviewing your posts, I don't have any idea what your field names
might be and what type of data they are storing. I can only assume they are
storing date values since you original post stated "Between #07/01/04# And
#07/31/04#". You should take the time to:
1) enter some sample data with field names
2) describe what you want to query
3) enter the results you would expect to see from the query
4) accept constructive suggestions from experienced developers
 
i am willing to accept criticism and would LOVE some help,
but if you look at the last mvp's post it did not offer me
any suggestions and/or help. he made a claim and had no
information to back it up. he suggested that i have poor
table design, which if he explains to me why and offers a
suggestion as to how to design better i would gladly
listen. i am sorry if i come off as rude, i had a
deadline to meet and find that if people blatantly insult
my work without offering suggestions or help, it is
completely useless to all.

For those of you who actually helped me, i thank you.

-Rob

-----Original Message-----
Rob,
Apparently you don't want to take solid advice. Many of us don't usually
like to suggest solutions that aren't in the best interest of good practice
application development.

After reviewing your posts, I don't have any idea what your field names
might be and what type of data they are storing. I can only assume they are
storing date values since you original post
Click to expand...
stated "Between #07/01/04# And
 
I am not sure how anyone can help unless you provide answers to the
questions I posed. I have no idea what your fields and records look like
from what you have provided.

Also, please use upper and lower case characters. You will rarely see a post
from an MVP (or others who help here a lot) that doesn't make an effort to:
1) spell correctly
2) use paragraphs to break up larger posts
3) use both upper and lower case characters to make reading easier
4) use proper punctuation (excuse my forgetting question marks quite
frequently ;-)
 
again thank you for your reply, but this reply doesnt help
me at all. i thought that as an "MVP" it was your job to
help people. i appreciate the constructive criticism, but
can you please offer a suggestion instead of insulting my
design.
Click to expand...

I apologize, Rob. That did come off as very highhanded.

Please note that I did reply in my previous message, indicating how
you can use AND and OR in your current table design: to quote

WHERE ([Column] = criteria AND (Date1 BETWEEN [start] AND [end] OR
Date2 BETWEEN [start] AND [end] OR Date3 BETWEEN [start] AND [end]))

Did you try this? Did it work? If it did not work, how did it fail?

My suggestion for normalization was:

If you have a one to many relationship, don't create twelve fields in
one table - use *two tables* in a one to many relationship, with one
receipt in each *record* in the many side table.

That is, rather than having fields Date1, Date2, Date3, .... , Date12
in your table, move these repeating fields out to another table. This
new table would have a foreign key linked to the primary key of your
current table (I don't know what that field is, of course, since you
haven't posted it). This would let you use a single criterion.

The normalized design has many advantages; not least is the simpler
search (you can use just a single criterion). More importantly,
though, it does not arbitrarily restrict your table to twelve and only
twelve dates, but allows as many dates to be entered as will be
needed.
 
Back
Top