Need query to find for matches of one field within another field

I'm looking for a way to find the field from one table within the
field of another table. For example:
Table1 => Field1 = Group1
Table2 => Field1 = Group1, Group2, ... , Groupn

The query would allow me to compare Table1.Field1 with Table2.Field1
and see that "Group1" is within it. I'm used to doing this with the
LIKE command using specific text and a wildcard (i.e. '%group1%) but
not using fields. Any suggestions on how I can accomplish this?
0
kelly
3/18/2008 9:15:29 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1857 Views

Similar Articles

[PageSpeed] 13

On Tue, 18 Mar 2008 14:15:29 -0700 (PDT), kelly.salvatori@gmail.com wrote:

>I'm looking for a way to find the field from one table within the
>field of another table. For example:
>Table1 => Field1 = Group1
>Table2 => Field1 = Group1, Group2, ... , Groupn
>
>The query would allow me to compare Table1.Field1 with Table2.Field1
>and see that "Group1" is within it. I'm used to doing this with the
>LIKE command using specific text and a wildcard (i.e. '%group1%) but
>not using fields. Any suggestions on how I can accomplish this?

The Access (JET) wildcard is * rather than % - do you get the data with a
query like

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.Field1 LIKE "*" & Table1.Field1 & "*"

or, if the Group1 field might be free text so that you could get ambiguities
such as

Table1 => Field1 = "AT"
Table2 => Field1 = "CATS, DOGS"

then include the delimiters:

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.Field1 LIKE "*" & Table1.Field1 & ",*"
OR Table2.Field1 LIKE "*, " & Table1.Field1 & "*"
OR Table2.Field1 = Table1.Field1


Of course you're paying the penalty here for violating the principle that
fields should be atomic! Table2's design is simply WRONG.
-- 

             John W. Vinson [MVP]
0
John
3/18/2008 10:46:44 PM
kelly.salvatori@gmail.com wrote:

>I'm looking for a way to find the field from one table within the
>field of another table. For example:
>Table1 => Field1 = Group1
>Table2 => Field1 = Group1, Group2, ... , Groupn
>
>The query would allow me to compare Table1.Field1 with Table2.Field1
>and see that "Group1" is within it. I'm used to doing this with the
>LIKE command using specific text and a wildcard (i.e. '%group1%) but
>not using fields. Any suggestions on how I can accomplish this?

Use Like in thins sort of way:

", " & tbl2.Field1 & ", " Like "*, " & tbl1.Field1 & ", *"

-- 
Marsh
MVP [MS Access]
0
Marshall
3/18/2008 11:29:15 PM
Reply:

Similar Artilces:

Print Footer with Last Print Date field name
How do you create a Footer to print the Last Printed Date and Last Revised Date? Its not as obvious in Excel as it is in Word. Hi this is only possible with VBA inserting this information within an event procedure -- Regards Frank Kabel Frankfurt, Germany Ted wrote: > How do you create a Footer to print the Last Printed Date and Last > Revised Date? Its not as obvious in Excel as it is in Word. ...

Need help for an Excel formula
I am creating a P+L and need a formula for this instance. I am reasonably profficient on this software but have never had to generate a spreadsheet like this. Could someone advise a formula for this instance: I have a units sold column which generates the rest of the P+L, but there are some variable fields. 40,000 units are ordered no matter what, and then any amount over this would require a reorder, but this can only be done in multiples of 2,500. so if I enter 51,000 in the units sold field, i need the order costs to calculate an order of 52,500 as it would not be possible to order...

Wrap Text from one cell to another cell
I want type all my text into C111 (1-3 pages worth). I want this text to automatically wrap to D111 to E111 to F111 to G111, etc. The cell appears to have a maximum limit of characters. Please help. "bras" wrote: > I want type all my text into C111 (1-3 pages worth). I want this text to > automatically wrap to D111 to E111 to F111 to G111, etc. > > The cell appears to have a maximum limit of characters. Copied from the help file:- Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. Have y...

I am having toruble grouping all elements of my Visio drawing for into one single group
Experts, I am having trouble grouping all elements of my Visio drawing for into one single group. I need to do this so I can copy and paste the drawing into MS Word. Currently when I copy and paste not all the elements carry through into the MS Word doc. -- Spin What kind of trouble do you have? Is it that you can't group all elements at all? Is it that you can group all elements but only a part of that group appears in Word (e.g. the lower left corner of the Visio drawing)? Or do you successfully group all elements but pasted into Word the complete drawing lacks a few elements?...

Autostart query every day
Hi, is it possible to start a query every day at a specific time, automaticly? You don't say if you DB will be open - so I assume it will not (ie. a backup at night, send reports overnight, etc) You can use xmacro - something like this. msaccess.exe PathToDatabase /x NameOfMacro If I have not understand your question please answer with more details. -- Wayne Manchester, England. "Forza MIlan" wrote: > Hi, is it possible to start a query every day at a specific time, automaticly? You understand my question right (sorry for my English) 10x for the reply, but can y...

Sending the variable to a query as a criteria
Hello, I can send a value as a criteria to a specific query if it is a text of a textbox, combobax, label etc. But, i wonder if i can send a variable to a guery... I mean, such as... dim a as integer a= me.texbox3.value requery xxx... And in xxx, there should be column, for ex. xyz that has the criteria a.. But it does not work :( How can i do it? thanks... SupperDuck wrote: > Hello, > > I can send a value as a criteria to a specific query if it is a text > of a textbox, combobax, label etc. > > But, i wonder if i can send a variable to a guery... Create a us...

Filter for Same Value in Two Fields
There are two fields in my query. "From" and "To". For sake of ease, let's say both these fields are US Cities. How can I pull all the records that show shipped "From" Denver and that show shipped "To" Denver? Thank you in advance. You stated: shipped "From" Denver and that show shipped "To" Denver Technically this would mean both values were Denver which might not make much sense if you expected the From and To to be different. Assuming you are viewing your query in design view, you would type "Denver" und...

Need info on scheduled meetings: time scheduled/who scheduled it
We have a situation where many people have access to the managers calendar and can scheule meetings. It would be very helpful to be able to identify not only who scheduled the original meeting (and the date/time) but also who modified meeting parameters after it was orignally scheuled. The current 'properties' tab only tells you the last time the file was modified, which is typically not very helpful. thanks "Joe Bruin" <Joe Bruin@discussions.microsoft.com> wrote in message news:126EFECB-58AF-4253-A53F-C629E4A354F7@microsoft.com... > We have a s...

Building a Query by Form Interface
Hello, I'm trying to create a method where a user selects some criteria in a form and a query then generates the results. I have been able to pass numbers successfully in the query but not text. If anyone has an easy way of developing this let me know. The code below works for numbers but not text Help me please. Function BuildSQLString(strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String strSELECT = "s.*" strFROM = "UFRRecords s " If Check2 Then strWHERE = strWHERE & " s.[FlagField1] = " & Combo0 End I...

Showing one figure as a proportion of another in a bar graph
Hi, I have a table with three lines for every month of the year: one line showing the total we billed in the month, the other showing our total expenditure and a final showing profit. I want to use the table to create a bar graph that for each month shows the total billed and the the total paid out in the same bar. Can anyone tell me how to do that? Thanks Karl Hi Karl, Sounds like what you want is a Stacked Column chart. -- Cheers, Shane Devenshire "Karl" wrote: > Hi, > > I have a table with three lines for every month of the year: one line > showing th...

how I can sum or subtract 2 or 3 field in access in any record
hi I want know that why there is NOT any relationship between access and excel and how I can sum and subtract 2 or 3 filds in access in any record thanks hadi_khodaparast@yahoo.com Hello Hadi - I'm not sure I understand exactly what you are asking, but Excel is *not* a 'front end' for an Access database. You can use Excel to query the db & copy Access data into a workbook in several ways. That copy of the data can be used for calculations, but the data *doesn't* get changed in the Access file. Although the modified results can be imported to Access it may very well be t...

Grouping query titles or color coding them
Is there a way to group queries together like in folders? Or is there a way to change the font color of the query titles. WHEn you have so many queries to look through it takes so much time finding just the right one. I know that choosing the right title name helps but still if we could group them in some way or color code them. Even if we could put a line after so many queries or something. I see the Groups option in my data window. But how do you use them. I will look into the training center if they have anything on this. But thanks a lot for the lightbulb idea. "scuba...

where can I find CWinApp file in a regular dll (not extension) wizard created project
Hi. I created a dll using the regular (not extension) dll wizard in vc++ 6.0. I was wondering where I can find this CWinApp file so I can put initialization code and exit code in it? I don't see it in the file view and of course I am not very experienced with this ide. thanks for probably the obvious. Jeff Kish If it is not an MFC DLL, it probably doesn't have a CWinApp class. Find In Files is a useful tool to know about. Look for DllMain. See what it does on entry. If it doesn't call InitInstance, then you don't have a CWinApp class. joe On Wed, 02 May 2007 1...

Selecting the font size matching the control's height
Hello, I have an array of labels, which height changes in relation to the form's height (form is resizable). What will be the best method of selecting the labels caption font size? Thanks, Claire On 7 Feb, 04:46, "Claire" <replyto@fra> wrote: > Hello, > =A0 =A0 =A0 =A0 =A0 =A0 I have an array of labels, which height changes i= n relation to > the form's height (form is resizable). > What will be the best method of selecting the labels caption font size? > Thanks, > Claire Might be an answer here http://www.dreamincode...

export find results
is there a way to export the results of the 'find' query to a notepad or a new excel sheet) Hi Amir, > is there a way to export the results of the 'find' query to a notepad or a > new excel sheet) If you download my "Flexfind" utility, you can use it's "List" button to do just that: www.jkp-ads.com/OfficeMarketPlaceFF-EN.htm Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com I expect that the only way to do that would be through VBA. If you press Alt-F11 from Excel you'll go to the VBA editor. Go to Help and look up the ...

finder search across multiple xls for one term
I have about 400 spreadsheets nested in various folders on a single hard drive. Trying to find a long forgotten spreadsheet, which I can only identify by a value it contains. Let's say it's 114,167.18 I've used OSX 10.6.2 finder search with 'contains' and at first it yields nothing. When I lose the comma, ie 114167.18, several .xlsx spreadsheets appear as containing that value. Great! Trouble is, I need the original spreadsheet with that value and it's an older document, listed .xls as opposed to .xlsx It seems OSX 10.6.2 finder cannot look inside .x...

Print a sheet area to another...
Hi people, i have another question for you. Is it possible to print some of a sheet area to another sheet or a form? I have 40 different sheet's in a single workbook. Column A to M is headlines and row 7 to .......... is input in every sheet. My question is; After all the input is done the result in column M appears with text and a background color. Is it possible to print the row's that has ex. red color and the sheet name into another sheet or a form. I need a vb code that can find the red color itself and copy that row with the sheet name to another location no mather if th...

How to copy a file from a disk to a specified sector of another di
Hi, I want to perform the following operations given below. 1. Read a file from a disk 2. Write the same file to another disk 3. In the second disk the written file should be on the same cylinder/track/sector as it is on the first disk. Hoping to get a reply soon. TIA. regards, Jahfer V P. you can't do this at a file level. Thanks Vipin "Jahfer V P" <Jahfer V P@discussions.microsoft.com> wrote in message news:846B7A49-C2FC-405C-8B96-4CF48356717A@microsoft.com... > Hi, > > I want to perform the following operations given below. > > 1. Read a file fro...

more than one owner
Hi, Is there way to assign a case to more than one user. I need this because, the case must be seen & can be edited by two different business units which are completely isolated (one business unit is niether lower nor upper business unit of the other.). By the way, i need to do this task in crm 3.0 No there's not. You can only have one owner, but you can share the case with another team of users, so that they will have permission to access the case. The sharing overrides the normal BU-security. CRM 5 sounds like it will have the concept of team ownership, but that's a wa...

Excel Link to another Excel file and formulas
I have a formula that I want to "fill" down through my spreadsheet ='I:\filepath\[filename.xls]worksheet!$B$5 When I fill the formula down it does not change the reference. e.g. ='I:\filepath\[filename.xls]worksheet!$B$6 ='I:\filepath\[filename.xls]worksheet!$B$7 ='I:\filepath\[filename.xls]worksheet!$B$8 Everything remains as referencing $B$5 How can I get around this issue? Thanks, RICK The problem is that your use of an absolute reference is holding the cell reference constant. Try changing the $B$5 to: $B5 if you want to keep the column set as B as you...

Moving recipients from one container to another
My predecessor created a couple of individual mailboxes, while viewing the Groups container. So now I have two individuals who are listed with the groups. This isn't that much of an admin problem as it is a user problem, when they use the Recipients view of the address book (those two don't show up). Is there a way to move those two recipients into the Recipients container? You can use the Import/Export functionality in the Administrator Program: 160179 XADM: How to Move a User to a Different Recipient Container http://support.microsoft.com/?id=160179 Thanks, Fitz Crittle ...

Find name of main form
Novice, Access 2003, XP I want to pass a control name to another application. The control is on a subform, but I don't know the name of the main form because the subform is in various mainforms. E.g. fsubTxt contains txtBox1. How do I find the name of frmMain and then pass the frmMain!fsubTxt.Form!txtBox1 Thank you You can get the main form's name by using Screen.ActiveForm.Name Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Peter Stone" <PeterStone@discussions.microsoft.com> wrote in message news:20BA9DE7-0107-46CB-96F8-591...

How can I find duplicate entries in an entire worksheet?
I have pulled in information from a database and I am trying to remove duplicate records without having to look manually. Is there a way to do this? Take a look at http://cpearson.com/excel/duplicat.htm In article <BB25CF5A-3389-4C83-A0D6-05A6711BFF99@microsoft.com>, jbrown <jbrown@discussions.microsoft.com> wrote: > I have pulled in information from a database and I am trying to remove > duplicate records without having to look manually. Is there a way to do this? ASAP Utilities has a feature that does this nicely........... Free at www.asap-utilities.com Vay...

Can't find other users on exchange server to share calendar
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: Exchange I'm trying to set permissions to share my calendar with other users (both mac and PC) in my Entourage Calendar. When I click on &quot;Add User&quot; and enter the email address of the person with whom I want to share, I get a message saying, <br><br>&quot;no matches for your search are found.&quot; <br><br>When I select the ADVANCED Tab and enter the users information there, I get a pop-up window saying, <br><br>&quot;Entourage cannot modify perm...

Weird characters and missing Calculated Fields
Could you please advice me how to fix the 2 problems in GP10 Report Writer. I have 2 modified reports -- one of them the Calculated Fields are missing, and the other modified report where the Calculated fields' name have weird characters. Please find the below links show the 2 screenshots of the 2 problem issues. http://img34.imageshack.us/img34/4149/20091005missingcalculat.png http://img34.imageshack.us/img34/6564/20091002specialcharacte.png Did this happen as part of an update? If you export the report(s) to a package file and then open it up in Notepad, are the fields still missing...