Concat Two Int Columns Into One
Jul 17, 2007
I got a access SQL question.
Let's say we have two int columns, I want to get the results Column1 + Column2 in string format
i.e
Column1 - Column2
5 - 2
6 - 2.5
10 - 6.5
10.5 - 8
I need to return the data as new column like:
NewColumn
"5 to 2"
"6 to 2.5"
so on
SQL + operator only works for string operation, how would I use it for numeric data type.
Thanks a bunch
shazaduh
View Replies
ADVERTISEMENT
Apr 6, 2007
I have a table:
id date status
====================
1 03/01/2006 W
1 02/01/2006 L
1 01/01/2006 L
1 31/12/2005 W
...
2 03/01/2006 L
2 02/01/2006 L
2 01/01/2006 W
2 31/12/2005 W
...
3 03/01/2006 W
3 02/01/2006 W
3 01/01/2006 W
3 31/12/2005 W
...
What I want is to concat the field status showing the latest 5 results, like this:
id status
=========
1 WLLWL
2 LLWWL
3 WWWWW
I have tried to use concat, but i can't get the desired one. Any idea to do that?
Thanks!
View 3 Replies
View Related
Nov 19, 2007
I have a TableA and one of the fields is FileName (filename1.mpg, filename2.mpg...).
On a Form, I have a box and I want there to have this string for the first row:
"/VIDEOS/filename1.mpg".
How can I do that? THANKS!
View 5 Replies
View Related
Jun 5, 2013
I am having trouble concatenating a field from an employee table where the format is "Last name, a comma, a space, first name." I attached a screen shot of my database.
View 2 Replies
View Related
May 2, 2008
Please see attached Sample,.
This may look like its fairly quick,.. but its the slow as hell,.. across the network "front end back end" especially when cross referencing againts qty's already ordered. "not included in sample"
I have only attached a basic sample of the query with some data, on my full system this acts very much like a Dlookup would do on a continuous form. "if you were foolish enough to use one there"
Check out the module for the code
My Question is: Is there an alternate "Speedy way" to produce the same results as seen in the sample.
To explain more,.. this is what i have and works fine,..."except having to wait a minute for the concat" which as you can imagine is very irritating for the user,.. when they have many lines to purchase.
When a job is created item required to be purchased are put in the Requests Table
Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order1114N4082-6Leading Edge SkinStock Required1eaPriority 1WO07-421
Often the same part is Required on a different job,.
Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order50114N4082-6Leading Edge SkinStock Required1eaPriority 1WO07-690
A query then creates a list to purchase
Code:ID #Part NumberDescription StatusQty ReqUOMWork Order1-50114N4082-6Leading Edge SkinStock Required2eaWO07-421 - WO07-690
The ID is then purchased against on the Order Screen
The Id goes into the purchase order table
The Id is then Split back down to its original numbers
The original numbers then look back at the Request table and update the status to Ordered
Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order1114N4082-6Leading Edge SkinOrdered1eaPriority 1WO07-421
Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order50114N4082-6Leading Edge SkinOrdered1eaPriority 1WO07-690
And so on,. When the unit is received it changes the status to received.
I can't do this with a temp table,.. that will take a bit of explaining so please take my experiance and word on that.
Sorry about the Table Formatting,... Is there another logical way to do this.
View 5 Replies
View Related
Jan 20, 2015
I have a Concatenate string of text and currency. When joined together, the currency format disappears. I need to reformat so the string stays together with new format. Below is what I have:
Public Function ConcatAgreementFundsCommitted(ID As String) As String
'/ Purpose: Generate a concatenated string of Tracks for selected line.
On Error GoTo Err_Handler
Dim lngLen As Long 'Length of string.
Dim strOut As String 'Output string to concatenate to.
[code]....
I need 'Funds_Committed' to display as currency.
View 2 Replies
View Related
Nov 6, 2014
In Excel I can do the following;
Field A Field B Result
Chips Fries Replacement of 'Chips' with 'Fries' on menu.
(null) Fries Addition of 'Fries' on menu.
Chips (null) Deletion of 'Chips' on menu.
Can I do this in Access using some form of append query?
View 5 Replies
View Related
Apr 16, 2015
DoCmd.RunSQL (" update tbl_userinformation SET [05-Henrichpiramid] = Yes where Username= Text146.value AND actualdate=Text148.value ;")
DoCmd.RunSQL (" update tbl_userinformation SET [combination] = [05-Henrichpiramid] where Username= Text146.value AND actualdate=Text148.value ;")
i want to update the column combination to its last value with concatanation to the value of current column.
View 11 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Oct 30, 2007
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
View 4 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Jul 31, 2013
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
View 12 Replies
View Related
Feb 19, 2008
Hi All,
I have a Append query which has columns from two tables. Now i need to create a new column "Sum" in new table which should Add two of the Number columns .
That is : I have column A and Column B; I need to create a Column C which contains (A+B)
Please advise.
Thanks,
Kon
View 8 Replies
View Related
Jan 24, 2007
Hi,
I'm new at access but getting the swing of it. It is a pretty cool toy.
I have a table that I am adding various columns of data. Once added I move the columns to where I want them to be. I am hitting the little save disk on the top toolbar but when I reopen the file the columns are not where I placed them. I'm not sure why this is happening. Any thoughts?
Thanks in advance for your help.
Eddie.
View 6 Replies
View Related
Dec 30, 2004
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar
View 6 Replies
View Related
May 6, 2006
Hi I have search the forums for an answer to this..i have 4 fields address 1, 2,3,4 and i would just like to all all the information to one field call address..how can i merge the information..please bear in mind i am useless at access..i thought there might be a way of copying the columns and just having a merge function to do so ..but cant find anything...please help..Williebear:(
View 4 Replies
View Related
Aug 22, 2006
Hello again!
How do I prevent the information in lookup columns from showing up in other tables, queries, etc... as numbers.
Example.
Lookup to Fruits. Where the records for fruits are Apple, Banana, Orange, and Pear.
The lookup column lists all the items {Apple, Banana, Orange...}, but in queries the data just appears as numbers {1, 2, 3}.
Many thanks for anyone's help in advance!
View 5 Replies
View Related
Dec 22, 2006
Hello.
Here's some background on what I'm trying to do. I am building a database that will hold data on 31 parks. Each park will contain many advertisers and each advertiser may be in one or many parks. Theres more to this but i will just stick to what I am having trouble with.
The tables i have made thus far pertaining to my question are:
1. "Parks" table. It has two cols one for ParkID (key) and ParkName
2. "Advertisers" table. It has 9 cols for things such as AdvertiserID (key), addresss, etc...
3. "AdsData" table. This is the problem table. It contains AdID (key) and ad content columns (copy, names, driving directions, etc...). In addition to the ad content however, I need each ad to indicate which of the 31 parks it is located in (one or many) and which of the 16 park activities (things like hiking, swimming...i have another table for these) it is to appear in (one or many).
The only solution I have been able to come up with is to make a table ("AdsData") with 55 columns in it. In addition to the ad content data for each ad, there are 16 yes/no columns for selecting one or more activities for the ad to appear in and 31 yes/no columns for selecting one or more parks for the ad to appear in.
This seems excessive because I have a table with all the Parks in it and I have a table with all the Activities in it. Plus I've been told that having so many columns in a table is a bad, bad thing.
But in my limited knowledge of Access, this is the only solution I can come up with.
Is this the only way to do this?
View 3 Replies
View Related
Jul 30, 2005
I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:
Budget Total Expenditures Balance
Thanks.
View 10 Replies
View Related
Dec 3, 2005
Hello,
I cannot find anyway to do a querry search in different columns.
For example:
a movie database, a movie has more genres, so i have a column for genre1, genre2 and genre3. now i want to be able to search in all three columns at the same time for 1 genre, is that possible ?
View 1 Replies
View Related
Jun 15, 2006
If I have a date column and another column with another consistant date, Can I make a criteria to compare one to the other. Ex:
END_DATE Today's_DATE
06 Feb 05 15 Jun 06
12 Feb 05 15 Jun 06
01 Jan 06 15 Jun 06
26 Mar 06 15 Jun 06
18 Aug 06 15 Jun 06
28 Dec 06 15 Jun 06
Can I make a criteria that will only show the END_DATE data that is before Today's DATE? Does it need to be a new column or can I place it in the criteria of one of these column.
View 1 Replies
View Related
Nov 14, 2006
Hi
I have a confusing situation. I have a need of creating a combobox which displays records from different tables.
Eg.
I have a table called "house parts" and filled with records like
room, hall, garage
Secondly i have a table called "Parts" and filled with
floor, ceiling, lamp, window etc.
As u see, "house parts" could consist of "Parts" like "room" could have floor, ceiling etc.
I need to create a query that shows me all the records from "house parts" and also "Parts" in the same list.
Can anyone help me please
View 4 Replies
View Related
Feb 23, 2007
NEVER MIND--- I FIGURED IT OUT:o
I have a table with two columns.
FIRST LAST
James Jones
R Kevin Foster
When I use this:
Left(FIRST,1) & " " & LAST AS Expr1
I get: James Jones
R Foster
Is there a way to correct this so R Foster comes back as R Kevin Foster?
View 2 Replies
View Related
Feb 27, 2007
Hi all,
Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc
transform it to
701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....
Thanks in advance
View 3 Replies
View Related
Apr 4, 2007
I have a column in a table that contains the date and the time, i want to be able to split this column into 2 so i have one column for date and one for time
msgtime
03/07/2007 09:59:00
03/07/2007 10:02:00
03/07/2007 10:02:00
03/07/2007 10:04:00
03/07/2007 10:04:00
03/07/2007 10:39:00
03/07/2007 10:39:00
above is the column is a sample from the column i need to be split.
Any help is very appreciated
View 3 Replies
View Related