Queries :: How To Get Access To Put These Records Together Separated By A Comma
Jul 31, 2014
I have an access table for SampleTests. Each sample may have multiple tests carried out on it. Each test is one record in the access table and a sample may have multiple records therefore depending on how many tests are to be carried out on this sample. I need to create a query that would have sampleID as one of the fields which it gets from the SampleTests table and then I need the second column to have all the tests associated with that sample ID in the format test1,test2, etc. but I cannot see how I can get access to put these records together separated by a comma.
View Replies
ADVERTISEMENT
Feb 11, 2015
I have a field email in the table 2015. I woud create a query that sort my mails separated by a comma. E.G.
123@456.com
456@789.fr
...
Sorted =
123@456.com,456@789.fr, ...
View 2 Replies
View Related
Jan 30, 2007
Hi
I'm having a senior moment...
I have a reference field - examples below and need to split into the component parts
example Ref_IDs:
C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07
C21/0052.04, Sheet21 ,E 2
C21/0054, Sheet 23, B 2, 05/01/07
First - ref no, Second - Sheet (note some have more than one), Third - Grid and lastly some have dates, but not all
View 6 Replies
View Related
Aug 26, 2013
I have a single 'text' box on a form that captures test scores. The person inputting the test scores puts in all of the scores separated by commas (77, 85, 100, 75.5).. Up to 6 test scores can be placed in this single text box. The field is formatted for text. What I propose to do is take the numbers in the text box and average them out and then display the test scores average in a number field named 'avescores' next to this field in the form. Once the inputer puts the series of scores into the text box, they would hit an 'update' button next to this box to 'update' the 'avescores' box. Right now they use a number pad and add up the numbers manually then place the average into the avescores box....
View 11 Replies
View Related
Apr 28, 2013
I've current got two tables, one with Products and the other with Suppliers. The products table has a column in which I may indicate Supplier IDs that carry the product. I'd like to make a report that groups the Products by the Supplier that carries them and I'm having some trouble. The way I see this working is to relate the Supplier ID found in the Product table with the auto generated Supplier ID in the supplier table.
My trouble is this, when the field is set to text I'm able to enter values separated by commas no problem but I can't report on them because I'd be relating a number to a string. How may I overcome this?
View 7 Replies
View Related
Feb 18, 2014
I would like update a table column values separated by ; For example
1. ColumA has no data, I want to do an update and place a comma at the end
2. If ColumA has already value do not delete add new valued at the end where ; if found.
My start attempt as follow:
Code:
qry_test= "UPDATE Table1 SET ColumA =" & "Enter Entered ;" & & _
" WHERE ID=" & 1
Set RS = cnnDB.Execute(qry_test)
What do I need to do to add more stuff where ; is found without delete existing value if values exist.
View 2 Replies
View Related
Jan 16, 2014
I use Access 2003. I am trying to populate a list box with the names of files found in specific folder in my computer. The problem is that if the file name has a comma (,) in it, so the name is being separated in two parts - first what is before a comma and second what is after. I need them exactly as they appear in directory in order to open the files from this list box.
View 2 Replies
View Related
Nov 16, 2013
I have a table like this below:
ProductID ProductName
1 AAA
2 BBB
3 CCC
I have a field in another table, like this:
RelatedProducts
AAA,CCC
AAA
AAA,BBB,CCC
I need a field in one of my queries that will return the related products field data like this:
RelatedProducts
1,3
1
1,2,3
View 8 Replies
View Related
Mar 22, 2013
If I have a text box (Text1) on my form with comma separated values entered i.e. 100,120,250,300 what would be the easiest way to enter these into a table column with a button click event. I would like to enter these values into my table tb_test under column Values.
View 7 Replies
View Related
Sep 30, 2011
I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.
When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.
What do I need to do to display the names ?
View 5 Replies
View Related
Apr 30, 2015
I have a list with 50 items in it, I would like to select some of these items by pasting in a coma separated list, is this possible?
View 12 Replies
View Related
Jun 18, 2013
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
View 11 Replies
View Related
May 7, 2014
I'm a new to Access, SQL. I need the following output:
Input:
PHP Code:
PO Vendor State
a, b 1,2 IL
c, d, e 3 TX
f 5 CA
Output:
PHP Code:
PO Vendor State
a 1,2 IL
b 1,2 IL
c 3 TX
d 3 TX
e 3 TX
f 5 CA
I found the following SQL code from another forum, which splits entries in a cell separated by commas into 2 rows, output being:
PHP Code:
PO Vendor State
a 1,2 IL
b 1,2 IL
c 3 TX
d,e 3 TX
f 5 CA
SELECT * INTO ImportedData
FROM (
SELECT PO, Vendor, State
FROM SourceData
[Code] ....
How to run a loop so that if a cell has n entries separated by commas, I want them to be split into 'n' number of rows.
View 3 Replies
View Related
Jul 20, 2012
I'm a new Access user. I'm looking for an easiest solution to combine more records into one, using comma delimited. Let's say that I have a table T1 with two columns Code and Client like this:
Code Client
X C1
X C2
Y C1
Y C3
Z C1
Z C2
Z C4
Z C5
I need to see those records in a Query, grouped by "Code" with the Client's value combined like this:
Code New
X C1, C2
Y C1, C3
Z C2, C4, C5
View 3 Replies
View Related
Oct 24, 2007
Hi.
Stupid question, but I have Access 97 database with ssn in each record.
I need to create a comma-delimted text file with these ssns.
When I use the export text file with a query that lists each record's ssn, it does not produce comma-delimited file.
How?
Russ
View 5 Replies
View Related
Jun 16, 2013
102,34112,021502503130010010000,QAR,1,24,1,{NULL:0 },{5:Y},6,14.5,31-MAR-2011
Above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.
For example if i specify range 2 to 3 then formula should return "021502503130010010000"
View 14 Replies
View Related
Nov 20, 2014
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
View 3 Replies
View Related
Jul 31, 2013
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
parte: Trim(IIf(IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))="",[Parte],Left([Parte],IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))-1)))
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
View 1 Replies
View Related
Mar 8, 2015
how to split this , I am new Access DB, I worked in Sql Server but access new to me
I have the below data
Appid Names
100 John,Bob,Kondya, Lima
200 Shor,James,Kim
300 Cinem
I want to convert to
Appid Names Name1 Name2 Name3 Name4
100 John Bob Kondya Lima NULL
200 Shor James Kim NULL NULL
300 Cinem NULL NULL NULL Null
View 2 Replies
View Related
Aug 4, 2015
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
Sort2: IIf([DELIVERY_HOUR]<8,[DELIVERY_HOUR]+24,[DELIVERY_HOUR])
View 14 Replies
View Related
Mar 25, 2013
I have a table (let's call it "tableA) with about 7 fields. One of the fields contains information that I need to do a query on so that I may find specific payees, for example:
The field is called "Payee" and in that field for each record, the information could be "At&T", "A T and T" or "Abercrombie & Fi", but there is also other information in that field along with At&T, etc., so the field for a record may look like this:
A T AND T MOBILI A T AND T MOBILI 10 90034 Bill Pay
or
ABERCROMBIE & FI 3042 PP 10 9111 DIRECT
Is there a way to set up another table (let's call it tableB) to have all the Payees names (such as AT&T or whatever) in it and by the push of a button (maybe macro), have a query go out and find all matches from tableB in tablea?
So, basically have the query look in tableA and find all the matches from TableB. Please understand that tableA could have records that contain the same name, but spelled a number of different ways, for example, AT&T or A T and T or ATandT or ATT. Again these names may or may not be embedded in with other information in a field.
Or is there a way that an Access query can look into an external list and find the records, example:
Have a text (file) list with all the names (separated by a comma or space - AT&T, AT & T, Amercrombie and FI, Abercom&Fitch, etc. Now have a query go to that list and find all records that match in my tableA?
View 3 Replies
View Related
Feb 4, 2013
I have one Access Database and i want to import the flat file coming from Cisco Phone Logs, its a comma delimited that contains the column names in the first row, and in the second row, its the data type, then the succeeding rows contains the data of the logs which are in Comma separated values, I want to put it to my created table programmatically,I used Docmd.TransferText but this will not let me define the row which i wanted to start at row 3.
DoCmd.TransferText acImportDelim, , "tblImportTextFiles", Me.txt_SelectedDirectory & "/" & Me.lst_FilesInDirectory, -1
Attached is the text file i received from Cisco Call Log Applications.
View 3 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Oct 17, 2013
i want to count records in progressive access table .in simutanasly column like excel "countif" function if Attached File
Column A Column B
A Count (A) =1
A Count(A)= 2
A Count (A)=3
B Count (B) =1
B Count (B) =2
A Count (A)= 4
View 3 Replies
View Related
Sep 12, 2014
I have a table called tblTarget, with 3 fields: Autonumber ID, Number F1, Number F2. F1 and F2 are indexed, allow duplicates, and are not required. I'm trying create an append query. SQL code is the following:
Code:
Insert into tblTarget
SELECT *
FROM (
Select 1 as F1, 1 as F2 from onerow)
-onerow is a dummy table.
When I run the query, I receive the error in the title: Microsoft Access Didn't Add 1 Record(s) Due To Key Violations. I've tried many syntaxes for the query, they all supposedly work, but they lead to this error.
View 5 Replies
View Related
Mar 24, 2013
I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.
View 4 Replies
View Related
