Combining 2 Fields Into 1 Without Spaces

Jan 26, 2006

I have 2 seperate fields within a select query (In this example Field 1 - Country and Field 2 Number).

I have combined the 2 together into one field.

However a space appears within my results i.e


Field 1 Country Field 2 - Number
----------------- -------------------
England 4
USA 10

Combined fields:

Field 3 - Combined Country + Number
--------------------------------------------

England 4
USA 10

How can I remove this space within Field 3


Thanks

View Replies


ADVERTISEMENT

Combining Multiple Columns Into One Longer Column (without Combining Fields)

Jul 17, 2013

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)

What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.

View 14 Replies View Related

Fields With Trailing Spaces.

Jul 31, 2007

I have tables that get their data from SQL server. Some of the fields in the table have trailing spaces. Does this issue need to be resolved in SQL server or in Access?

View 4 Replies View Related

Combining Fields

Oct 12, 2007

Hello,

I need to find a way to modify the following structure:

Name, Page, Grid
Acton Rd, G10, 12
Acton Rd, G10, 4
Acton Rd, G10, 8
Adams Ct, F6, 2

Into something like this:

Name Page Grid
Acton Rd, G10, '4, 8, 12'
Adams Ct, F6, '2'

What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.

Thanks,

View 3 Replies View Related

Combining Fields On A Form

Dec 20, 2006

Hay Folks,
I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form.
The problem.
I cant get the expression right to show real values instead of key-values.
Here's the sample expression for the control-field:
=[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]

Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]...
SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;

Can anybody help me on track?
Thanks a lot.

View 10 Replies View Related

Combining Fields For Display

Aug 15, 2006

I have fields lastname, firstname, spousefirstname, spouselastname -

I would like to display:

Koenig, Mark & Jane (not a problem)

- but in some cases the spouselastname is different so then I would like to display:

Koenig, Mark & Jane Doeinger

This is how it displays with my code:

Koenig, Mark & Jane & Doeniger

I can't seem to get rid of the & between the spousefirstname and spouselastname.

Code:SELECT Potentials.Agent, [LastName] & ", " & [FirstName] & IIf([SpouseFirstName] Is Null Or [SpouseFirstName]=""," "," & " & [SpouseFirstName]) & IIf([SpouseLastName] Is Null Or [SpouseLastName]=""," "," & " & [SpouseLastName]) AS Expr1, Potentials.Address, Potentials.City, Potentials.State, Potentials.ZipFROM Potentials;

How do I get rid of the second &

View 5 Replies View Related

Combining Fields To Filter For Same Addresses

Sep 17, 2005

Hello
I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:

Expression: (Trim(StrConv([AddressStreetNumber]&" "&[AddressAptNumber]&" "[AddressStreetName],3)

Then I set the Total line to FIRST

Is it my syntax or is it that I can't join separate fields together like you can in a report ?

View 2 Replies View Related

Combining Multiple Like Fields And Totaling

Nov 10, 2006

Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.

In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case

So clv1 might be done by User A
Then clv2 might be done by User B

But the next case it might be switched.

I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range

Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.

Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?

Well the db example is imb so it cannot be attached

Thanks for any help.

View 13 Replies View Related

Combining Multiple Fields Into One Colomn

Feb 15, 2007

Hello,

I have a table with employee numbers in four fields (Leader, Facilitator, ect...). How can I combine those numbers into one column trough a query?

Thanks in advance.

Keith

View 1 Replies View Related

Combining 3 Fields Space Problem

Sep 15, 2007

Hi,

Im combining 3 fiels with the following code.

Newfield: [Field1] & " " & [Field2] & " "[Field3]. So after eacht field a space is placed but when field 2 had no data there are 2 spaces between field 1 and 2. How do i solve this

You can react in euther english or dutch

Tank you in advance

View 1 Replies View Related

Combining First Letters Of Two Text Fields

Feb 21, 2005

In a form I can easily combine and display the content of two text fields in a third text field with the formula =[field1] & [field2]. Very well. Now I would like to combine and display only the first letter of each of the two text fields. This I would expect to go somewhat like this =(letter,1,1[field1]) & (letter,1,1[field2]). How is the correct formula?

View 4 Replies View Related

Combining Address Fields Into One Column

Sep 12, 2004

Hello,
How can I combine two seperate fields into one field? For example
field1 has 123 field2 has Smith St. I want to put it in one column
that has 123 Smith St.

Thanks

View 1 Replies View Related

Combining Fields In A Search Query

Jan 5, 2005

Hi,

I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:

Strd
12 Rock View
Str
Marston Cresent

change to :

12 Rock View, Marston Cresent

I can add fields together with an expression but cannot divide them by commas, any help appreciated.

M-.

View 6 Replies View Related

Reports :: Combining Same Values From Two Fields

Aug 21, 2013

I would like to make a report to show how many employees and which employees are attending to which colleges/universities.

In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.

There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?

For instance, this is my raw data:

Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC

I want my report to show:

Colleges -
University of HI
Bob
Sandy
Clare
John

Honolulu CC
Bob
Sandy

Kapiolani CC
Clare

Windard CC
John

View 4 Replies View Related

Queries :: Combining Fields In A Query

Jan 21, 2014

I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?

View 4 Replies View Related

MS Access Combining Fields Into A Longer One

Jul 27, 2014

i wish to combine multiple fields (there are no fixed number of fields, they vary depending on the data, so i guess union queries are out of the question) into one large field. For example:

TABLE 1:
PNumber
PName
C1
C2
C3

1
AAA
0.1
0.2
0.3

2
BBB
0.4
0.5
0.6

So i wish to combine the fields C1, C2 and C3 into a larger fields containing all the data. So considering the example above, it should look like this:

TABLE 2:
PNumber
PName
C1+C2+C3

1
AAA
0.1

1
AAA
0.2

1
AAA
0.3

2
BBB
0.4

2
BBB
0.5

2
BBB
0.6

I plan on entering data into TABLE 1 using a form and running a query, or some code etc so that it looks like TABLE 2.

View 14 Replies View Related

Combining Fields From Multiple Rows Into One Row & Field

Jun 5, 2007

Hello All,

I am trying to figure out the best was to combine fields from multiple rows into one row & field.

Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:

Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1

What I want is one row and the 3 footnotes combines into one field:

Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1

Any help would be greatly appreciated.

View 14 Replies View Related

Queries :: Combining Fields - Spacing Between Values

Mar 6, 2014

I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.

I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.

Code:

IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]

The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?

View 3 Replies View Related

Tables :: Combining Fields To Create Unique ID

Nov 14, 2013

How i would best combine values in a table to produce a 'primary key id number.'

For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233

I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.

View 14 Replies View Related

Combining Fields Form Different Tables Into The Same Column On A Listbox

Mar 15, 2007

Hi Everyone,

I hope I'm posting in the right place, I've been trying to solve my problem using queries so I thought it might be appropriate here.
I have a database for a health care service which contains among others the following four tables...

Table 1 contains client details, primary key [ClientID] plus other client details.
Table 2 contains Episode of care details, primary key [EpisodeofcareID], [episodeofcareDate] etc...
Table 3 contains all test results for assessment 1, primary key [CoreID], [CoreDate] plus numerous scores for individual tests.
Table 4 contains all test results for assessment 2, primary key [HonosID], [HonosDate] plus numerous scores for individual tests.

Table 1 is linked to Table 2, and Table 2 is linked to both Table 3 & 4.

I have set up a search procedure whereby the user enters a clients name which then opens a list box of all clients with that name. When the client selects from the listbox I want a second listbox to open which has three visible columns. One giving the date of each episode of care for the client, the second giving the date of each assessment completed and the third giving the name of the assessment carried out.

I'm managing to get the date of the Episode of Care to display on the first visible column but I'm getting stuck on the next two. This would seem to involve somehow combining data from different tables into individual columns on the listbox. For example I need the second visible column on this listbox to list all the dates of assessments for the chosen client from tables 3 & 4. (e.g. [CoreDate] and [HonosDate]). Column three would then give the title of the assessment next to the date in column two. None of the tables have a field listing titles as this is determined by which table the data is entered into. I've been trying to solve this by queries without much success.

Could anyone give some ideas as to how I could solve these problems?

Many Thanks

John

View 9 Replies View Related

Queries :: Identifying And Combining Rows Based On Matched Fields?

Jul 10, 2013

I have two tables. One table is a list of classes with the number of enrolled students:

Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15

And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):

Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2

Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:

Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19

View 7 Replies View Related

Blank Spaces In Data Fields After Copying Data With SQL

Apr 14, 2007

Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.

I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:

Do Until blnFound = True Or rs.EOF
'DEBUG: answer = MsgBox("ok", vbYesNo)
'DEBUG: MsgBox rs("Account Name")
If rs("Account Name") = strAName Then
MsgBox "DEBUG: FOUND " & rs("Account Name")
blnFound = True
End If

rs.MoveNext
Loop


The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).

I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).

The SQL statement responsible is as follows:
strASQL(i) = "INSERT INTO " & strTableName & "([CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3])" & _
" SELECT [CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3] " & _
"FROM tblCustomer WHERE [" & strField(i) & "] LIKE '" & strValue(i) & "';"

strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D

Any suggestions?? Cheers.

View 12 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Tables :: Combining Fields In Different Tables

May 29, 2014

I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:

1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]

I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:

a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.

View 1 Replies View Related

Spaces

Mar 17, 2008

I have a table w/field set as text that I don't want users to enter a "space" into, how would I stop this? I suspect " " in the field validation field? Does this sould correct? What does everyone else use?

View 4 Replies View Related

Disable Spaces

Dec 7, 2007

Hi, I have a text box in a from for data entry, how do I disable a space. For example I don't want the user to have a space between charaters or before and after a charater. Thanks.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved