Another simple query

Dear all,

I have a table that contains first names and last names in different
fields. Is there any way to write a query that will give as a result
the first name and last name in the same field?

I hope this makes sense.

Thanks in advance!

0
Homer
4/23/2007 1:47:49 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
897 Views

Similar Articles

[PageSpeed] 1

SELECT
   [last_name_field] & ", " & [first_name_field] AS whole_name
FROM
   [your_name_table]

Change the field and table names to whatever your actual fields and
tables are.

Cheers,
Jason Lepack

On Apr 23, 9:47 am, Homer <peter.a.r...@googlemail.com> wrote:
> Dear all,
>
> I have a table that contains first names and last names in different
> fields. Is there any way to write a query that will give as a result
> the first name and last name in the same field?
>
> I hope this makes sense.
>
> Thanks in advance!


0
Jason
4/23/2007 1:54:02 PM
Type an expression like this into the Field row in query design:
    FullName: Trim([First Name] & " " & [Last Name])

Use your own field names inside the square brackets.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Homer" <peter.a.rich@googlemail.com> wrote in message
news:1177336069.749867.260320@q75g2000hsh.googlegroups.com...
> 
> I have a table that contains first names and last names in different
> fields. Is there any way to write a query that will give as a result
> the first name and last name in the same field?
> 
> I hope this makes sense.
> 
> Thanks in advance!
0
Allen
4/23/2007 1:56:47 PM
In the query grid you can use:

FullName: [FirstNameField]&" "&[LastNameField]

Homer wrote:
>Dear all,
>
>I have a table that contains first names and last names in different
>fields. Is there any way to write a query that will give as a result
>the first name and last name in the same field?
>
>I hope this makes sense.
>
>Thanks in advance!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1

0
StrayBullet
4/23/2007 1:57:51 PM
No overpost intended Jason. Cannot find a delete button though...

Jason Lepack wrote:
>SELECT
>   [last_name_field] & ", " & [first_name_field] AS whole_name
>FROM
>   [your_name_table]
>
>Change the field and table names to whatever your actual fields and
>tables are.
>
>Cheers,
>Jason Lepack
>
>> Dear all,
>>
>[quoted text clipped - 5 lines]
>>
>> Thanks in advance!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1

0
StrayBullet
4/23/2007 1:58:31 PM
On 23 Apr, 14:56, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Type an expression like this into the Field row in query design:
>     FullName: Trim([First Name] & " " & [Last Name])
>
> Use your own field names inside the square brackets.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Homer" <peter.a.r...@googlemail.com> wrote in message
>
> news:1177336069.749867.260320@q75g2000hsh.googlegroups.com...
>
>
>
>
>
> > I have a table that contains first names and last names in different
> > fields. Is there any way to write a query that will give as a result
> > the first name and last name in the same field?
>
> > I hope this makes sense.
>
> > Thanks in advance!- Hide quoted text -
>
> - Show quoted text -

Brilliant pure brilliance!
Simplicity at it's best

0
Homer
4/23/2007 2:10:08 PM
Meh...  I was faster... (thumbs nose...) <g>

Don't worry about that at all.  As long as the OP gets what he needs
then it's all good.

Cheers,
Jason Lepack

On Apr 23, 9:57 am, "StrayBullet via AccessMonster.com" <u33042@uwe>
wrote:
> In the query grid you can use:
>
> FullName: [FirstNameField]&" "&[LastNameField]
>
> Homer wrote:
> >Dear all,
>
> >I have a table that contains first names and last names in different
> >fields. Is there any way to write a query that will give as a result
> >the first name and last name in the same field?
>
> >I hope this makes sense.
>
> >Thanks in advance!
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1


0
Jason
4/23/2007 2:14:53 PM
Reply:

Similar Artilces:

Deleting specific records based on query results
Is there a way to use a select query to find and then delete specific records in a database. My database records basic applicant information in one table (tblApplicants) and any interview data in another (TblActivities). I need to do an annual purge of applicants from a previous year who were never interviewed. I have a select query that returns the name and date entered from the TblApplicants table where the requisition data in the TblActivities table is null. I know I can then go to the TblApplicants and manually delete each record, but at over 1700 records that seems a lit...

matching a column of numbers to another in another spreadsheet
I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Acessing function with a DLL which has be loaded from another dll
I have A.dll and B.dll. Assume that A.dll has following functions: A1() A2() Assume that B.dll has following functions: B1() B2() I am loading B.dll from function A1() in A.dll.After Loading B.dll i make a call to B1().After executing the call the function returns to beginning of A2.dll. why is this so? Define "begining". The word tends to make no sense in this context. What you seem to be saying is that you have void A1() { HANDLE h = ::LoadLibrary(_T("B.DLL")); ...test for h==NULL, deal with error typedef void (*B1)() B1Proc; B1Proc B1; ...

Grabbing a number from another cell on another worksheet
Hello, I have a spreadsheet with a number of different worksheets. All I want to do is if I enter a number into a cell on the 2nd worksheet for example, I want it to automatically show in a specific cell on the first worksheet. So a simple copy and paste but for excel to automatically do it for me! it seems like something so easy that I just cant figure out how to do. Try this... Do this once. Select the cell on the 2nd sheet Right click>Copy Navigate to the other sheet and select the cell where you want the value to appear. Right click>Paste Special>Paste Lin...

Creating column content based on another column's keyword(s) #2
Max, That scriptlet was very useful, and easily tweakable to work in myria applications. Thx! -K -- KHashmi31 ----------------------------------------------------------------------- KHashmi316's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1043 View this thread: http://www.excelforum.com/showthread.php?threadid=27348 Glad to hear that ! Thanks for posting back -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "KHashmi316" <KHashmi316.1evevz@excelforum-nospam.com> wrote in message news:KHashmi316.1e...

A simple Question
I am a little new to macros and I am having trouble using a suggested macro in my spreadsheet. My simple question is what do I need to do to change the following macro so that it will work on my spreadsheet. Sub DeleteEmptyRows(DeleteRange As Range) ' Deletes all empty rows in DeleteRange ' Example: DeleteEmptyRows Selection ' Example: DeleteEmptyRows Range("A1:D100") Dim rCount As Long, r As Long If DeleteRange Is Nothing Then Exit Sub If DeleteRange.Areas.Count > 1 Then Exit Sub With DeleteRange rCount = .Rows.Count For r = rCount To 1 Step -1 If Application.CountA...

modeling action queries
Hi- I am reverse engineering a database I wrote in access for documentatio purposes. Visio does not import my action queries. Do I have to writ the appends and make tables in SQL and then enter a new object as view? Here is an example of the SQL for one such append query: INSERT INTO CFO ( [Desc], [Budget Code], [Expend Code], [Spent De 2004], [Allocated Dec 2004], [Spent Jun 2005], [Allocated Jun 2005] [Spent Sep 2005], [Allocated Sep 2005], [Spent Oct 2005], [Allocate Oct 2005], [Spent Jun 2006], [Allocated Jun 2006], [Unit Description] [Department Description], [Requested Oct 2005]...

Populate data in cell by looking at another cells data
Hi Everyone, Hope I find you well. I'm not even sure how to go about this, so I hope that someone can shed some light. I have a serial number in one cell eg '80199DD270238' where 'DD' is code for another value, in this case 'DD' = 'BLUE'. How can I automatically populate a cell with the value 'BLUE' by looking at the serial number. Many thanks for any help you can provide. Best Regards Gazza Hi if the characters are alsways at the same place use =IF(MID(A1,6,2)="DD","Blue","other color") if they could be at ...

Sum dependant on '1' value in another column
Hi, Let's say I have a '1' or nothing in column 'L' and a time in seconds in column 'B'. The '1' means the time is a legitimate one. Can I do a sum of all the 'legitimate' times in column 'B' based on whether there is a corresponding '1' in column 'L'? Is there a formula that will allow me to do that? Sorry for the ignorance of the question but it is late and I am struggling to make this spreadsheet work for me. Thanks in advance, Dave Hi DL, Look at the SUMIF function in Excel help. Try: =SUMIF(L1:L10...

4 #Error Lines in a Query
I have a query that will show any HorseID that does not have a ClientName but for some reason it has 4 records showing with #Error in ClientName, any Ideas how this can happen and how do I clear them? SELECT IIf(IsNull([HorseName]) Or [HorseName]='',[FatherName] & '--' & [MotherName] & ' -- ' & funCalcAge(Format('01-Aug-' & [DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1) & ' -- ' & [Sex],[HorseName]) AS ClientName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.Sex,...

is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? #2
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? ...

Sub Queries
Hello, Does anyone know if it is possible to query within the results set of another query using MSCRM V3? So a user can create an advanced find, then the user queries from the results displayed from the search? Like a nested select. Thanks Jo Using Advanced Find, every line you add to a query narrows your results set further. So, for example, if you do an Advanced Find for Contacts where Owner | Equals | Bob Smith Email Address | Contains Data and this returns 50 contacts, if you add another line, such as Status | Equals | Active you will in effect be searching within your result...

Help with query
if field1 is greater than zero and less than 1 than 1 otherwise 0 Whats the right way to write that expression? QASPEC, NewCalculatedField: IIF([Field1] > 0 and [Field1] < 1, 1, 0) HTH, Conan "Qaspec" <Qaspec@discussions.microsoft.com> wrote in message news:7E8E2C7E-F5A6-417F-A2EF-1E832CBC0F1D@microsoft.com... > if field1 is greater than zero and less than 1 than 1 otherwise 0 > > Whats the right way to write that expression? > ...

Automatic Link to another workbook
I have a workbook created that, every time I open it, I get the question: Do you want to update the automatic link to data in another workbook? I didn't consciously set up a link and I don't want to get this message. How do I find this link and break it so I will not continue to get this message? Any help is appreciated. Close any other Excel workbooks you have open and do a "Find" for :\ (colon backslash). If you have several worksheets in your workbook, ask Excel to search the entire workbook by selecting find within "Workbook" instead of "Sheet&quo...

Copying from one ListControl to Another...
Hello everyone, I have been studying how List Controls work, and now I have two List Controls, A and B. Both of the list controls are in Report_View and they both have the same # of columns. What I would like to do is copy each entry of A into B. I use CListCtrl::GetItem(..) to get the item, and I use CListCtrl::InsertItem(..) to insert the item into B. However, this is not working. It is only inserting the first subitem. Is it possible to copy the entire item i.e, subitems 0-2 from CListCtrl A into CListCtrl B? Thanks ...

Multiple Queries Question
Hi All, I have a database to track awards for our program. I have created several queries and reports however I want to do a all inclusive status report for the Awards program. I have made Queries for Pending Awards for the Current Week, Previous Week, Previous Month, Current Month, Previous Qrter, Current Qrter, Current YTD, Previous Year. I also have Queries for presented Awards for Current and Previous: Week, Month, Quarter, Year I have a question that I am stumped on. The several queries that I have made I want to place into one report. In some cases one of these queries may have blank ...

concatenation query
Hello I'm guessing this isn't allowed but I'd welcome confirmation Can one concatenate a variable into a VBA command So for example instead of userform1. windowsmediaplayer1.controls.currentposition could you have userform1. windowsmediaplayer & ("range a1") & .controls.currentposition If so what would the correct syntax be? Many thanks In WHICH group do you want an answer? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "teepee" <nomail@nomail.com> wrote in message news:490db43d@newsgate.x-privat.org.....

Another question about IF(COUNTIF) checks in Excel
<Tiff1618@discussions.microsoft.com> wrote in message > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com... > > Hey again, > > > > Is there a formula I can use to figure out if there is one specific phrase > > in a selection? > > > > I'm updating the attendance prgram at the school that I work at. Every > > student has their own attendance sheet in Excel, and each sheet sort of > > looks > > like this: > > > > |Monday| > > Period 1:| A | (A=Absent; S=Seat time) > > Per...

combobox in query
Hello everybody, I have a question concerning the "Enter Parameter Value" window that opens when I run a query. Is it possible to have that as a combobox so that a list of possibilities is shown? Another question has to do with the same query. This asks a date from and to (in the query criteria >[from] And [to]. I would like the selected period to be shown in the report. Is this possible (and if so... how can I achieve that)? Thanks for all the help you can give -- Lisa Save the Dogs Onlus - www.savethedogs.eu THINK BEFORE YOU PRINT: before printing this e-mail think whe...

Turn Off Auto Query on saved query
I saved a query in MS-Query and now want to change it, but I cannot un-press the Auto Query button to allow me to edit it. How is that done? hi, on your external data tool bar is an icon for edit query. if you external data tool bar is not up, then RClick the tool bars at the top, click customize. click the toolbar tab. check the external data toolbar. Click somewhere inside the query range, then click edit query. follow the wizards instructions. >-----Original Message----- >I saved a query in MS-Query and now want to change it, but I cannot un-press >the Auto Query button to a...

Another If/Ten
HI all. Trying to use IF/THEN to look at Column A on one sheet, if any cell in that column equals today() would like cells from that row to fill in another sheet. Is this possible? Any ideas on how to make that happen?? Thanks. Flue. Maybe you can use format|Conditional formatting. Select the range to color (I used A2:X999). With A2 the activecell, I did: format|conditional formatting formula is: =($a2=today()) and give it a nice format Brian Bennett wrote: > > HI all. > > Trying to use IF/THEN to look at Column A on one sheet, if any cell in that > column equal...

How can I open a file saved in another version of Publisher?
I have Publisher 2000 and I am trying to open a .pub file but the message says it cannot open because the file is save in a different version of Publisher. Is there a way to open the file in Publisher 2000? Any help would be greatly appreciated. YvetteR wrote: > I have Publisher 2000 and I am trying to open a .pub file but the > message says it cannot open because the file is save in a different > version of Publisher. Is there a way to open the file in Publisher > 2000? Any help would be greatly appreciated. More than likely, this file was created in Publisher 2002 or 2003. Y...

Restricting query data
I have a query set so I pull DISTINCT dates in the SQL from 1/1/08 thru 12/31/08 to take out duplicate accounts, this works great when I run the report quarterly or yearly but I need to run this report monthly. I have 3 tables that work together and these dates are coming from my Activities table, I keep track of when I have had contact with them. I am looking to only count these people 1 time in a year (filter by start date does not work is it is driven by actibvity date) for example if I work with them in 1/08 run my report in 1/08 they should show up. If I work with them in 2/0...

Another Gag thread
What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. The mayor of New Orleans was asked about his position on Roe vs. Wade. He said he didn't really care how people got out of the city. -- * "AB" <AB@discussions.microsoft.com> wrote in message news:2B46FD0D-3AA8-4D81-B90A-3FB512EE1F8F@microsoft.com... What's the difference between an Irish wedding and an Irish funeral? One less drunk at the funeral. By looking at a picture of a person, you have to decide if he is a computer geek or a serial k...

Referencing another field
Hello, I have a Combobox field called "WorkerID" on a form. This field is not tied to the Forms data source. It is pretty much floating, no control source, but it does have a Row Source Type, Select Query that pulls Worker IDs form a table called "tbl_WorkerIDs". This table has both WorkerIDs and WorkerNames. I want to add another floating combobox field to the form to display the WorkerNames. So if I change the combobox WorkerID I need the WorkerName to change automatically. How do I configure the WorkerName combobox to look at the WorkerID field and upda...