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'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 an Access database where we grade reports. At the end of the grading form I have a memo field [Comments] for the grader to enter comments about why the report received the grade [Grade]. Each report is graded by multiple people. So for example, ReportNum 08-001 may be graded by 5 people and therefore will have 5 records in the database. I need a report to show all the comments for all the reports where the grade is <80. I know how to do this except for how to display all the comments from the memo field in the 5 records on one report for that ReportNum.
I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]
I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]
Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)
The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.
Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)
I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :
Code: SELECT [tblRates].[RateDate] FROM [tblRates] INNER JOIN [tblCurrencies]
[Code]....
But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.
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
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'm having a bit of a problem with a query that I am trying to run that searches a memo field, and I hope someone can help me!
Basically, I have a fairly simple database, 2 tables - 1 of the tables has a memo field which has 'keywords' listed in it (a mixture of words/phrases separated by commas, although I've tried with just spaces in between).
When I search using a Query box for 2 keywords using the following query, it can only find the correct records when I enter the keywords in the reverse order in which they appear in the memo field... query: Like "*" & [Enter the 1st keyword to search for] And Like "*" & [Enter the 2nd keyword to search for] & "*"
If the memo field has data eg, "funding, teaching" (as opposed to "funding, nursing" for example) and I search for 'funding' and then 'teaching' - no results appear. If however, I search for 'teaching' and then 'funding'... ta da! Result found.
Obviously, whoever searches the database isn't necessarily going to know what order the words were entered into the memo field!
So, is this a peculiarity to Access, or is there something I'm doing wrong?
In 2002, I set up a database with Access 97 which included a table with a memo field. Through the years, the database has moved from Windows XP and Access 97 to Windows 7 and Access 2007. Recently, the memo data has been difficult to edit (the display will move away from the cursor, or a new record will be inserted).
It appears to be logically straightforward to convert the memo data into multiple text fields as the memo field can be parsed for dates of entry.
I have a problem that is Vista related. My business partner has just purchased a laptop with Vista and one of the problem areas is SendKeys. I don't have Vista and he is away at the moment so we only have part time communication so I am trying a few solutions in the hope of getting the problem fixed.
He is getting a message along the line of.....Access needs to be reinstalled because SendKeys is not available.....he does not has his MSOffice with him so that can't be tried at the moment, although everything else is working OK.
There are a few macros involved but I have converted then into VB Module and that works OK for me on XP, although it does not carry out all instructions but it basically works. But it will be a few days before he can try what I have sent him.
Note: I just notice that my conversion to VB module is not doing the SendKeys. No messages etc it is if the section covering SendKeys was not there. The example below was done with XP and macro. The SendKeys and the various other actions are all in the module. Edit...All is OK....my conversion of the macros to a module worked:D
So I would like to try another possible solution and that being to avoid SendKeys.
The situation involves a file note entry system. Each file note creates a different record and in each record there is a memo field with the file note that was made and of course time and date.
There is another table that holds one record for each person that has a file note made. Whether one file note is made or a 100 file notes there is only record.......The record is created when the first file note is made and the table will not accept duplicates on the persons ID number.
When a file note is made the system then opens the record in the other table and adds with SendKeys to the entry in the memo field on the "one record only table"
A form opened on the "one record" looks like this and as you can see the contents of the memo field from the "many records" have been combined into one. Can I do this without using SendKeys
5/01/2008 11:34:51 AM Test 1 The quick brown fox jumped over the lazy dog
5/01/2008 11:35:21 AM Now is the time for all good men to come to the aid of the party
5/01/2008 11:35:50 AM Why did John have to buy a computer with Vista?
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
5/01/2008 11:37:38 AM zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz zzzzzzzzzzzzzzz zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz xxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a process which imports a txt file into a memo field of my TBLORIGINAL table Each file may have one record or it may have multiple records Every record begins (exactly) with
Quote:
FRE Order Order RAD ShipDate Customer City State Postal Code
For each record in the memo field, I need to create a new record into my TBLPROCESSED table
This can probably be done while it is importing but I need to keep the original file intact
I suppose I could copy and paste but there must a vba way of doing this
I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.
So I am desperately trying to find a way of doing this without VBA code.
Example of data:
Customer Name |Order Number Smith |O101 Brown |O102 Smith |O103 Green |O104 Brown |O105
I am trying to achieve below:
Customer Name |Order Number Smith |O101, O103 Brown |O102, O105 Green |O104
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.
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is: SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header FROM tblSomething WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
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 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 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 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
Product. Item Description A It's Red B It's Blue C It's Green D You'll love it
Category. Item Category_name A Cat1 A Cat2 A Cat3 B Cat1 B Cat4 C Cat1 C Cat6
I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:
ItemCategoryConsolodation. Item Desc Categories A It's Red Cat1,Cat2,Cat3 B It's Blue Cat1,Cat4 ...
How does one do that?!
What's it called when you try to do this so I can Google it?
I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?
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 was wondering if anyone had code to split a field by semicolums, to create multiple records.The field has the first line of address for each property with a particular postcode. For instance
field name: PRMF Craven House; Ample Mansion; WHSmith; Bulevard Terrace.
I want to be able to split fields like the one above with these properties by the semicolums so they become seperate records, but share the same other fields e.g. postcode, city...
