From: Ann on
Hi -

I need to query multiple fields to find out how many times a specific horse
was used for the purpose of a group or private lesson. My query is showing
me "all" billing reasons as opposed to only the "lesson" ones.

I've pasted the SQL below...is anyone able to help me with this? I just
want to see the horses that were used for lessons only.

I created this in Design View as I'm only familiar with creating queries
that way, so if you could please be specific with any answers, I'd really
appreciate it, since I do not know SQL.

Thanks!
SELECT BillingReasonsTable2forSubform.HorseBilling1,
BillingReasonsTable2forSubform.BillingReason1,
BillingReasonsTable2forSubform.HorseBilling2,
BillingReasonsTable2forSubform.BillingReason2,
BillingReasonsTable2forSubform.HorseBilling3,
BillingReasonsTable2forSubform.BillingReason3,
BillingReasonsTable2forSubform.HorseBilling4,
BillingReasonsTable2forSubform.BillingReason4,
BillingReasonsTable2forSubform.HorseBilling5,
BillingReasonsTable2forSubform.BillingReason5,
BillingReasonsTable2forSubform.HorseBilling6,
BillingReasonsTable2forSubform.BillingReason6,
BillingReasonsTable2forSubform.HorseBilling7,
BillingReasonsTable2forSubform.BillingReason7
FROM BillingReasonsTable2forSubform
WHERE (((BillingReasonsTable2forSubform.BillingReason1) Like "*less*")) OR
(((BillingReasonsTable2forSubform.HorseBilling2) Like "*less*")) OR
(((BillingReasonsTable2forSubform.BillingReason2) Like "*less*")) OR
(((BillingReasonsTable2forSubform.HorseBilling3) Like "*less*")) OR
(((BillingReasonsTable2forSubform.BillingReason3) Like "*less*")) OR
(((BillingReasonsTable2forSubform.HorseBilling4) Like "*less*")) OR
(((BillingReasonsTable2forSubform.BillingReason4) Like "*less*"));

From: ghetto_banjo on
You really need to consider recreating this table (and others if
applicable) . Why do you have BillingReason1,2... and HouseBilling1,2,
etc setup that way? Why stop at 7? This is not a good way to setup
up a database. You are going to frequently run into problems like
this one, and others much more severe.

For example, if BillingReason4 = "lesson", its going to return all the
other BillingReason# since they are part of the same record.

You should read some articles on "Normalization".
From: Jeff Boyce on
Ann

Repeating fieldnames like that (HorseBilling1, HorseBilling2, ...) is almost
always an indication that you are trying to make Access work like a
spreadsheet.

If "normalization" and "relational" are unfamiliar terms, plan on brushing
up on them before trying to get Access to work (well) for you.

Or could you just use Excel?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Ann" <Ann(a)discussions.microsoft.com> wrote in message
news:D8C41138-640A-45FE-A53E-EFB2366964C3(a)microsoft.com...
> Hi -
>
> I need to query multiple fields to find out how many times a specific
> horse
> was used for the purpose of a group or private lesson. My query is
> showing
> me "all" billing reasons as opposed to only the "lesson" ones.
>
> I've pasted the SQL below...is anyone able to help me with this? I just
> want to see the horses that were used for lessons only.
>
> I created this in Design View as I'm only familiar with creating queries
> that way, so if you could please be specific with any answers, I'd really
> appreciate it, since I do not know SQL.
>
> Thanks!
> SELECT BillingReasonsTable2forSubform.HorseBilling1,
> BillingReasonsTable2forSubform.BillingReason1,
> BillingReasonsTable2forSubform.HorseBilling2,
> BillingReasonsTable2forSubform.BillingReason2,
> BillingReasonsTable2forSubform.HorseBilling3,
> BillingReasonsTable2forSubform.BillingReason3,
> BillingReasonsTable2forSubform.HorseBilling4,
> BillingReasonsTable2forSubform.BillingReason4,
> BillingReasonsTable2forSubform.HorseBilling5,
> BillingReasonsTable2forSubform.BillingReason5,
> BillingReasonsTable2forSubform.HorseBilling6,
> BillingReasonsTable2forSubform.BillingReason6,
> BillingReasonsTable2forSubform.HorseBilling7,
> BillingReasonsTable2forSubform.BillingReason7
> FROM BillingReasonsTable2forSubform
> WHERE (((BillingReasonsTable2forSubform.BillingReason1) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.HorseBilling2) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.BillingReason2) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.HorseBilling3) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.BillingReason3) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.HorseBilling4) Like "*less*")) OR
> (((BillingReasonsTable2forSubform.BillingReason4) Like "*less*"));
>