Concatenate And LOOKUP Help…
Aug 24, 2007
Here’s my goal.
1.)I have one column and want to add a 633 in front of if.
a.Expr3: "633 " & [Grp] & ""
b.This only shows 633 and asks for an entry for the GRP? Why? I want the format to be 633 xxx. If I do add, lets say, 123 in the popup, all of the columns are 633 123.
2.)After combining the following, I want to take that number, and look it up in another query. If the number matches a number in the other query, than I want it to enter the number in column 1. If not, I want it write no number. I have gotten this part to work using an IIF function but I just wanted to let you know what I am after.
View Replies
ADVERTISEMENT
Jan 2, 2014
I have a table with a bunch of columns (call it Table A), one of those columns being a look-up (foreign key) to another table (call it Table B). I would like to have another column in Table A that is a concatenation of the value in 1 column on Table A with the look-up value from Table B. What is happening though is a concatenation of the value from Table A and the KEY from Table B. I am not aware of a way to get the concatenate to use the lookup value instead of the foreign key.
View 1 Replies
View Related
Jul 24, 2007
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
View 4 Replies
View Related
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
Mar 24, 2006
I have a problem I am working and and am not sure what the best approach is, hoping someone can lend me some insight:I have two fields on a table:SerialNumber....LocationI need to concatenate these two (no problem there). However my situation is such that my serial numbers vary in the number of digits. For example:serial - 55124 (five digits)serial - 552356 (six digits)serial - 5514235625 (ten digits)the serial number field is 10 characters and the serial numbers can be anywhere from one to ten in length (this is external data I am working with, not data I created)When I concatenate the two fields I need the location value to always be in the same place, so if I have a location called - TEST - I need the end result of my concatenate query to place the location always after the tenth place of the serial number position, like this:55124_____TEST552356____TEST5514235625TEST(Without the underscores)Don't ask me why...it's a long story.
View 2 Replies
View Related
Dec 9, 2004
I have a table that I would like to concatenate three name fields:
Last Name, First Name, Middle Name. I inserted a field between each one to create a space when the fields are brought together. The table looks like this:
Last Name, Blank 1, First Name, Blank 2, Middle Name
When these five fields are brought together they can not exceed 25 characters, so the middle name will cut off when the field reaches 25.
Any help will be appreciated.
View 1 Replies
View Related
Mar 2, 2005
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
View 1 Replies
View Related
Aug 27, 2006
Hoping someone can help me with this:
I have a table with two fields that I am wanting to concatenate. The two fields are: Order and Line#. The line numbers are sequential by tens, like this:
10, 20, 30...etc.
So I have order lines that are from two to four digits. Like this:
20
120
1020
All order numbers are six digits. I want to concatenate the order and line (easy to do) my problem is I want to append zeros in front of any line number less than four digits. For example, using the lines above and an order number of 111111 I want the result of the concatenation to be like this:
1111110020
1111110120
1111111020
Sorry to ask...but I don't know how to do it.
Thanks for looking!
View 3 Replies
View Related
Oct 17, 2006
I have two fields in a table.
client_id and property_id
client_id is an autonumber with a format of 0000000 (meaning the first record is 0000001)
property_id is made up from the first 4 letters of and address line (for example Smith Street would be shown as 'Smit')
I am trying to 'concatenate' the two id fields together, via an expression in a query, to get 'Smit / 0000001' but can only seem to get 'Smit / 1'
How can I solve this ?
View 4 Replies
View Related
Apr 6, 2007
My database has FirstName and LastName fields.
I would like to initialize a new field called UserID with the first letter of the first name concatenated to the last name. I have found references that point to using Left([FirstName],1)&[LastName] but am unsure if this can be done in the table definition or if it must be done using a query. Either way I could use some advise on initializing this fields since I have 3500 registered users.
Thanks
View 2 Replies
View Related
Feb 25, 2008
I have this query
SELECT [9A].DO, [9A].WORK, [BATCH] & " " & [SOURCE] & " " & [NO] & " " & [SEQ] AS RECEIPT,
FROM 9A;
It returnes the results like this:
RECEIPT
6/2/2003 P 29 10
1/15/2003 P 54 55
3/3/2003 P 42 7
1/6/2003 P 39 35
12/30/2002 P 23 30
What changes are needed to the query to return it like this:
06022003P0029010
01152003P0054055
03032003P0042007
01062003P0039035
12302002P0023030
In other words
The date must have 8 charactors with no /
Then P with no spaces before or after
The next number must have 4 charactors no spaces
and the last number must have 3 charactors
Thanks
View 1 Replies
View Related
Apr 4, 2006
Hi ...
I have a table called new items which has these fields
Name (Primary)
Serial No
Tag Type
Description
Status
I want the name to be unique and generates automatically and be SerialNo + Tag Type + Description <<<< NAME
I tried a querie which doesnt work
Code:[Description] & ", " & [SerialNo] & " " & [AssetType]
View 1 Replies
View Related
Jun 27, 2013
I concatenated two fields in a query for a cbo, one field has cpt# the other descriptions. CPT# can vary with respect to maybe a four digit, five digit, etc. Because of this, when joining these fields, descriptions are not lined up. Is it possible to fix this?
View 8 Replies
View Related
Jun 11, 2007
i'm using Master and Detail table. Master table i have TaskId, ResId where ResId is mapped to Detail table which contains resource working on the task. i want to generate a query where for each task i want to display resource id in single column like below.
Task Id Resource Id
1 222,233,244,255
Do i need to use recursive query or any other method to get results like above.
View 1 Replies
View Related
Apr 3, 2006
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer
Dim testcheck as Boolean
Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount
If Me.selectedproduct.Column(testcount) = True
Me.testnumber.Visible = True
Else:
Me.testnumber.visible = False
End If
testcount = testcount + 1
WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
Any help would be appreciated.
View 2 Replies
View Related
Mar 5, 2014
I have a table called Export_Car which contains Data such as License Number and Make and Model.I am calling a VBA function (in my query) that I have found online.This works fine and concatanates all the License Numbers for that Dealer.
ConcatRelated("License & Chr(13) & Chr(10)","Export_Car","[Dealer_ID] = """ & [Dealer_ID] & """")
My question is how do I add more fields to concatanate, such as Make and Model.
View 1 Replies
View Related
Mar 17, 2014
I would like to join 3 values to one new column called incident number. Problem is that one of the value is from a lookup field.
If the table look like
ID Type PNumber
1 INR 2477
I generated Incident number as [ID] "-" [Type] "-" [PNumber]. the result should be 1-INR-2477 but its shows 1-1-2477 because the Type column is a lookup field and INR is on the first value of the same lookup table.
How I could resolve this in the table or Query.
View 1 Replies
View Related
Sep 21, 2011
I have written a query to concat two strings,
select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]
THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).
View 1 Replies
View Related
Jun 15, 2007
I receive reports formatted as a .txt (flat) file.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:
Date Time User Status Customer Name ID#
06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf
1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.
When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.
I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.
2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?
At the end of each record in the report there is a space separating the records.
I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.
View 1 Replies
View Related
Apr 25, 2008
I have created a Table Design
Name
Last Name
ID Number
NameID
I import data from CSV xls file which has Columns of Name, Last Name, ID Number.
What I want to achieve is while importing it should Concatenate Name and ID number and store in Name ID.
I have searched it well but havent been able to find solution. Any suggestion how to achieve this.
Thanks
NIRI
View 4 Replies
View Related
Jul 21, 2005
Is it possible in Access SQL to concatenate a control into the select statements table or fields? Such as:
SELECT fldtotal & [Forms]![frmTest]![lstYears] & fiscal
FROM tblTotals & [Forms]![frmTest]![lstYears] & Archived
The statement would normally be:
SELECT fldtotal2003fiscal
FROM tblTotals2003Archived
Unfortunately, I am querying somebody elses tables. They archive the data yearly into a new table to control bloat. Instead of yearly having to create a new query for the new year, I was wanting to just have a form that had a list box of years. Then the user can just select what year they wish to query, and the query is automatically updating the field, table, where condition, etc.
I do not know if this is available in Access as it is in Oracle / Coldfusion. Thanks for any help though.
View 1 Replies
View Related
Nov 29, 2007
After searching through 18 pages of search results I have not found the answer. Would someone please tell me why this expression is not working in my query?
RCStatement: """&[RootCauseStatement]&"""
The result is "&[RootCauseStatement]&"
View 4 Replies
View Related
Jun 5, 2006
Hi,
Thanks in advance for your help.
I'd like to concatenate 2 name fields being TeacherFName and TeacherLName. No problem with that bit.
The bit I'm not sure on is that I'd like to have just the first 4 letters from the last name and the first 2 from the last, unless the last consists of less than 4 letters then I will fill with the first name. IE Dianne Cox becomes coxdia.
Can anyone help please?
Regards
Matt
View 1 Replies
View Related
Jun 29, 2007
To anyone who can help,
I have created a function that loops through an amount of records and concatenates them into a single comma-separated string. What I would like to do is concatente the values into a list. Instead of:
value1, value2, value3
I would like to see
value1
value2
value3
This needs to be in a single record only. The code I am using is:
----------------------------
while not rec.eof
string = string & ", "
rec.movenext
wend
string=left(string,len(string)-2)
--------------------------------
Thanks for all your help. I have tried to insert char(20), but this only seems to work in Excel. Access gives me invalid char symbol.
View 1 Replies
View Related
Aug 21, 2013
How to concatenate a number of fields from a table into a query.
The fields are:
Prefix
Firstname
MiddleInitial
MiddleInitial2
MiddleInitial3
Surname
Note that there will be varying combinations: some people will have a Prefix, Firstname then surname (no middle initials), so I would want to display "Mr Joe Bloggs". But others could have no Prefix but two middle initials, so "Joe P J Bloggs".
View 2 Replies
View Related
Jul 1, 2013
I have a table Pmt in Ms Access with following information:
Table Pmt
Cust no Inv no T no Date Amt
123 ABC T1 20110131 1000
123 ABC T2 20130228 1000
123 ABC T3 20130331 1000
789 XYZ A1 20130131 2000
789 XYZ A2 20130228 2000
Fields "Cust no", "Inv no" and "T no" are in Text format, whereas "Date" and "Amt" are in Number.
My question: how can I concatenate row values using a query. The query results that I wanted are as follows:
Cust no Inv no T no Date Amt
123 ABC T1, T2, T3 20130131, 20130228, 20130331 3000
789 XYZ A1, A2 20130131, 20130228 4000
View 2 Replies
View Related
