concatenating fields for links in queries

hey all,

i would like to link a PolicyNumber field concatenated w a field called 
INVSUB to a PolicyNumber field in another table. can i do that or do i have 
to create another field and update it to [PolicyNumber] & [INVSUB]?

here is the FROM section of SQL code. i would like to concatenate 
ALLCOMPANIESDATA.[POL#] w ALLCOMPANIESDATA.[INVSUB]

"FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords ON 
ALLCOMPANIESDATA.[POL#] = qryCPRackleyRecords.[Policy Number]"

TIA
Ted 



0
Ted
10/25/2007 7:09:44 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
818 Views

Similar Articles

[PageSpeed] 19

You can join on a calculated value, however you cannot build this type 
of query using the Design view, but must set the join up in SQL view.

FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords
ON (ALLCOMPANIESDATA.[PolicyNumber] & ALLCompaniesDate.[InvSub]) = 
qryCPRackleyRecords.[Policy Number]

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Ted wrote:
> hey all,
> 
> i would like to link a PolicyNumber field concatenated w a field called 
> INVSUB to a PolicyNumber field in another table. can i do that or do i have 
> to create another field and update it to [PolicyNumber] & [INVSUB]?
> 
> here is the FROM section of SQL code. i would like to concatenate 
> ALLCOMPANIESDATA.[POL#] w ALLCOMPANIESDATA.[INVSUB]
> 
> "FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords ON 
> ALLCOMPANIESDATA.[POL#] = qryCPRackleyRecords.[Policy Number]"
> 
> TIA
> Ted 
> 
> 
> 
0
John
10/25/2007 10:16:15 PM
Reply:

Similar Artilces:

Just one more table to link!!!!!!
-- Kate Hulit Asyma Systems Inc. I can help you but need more details. Can you email me the report at staceyleelee@hotmail.com "Kate Hulit" wrote: > > -- > Kate Hulit > Asyma Systems Inc. If you are trying to link tables to a report and are finding it difficult to create the relationship, you can always use VBA if you are registered for Modifier & VBA to read the data directly from SQL. Just a thought. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Micorosoft Dynamics (form...

Use Query or Macro to Creat enew Database
I need to figure out a way to create a new database from within an existing database during a process in which i am running several queries. the new database name will have a generic name like "Quarterly..." but will then be concatenated with the quarterly submittal date in a specific format (e.g., 20070601) I'm thinking i could create a query to determine the quarterly date and then concatenate with the other part of the file name, but how would i actually create a process for creating a new database (in a specified directory) from within my existing directory. any sugg...

Multiple Account Lookup fields
Hi all, These newsgroups have been an immense wealth of knowledge, thank you to all who contribute! I have one item that I'm struggling with. I have added a custom entity and added two N:1 relationships back to the accounts. This is giving me two lookup fields on the form for the custom entity which is what I wanted. However, what I didn't realised is I now show the custom entity twice on the Account form. I'll give an example of what I'm looking for, let's say I have a custom entity for a Building. What I want is to have a lookup field linked to an Account fo...

Re: Windows 7 - Odbc link
"zafer özbek" <awds@microsoft.com>, iletide şunu yazdı news:... > ı am zafer > no english > "Anne" <Anne@discussions.microsoft.com>, iletide şunu yazdı > news:DB6C48AA-DE22-446E-B8E7-82DC5143CA38@microsoft.com... >> Hi >> >> Thanks for responding. I corrected after an update :) >> >> >> "Paul Shapiro" wrote: >> >>> Not sure, but it might work if you right-click the Access shortcut and >>> choose Run as Administrator when you start Access. You might be bette...

Derived Field
I'd like to insert a derived field into this situation tblShipments (Parent table) Shipment_No Shipment_Size tblShipment_Inspection (Child table) Shipment_No (connected field) Item_Dimension (part of the item being inspected to see if it is "good") Number_Out_Tolerance (number of items in the shipment that are bad for this dimension) I'd like to create the field "Der_Percent_Good" that calculates the percentage of good items in the shipment. (1 - Number_Out_Tolerance/Shipment_Size)*100. How would I do this since the tables a...

Outlook 2010: Unable to open an internet link in a received message
Whenever I try to click a link appearing in an email I get a message saying someting like: "operation aborted due to limitations defined for this computer. Contact your systems manager (it's in Norwegian so I have to translate to the best of my ability. My OS is Windows7. I use Norton Internet security, latest edition. Can anyone tell me how to control this? Jan Erik, Oslo, Norway "Jan Erik" <jefrithj@online.no> skrev i meddelelsen news:1fit56d2ruccndr7pmneqrlm32qohghgnr@4ax.com... > Whenever I try to click a link appearing in an email I get a message > sa...

Break Links command
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I have several workbooks, most of which have internal links between worksheets. Suddenly the &quot;Break Links&quot; option from the &quot;Edit&quot; command is not active in ANY of my workbooks, including those with many links. Even if I open a new workbook and creat a link between two worksheets, the &quot;Break Links&quot; tab is inactive. <br><br>I've repaired permissions and ran Disk Warrior. Didn't help. <br><br>Any help would be appreciated for I rea...

Incorporating extender fields into the smartlist
Hi everybody, I have created a couple of extender fields under purchasing>Receiving Transactions Entry. I need to bring these fields under the smartlist relevant to the same window. Can anybody help me on this please. Thanks Hi Victor. In GP 8, if you go back to the design of the eXtender window, you should see a button on the lower right marked Smartlist. From there, you have to select which smartlist objects to integrate with. There may be a small delay between setting this up and actually having access to the fields in Smartlist. Hope this helps, Maria. "Victoria Menzes"...

hyperlink list in excel that stays put when it jumps to linked cel
I am trying to use internal hyperlinks in a huge sheet but want the hyperlinks to remain in view - and just for good measure, it has to open with the links visible on other machines. Any suggestions would be much appreciated, TIA ...

Error 3845 Linking
The code below generates error 3845 as I am using an Access 2003 .MDB file and the new table gets created in 2007 format. How can I create the table in 2003 format? I attempted to convert the database to 2007 format but received multiple errors when using the interface and importing all objects into a new database. With CurrentDb Set tdf = .CreateTableDef(strTBName(i)) tdf.SourceTableName = strTBName(i) tdf.Connect = ";DATABASE=" & ls_import_filename .TableDefs.Append tdf 'ERROR .Close ...

Creating new table columns and maintaining joins in another query
Hi, I have a table with alot of account information. However, to report on certain accounts, I have to manipulate a few of the column data and create a new column with the correct information. Once I have this new column, I do another query and get the information that I need out of that new column. The problem is when I'm querying from that new column, I'm join'ing that information to another column of information and it works the way I want it to work. But when I get more data, I have to run the original script that manipulates the data and creates a new column, agai...

How to stop automatic links
Whenever I type text into outlook that includes an @ symbol, Outlook turns it into a link. That is extremely annoying - how do I turn it off? For example, if I want to talk about the name attribute of a book element, I write "book@name", so outlook underlines it and turns it blue as if it thinks it's an e-mail address. It isn't and I can't stop Outlook from doing its thing unless I go to plan text e-mail. -- Rob Version? Try tools, options, spelling, autocorrect settings, autoformat tab. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Ou...

Linking Question #3
I have several books that are all in the same folder (master templates) I'd like to link them with out recording the "full path", that way I can copy the files to "project" folders and use them. But I don't want them pointing back to the files in the master template folder. Can I declare some kind of variable %HERE% so that the linked files always searches the folder where the current file is located? Thanks for any suggestions. xjvs xjvs, Once way to do it that always works is to open the several files from the master folder, then save each file into the p...

Output Query as an Excel file
I am trying to output a query as an excel file and save it to a specific location. Here is my code. DoCmd.OutputTo acOutputQuery, "Qry:SKU_ALL_2", acFormatXLS, _ "C:\RH DOC\LCM_PROJECT\LCM_QRY" & "\" & "LCM_QRY" & "_" & "PERIOD" & "_" & ([Forms]![LCM_DATA]![PER]) & ".xls", False I get a run time error '2306' that states there too many rows to output, based on the limitation specified by the output format or by Microsoft Access. There are 36,464 records. When I take...

Links between cells
I have cells linked within my workbook so that I change a source tab and automatically update other tabs. The text updates fine. The color of target won't change when I update the source color. Any suggestions. Hi not possible in Excel. Even with VBA event macros not feasible -- Regards Frank Kabel Frankfurt, Germany "lsm" <lsm@discussions.microsoft.com> schrieb im Newsbeitrag news:D0940D5D-A044-4B50-945F-B5F749D0DAC7@microsoft.com... > I have cells linked within my workbook so that I change a source tab and > automatically update other tabs. The text updates...

web link #2
Hi, I would like to update one excel sheet with a web page which is having some daily currency rates.It changes daily wise, Can anybody help in this regard . thanks in advance, with regards nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10003 View this thread: http://www.excelforum.com/showthread.php?threadid=275994 Nowfal If you have XL XP or 2003 you should be able to go to Data>External Data>New web query. Navigate to the page and select the relevan...

Changing Names of Multiple Cell Link Ranges
I have a series of drop down boxes created with the Forms menu. The cell link range for each dropdown is different, as follows: Cell Link Box1 = DemandBase_A_UndistExp1 Cell Link Box2 = DemandBase_A_UndistExp2 Cell Link Box3 = DemandBase_A_UndistExp3 Cell Link Box...n = DemandBase_A_UndistExp...n I want to change them all at once to: DemandBase_A_OtherCost1 DemandBase_A_OtherCost2 DemandBase_A_OtherCost... I know I can change the entire name using the following code: Sub Change_Drop_Link() For Each bx In ActiveSheet.DropDowns If bx.LinkedCell = "DemandBase_A_UndistExp1" Then ...

Pop up Form to set parameter of query to generate a report
I have a pop up form that opens when I run a report via a macro. The query searchs for an item (drug) that has 2 different mnemonics. (fields are mnemonic2 and mnemonic3 from table PDM) I cannot append the table fields into a new column because the table is on a server that does not allow changes. The problem is that I cannot figure out a way to attach [Forms]![frm_mnemonic]![lstbox] ---the combobox I am using on the form ----to mnemonic2 and mnemonic3 so it it will search both since they are both text values. I can attach it to one or the other field and it works fine, but when I attach it ...

What is the size limit of the notes field in an Outlook contact re
When I looked at the properties of the notes field within a contact record in Outlook, it displayed he following information Field Properties Name: Notes Type: Integer Format: 1,234 I was expecting a memo or text data type? ...

vendor master should have separate field for 1099 legal name
Need to insert a field about the Vendor Maintenance Options window above the field labeled Tax ID. The new field should be labeled 1099 Tax Name or 1099 Legal Name. This field should be 40 characters. This field should then be the field used on the 1099 form at year end. It could be pre populated with the name entered on the vendor maintenance window but changed as necessary. This woud allow users to continue using the Vendor Name and Vendor Check Name fields as normal for every day business requirements. ---------------- This post is a suggestion for Microsoft, and Microsoft resp...

Queries?
Is it possible to make an "update query" to roll back year to date overtime hours? I have an tblhours that totals all overtime hours worked and refused in which I have ran queries to sum all of those. Once a year we have to rollback the year to date overtime hours which means we take the lowest employee, he goes to "0" and we subtract the lowest employees hours from the highest employees hours. I was trying to do this in an update query, but everything I try subtracts, for example, 10 hours from every line of overtime in the tblhours table. I know you are no...

Incrementing a Field to the Next Value
Hello: I need a T-SQL query that returns a string value and, from the latest dataset that I have, returns the next number. This would be, then, that string value "+1". Now, regardless of whether there are leading 0's, I want SQL to return the next number for this string field. This, in essence, is the value returned from the query "+ 1". So, if the value in the field were 000039, then I would want SQL through this T-SQL query to return "40". Or, if the field were 1157, I would want it to return "1158”. In other words, I want for...

Stored Procedure details in one query
Hi all, can anyone help in getting below information from all stored procedure from one DB we have 100 procedure in one DB of sql server 2005.instead if of manully getting info for each procedure i want all below details in one shot or one query Procedure Name : Input Parameter : Output Parameter : Called By : Calls : Dependent tables : Thanks in advance Abhi (bawejaji@gmail.com) writes: > can anyone help in getting below information from all stored > procedure > from one DB > we have 100 procedure in one DB ...

Web Link
I would like to create a link in Excel to a website that has currency exchange rate. I would then like to select specific rates to insert in the spreadsheet so that whenever the spreadsheet is opened, the exchange rates will be current per the website. Can anybody assist. Thanks Please reply to me if you figure out how to do this! I am running int the same problem -- Message posted from http://www.ExcelForum.com ...

mail merge form data to word field
I'd like to be able to fill-in data into an Access Form and then export the fields to a Microsoft Word Template with merge fields setup. Can anyone set me in the right direction? Take a look at http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html specifically the spuer easy word merge -- Hope this helps, Daniel Pineault "neenmarie" wrote: > I'd like to be able to fill-in data into an Access Form and then export the > fields to a Microsoft Word Template with merge fields setup. Can anyone set > me in the right direction? ...