Multiple Fields Query Question
in [Queries]
|
Prev: DateDiff function throwing error in Where clause
Next: Summing multiple fields in matrix/table (qry)
From: Ann on 3 May 2010 15:38 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 3 May 2010 15:59 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 3 May 2010 16:06
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*")); > |