 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005same Criteria for several Fields within a Table?
Hi, I have a table that has 7 fields all of which may contain a given value. I need to add criteria that selects records where *any* of these fields equal a given vale. It's complicated by the fact that this value could actually be one be of a list of 5 All of the the 7 fields effectively need the follwing criteria: Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*" I know I can enter the same criteria seven times, to get the "OR", that works, but is there an easier way? The question has been listed here, but when using a parameter in the criteria, where as mine is the same list each time. Any advice appreciated, I'm happy to hack the SQl if that's the way to go Cheers Matt I don't know of an easier way to do what you want, as long as your table structure is like you have it. Is there some reason that you have 7 fields that contain such similar data? You may need to consider normalizing your table.  SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html > Hi, > [quoted text clipped - 19 lines] > > Matt I *think* the table has to be done this way. I certainly wouldn't be able to redesign it as too many process's already use it. It's medical data - the codes describe a process. Each series of treatment can be coded up to 7 times, but it's done on a priority basis so a C101 procedure maybe one persons primary treatment code, but someones elses secondary code so it would appear in the second field for them. If it can't be done no problem, but it's always woth looking for the quick way! Cheers Matt > I don't know of an easier way to do what you want, as long as your table > structure is like you have it. Is there some reason that you have 7 fields [quoted text clipped - 24 lines] > > > > Matt From the little bit of information you have given here, I would say that it does NOT have to be done that way. If your table has a procedure field and a procedure type field, then you could enter multiple procedures for a given person and give each one a type. Thus, avoiding the need for the repeating fields. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html >I *think* the table has to be done this way. I certainly wouldn't be able >to [quoted text clipped - 44 lines] >> > >> > Matt True, I'd probably have gone that route with a "primary procedure" checkbox or something, but as I say the table's already in place and used my many people/process's so I can't change it - plus we'd have to migrate the existing data. Cheers for the response though, excellent service, I'll be back I'm sure! Matt > From the little bit of information you have given here, I would say that it > does NOT have to be done that way. If your table has a procedure field and a [quoted text clipped - 50 lines] > >> > > >> > Matt IF your values are always Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*" You could shorten this a bit and use one value under each column you are searching Like "c7[1-5]*" That finds all matches starting with c7 followed by the digit 1 to 5 followed by anything. > True, I'd probably have gone that route with a "primary procedure" checkbox > or something, but as I say the table's already in place and used my many [quoted text clipped - 72 lines] > > >> > > > >> > Matt Much neater, thank-you! Matt > IF your values are always > Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*" [quoted text clipped - 79 lines] > > > >> > > > > >> > Matt |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.