If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Fields Query Question
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*")); |
#2
|
|||
|
|||
Multiple Fields Query Question
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". |
#3
|
|||
|
|||
Multiple Fields Query Question
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" wrote in message news 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*")); |
Thread Tools | |
Display Modes | |
|
|