I have an Access 2000 project and am trying in a query view to join 3 values into one.
2 of the values come from tables, the third comes from another query view (all linked in the query design screen)
Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty.
All values are strings.
I have tried this formula in the query design grid:
I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.
I have a project that in Access. I'll be handling a survey file that will maintain answers per customer. The visit to customer is done monthly in a year, which means that a customer can have 12 visits in a year. My problem is that if I will maintain this project for the next five years or more, I have to maintain another field in my table that will block the duplicate entries.
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
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
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?
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.
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
I have about 36 names and each of those names appears anywhere from 2 7 times each. Each name entry has a comment1 and a comment2.What I would like to do is create a form that allows me to choosea name and the display the comments 1 and comments 2 in text boxes.
I have made the form to allow me to choose a name and it shows one of the comment1 and one of teh comment2 in the adjacent text boxes as planned but, I want all the comment1s for Bob to show in the text box for comment1 and all of the comment2s for Bob to show in the Comment2 text box.I believe I need to concatenate the results in the comment1 and comment2 boxes, but I do not know where or how I would do that.
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.
I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.
I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.
I am working on form where the user selects either "IN" or "OUT" from a dropdown of field name "CheckOut" in Frm1.
If they select "OUT" they will in turn need to fill in 2 additional fields. When they change the value back from "OUT" to "IN",
I want those other fields to be cleared of data for just this record so next time they change back to "OUT" from "IN" those 2 additional fields are already blank.
tblSFDC (contact information) includes CustID (primary key) and a memo field "Notes"
tblTheCall (call information) includes CustID (relates to tblSFDC) and "activity data fields" ie "Live", Left Message" , Sent email".
tblTheCall may in some cases have multiple records for each CustID
I need to : Concatenate from tblTheCall Field Names and field values (that are not null) Insert into tblSFDC.Notes Where tblTheCall.CustID = tblSFDC.CustID
I have a combo box which is populated by a table with the fields ID, First Name and Surname. What I want to do is display the first name and surname concatenated and hide the ID column, but populate the field with the ID number.
I need to concatenate these three fields into one but for each record the order can be different. What I want to do is have another field in the table to store the concatenation order as the field names not the field values.
I also need to display the concatenated field as the actual field values and not field names in a form or a report
So if field4 was the concatenated field store it would store the information like this
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.
Value 1: FormRegNo CombinedParticipants DC-190 Political parties, CSOs, community residents
Value 2: FormRegNo CombinedParticipants DC-190 Political parties, CSOs and community residents
Function: Option Compare Database Option Explicit Public Function ConcatField(strSQL As String) As String Dim dbs As Database Dim rst As DAO.Recordset Dim strConcat As String
Hi everybody. I got a access 2000 query that lists :
1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project )
12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point.
The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task.
Notes:
- There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database) http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population) http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)
query that display hourly wages of certain project during each week
SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;
code for querythis week( calcualte the salary and hours worked)
SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary FROM dbo_Hours_worked;
