General :: Combine More Records In One Using Comma Delimited

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 Replies


ADVERTISEMENT

Taking 1 Field From Multiple Records And Merging Into One Comma-delimited File

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

General :: Programmatically Importing Flat File Comma Delimited To Access Database

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

Importing Comma Delimited Text File

Mar 4, 2005

I used the get external data tab and went thru the process. everything looked good in the preview but when I clicked finish I got type mismatch errors and the data that was supposed to be in field 1 was in fieild 2 and so on.

View 1 Replies View Related

Trim Comma Delimited Value Stored In A Text Field

Apr 27, 2007

With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.

But I am confused since have never used comma like in query

View 1 Replies View Related

Forms :: Comma Delimited List In Memo Field

Oct 9, 2013

I have inherited a database where technicians track equipment that has been repaired.In this database, there is a memo field where the technician lists all the part numbers he / she uses to repair the piece of equipment. This field was never reported against and was strictly used for reference. However, someone is coming to me now and asking that a report be generated with that information. Then she will separate those parts out on her end. because it is a free form field, the technician is not forced to put a comma between each part number. I was wondering if there is a way for the database to go through approximately 62,000 records and ensure that there is a comma between each part number? I was also wondering if there is way to program the database to automatically insert a comma after each part number.

View 5 Replies View Related

Queries :: Creating Query From External Comma-delimited List Of Words

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

General :: Taking Two Records And Combine Them Into One

Jul 20, 2012

So I have two tables. One table "tbl_SRVASSET" lists out servername, serialnumber and asset tag. Second table "tbl_SRVNIC" list out the same servername, IPAddress and IPAddressTypeDesc.

The issue is that I have records in the "tbl_SRVNIC" with the same server listed numerous times (because it has 2 or 3 NIC's). So there is obviously a one to many relationship betweeen the two tables (common I guess). What I'm trying to do is be able to take the servers in the "tbl_SRVNIC" table and a new table or create a query with a single record for that server and have all 2, 3, 4 or maybe more IPAddress's assigned to them listed in the same record. This is what I'm having trouble with...

The end result is that after I've got this new table that lists only a single server and it's associated NIC's in the same record I can then combine that data with the "tbl_SRVASSET" via a basic query so my end result is a brand new table "tbl_SRVDETAIL" that looks like this:
SERVERNAME, SERIALNUBMER, ASSET, SERVER NIC1 IPADDRESS, SERVER NIC1 IPADDRESSTYPE DESC, SERVER NIC2 IPADDRESS, SERVER NIC2 IPADDRESSTYPE DESC, SERVER NIC3 IPADDRESS, SERVER NIC3 IPADDRESSTYPE DESC, (and maybe more columns if there are more, but that is a variable as some servers have 2 NIC's, Some with 3 or more so...).

View 1 Replies View Related

General :: Exporting To Tab Delimited UTF8?

Jul 16, 2013

When I export a table or query by right-clicking its name > export to text > with formatting and layout > choose encode as Unicode UTF8 it creates a file where all the values are surrounded by boxes, which I guess means its fixed width although it didn't ask me if I wanted the file delimited (example attached)

How can I create a tab delimited text file using UTF8 encoding?

View 1 Replies View Related

General :: Populating Combo Box With Delimited Field?

Aug 21, 2012

So I'm working on an Access 2007 application for my company's suppliers list, but I am restricted to using a single Excel worksheet for the back-end. One of the fields, called [Files], is a list of files in .pdf format related to a supplier. The number of files can be 0, 1 or many. I currently have this field set up so that each file is separated by a semi-colon. A sample first record in [Files] is:

K:Users est user2008-12-24-ASTM D5857-08.pdf;
K:Users est user2012-03-12APCPP25-Black.pdf;
K:Users est userDIN_16901.pdf

The front-end contains a multi-item form. I'm trying to add a ComboBox (cboFiles) for each record, which is populated by the value in [Files]. Then, selecting an item in the ComboBox should automatically open that file.

I've tried a few approaches so far for populating the ComboBox:
Setting [Files] as the Control Source results in a single item appearing as
"K:Users est user2008-12-24-ASTM D5857-08.pdf;K:Users est user2012-03-12APCPP25-Black.pdf;K:Users est userDIN_16901.pdf"

I also tried setting the ComboBox Record Source Type to Value List and created a hidden TextBox (txtFiles) with the Control Source set as [Files]. Then I used VBA in Form_Load to equate the ComboBox Row Source to the TextBox. The code is simply:

Code:
Private Sub Form_Load()
Me.cboFiles.RowSource = Me.txtFiles
End Sub

While this does create a ComboBox with the correct items for the first record, it also populates the ComboBox for all the other records with the same thing, ie. every ComboBox has the items:
- K:Users est user2008-12-24-ASTM D5857-08.pdf
- K:Users est user2012-03-12APCPP25-Black.pdf
- K:Users est userDIN_16901.pdf

I'm thinking maybe this approach could work with some modification in the code or approach. Unfortunately, I'm not too clear on why the code goes through all the Me.cboFiles.RowSource, but only once on Me.txtFiles or how to get it to check the txtFile for each row. Otherwise, I will need to figure out some other way to fill in the ComboBoxes.

Edit: Actually, I've noticed something peculiar. Each time I select an item from a ComboBox, the other ComboBoxes for the other records also change to the same item. I think this has to do with cboFiles being unbound. Changing it to bound doesn't seem to be quite right though as it just gives the same default values as txtFiles while the items are still just copied from the first cboFiles. Also, I can't actually select any of the items when it's bound. I imagine it might have to do with the read-only attributes of a linked Excel File.

View 6 Replies View Related

General :: Exporting A Query To A Tab Delimited Text File

Nov 26, 2013

I am able to successfully export data from an Access 2010 Query to a Tab Delimited Text file without difficulty.My problem is that the Query includes several 'tick boxes'. The resulting text file shows the text boxes as 1 or 0 as appropriate. What I actually require is a Y/N result.To achieve the required Y/N result requires some fiddly find and replace editing which is complicated by the fact that the query also contains telephone numbers incorporating 1 & 0, This then requires further editing of individual records to convert misplaced Y/N back to 1/0. Is their any way that one can force the export to convert text boxes to Y/N rather than 1/0.

View 8 Replies View Related

General :: Import Non-delimited Text File Into Access

Apr 7, 2015

I am trying to import a non-delimited text file into access, but where there is a strict hierarchy to the records, i.e.

NAME:

AGE:

DOB:

etc. etc.

The field names are constant throughout the document but the pages are of variable length depending on what is in the fields.

View 1 Replies View Related

General :: Removing Comma From String

Jun 19, 2015

I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?

View 2 Replies View Related

General :: Export Query Or Report To A Delimited Text File

Aug 29, 2012

A little background. I need to export the results of a query I use to build a report. For Print Master software I need the "Field Names" in the text file as well as the data for a Mail Merge in Print Master (PM).

"The field name information in the file you have specified is missing or not correctly formatted. The first line of the file must contain the database field names. Make sure the "Export Field Names" (or similar) option is selected in the program from which you are exporting data."

Trouble is, when trying to export the report or query, Access has no "Export Field Names" option. It works if I first export to Excel and then from Excel to "txt" then to Printmaster. I would like to eliminate the Excel step. Therefore, how do or can I get Access Export to transfer the "Field Names" along with the field data?

View 8 Replies View Related

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 5 Replies View Related

Combine Records

Oct 26, 2005

I need to know how to combine two records. What I have is a text file that is imported into a table. The problem is, the text file has 4 fields on one line then 3 fields on the next line. Is there a way to combine these two lines into one record? I do not want to do this in the text file. I want to import the file and run some code to combine the two records into one record, delete the second line, and go to the next two records. What can I do? Sorry for any spelling but I have to run. Thanks for any help.

View 1 Replies View Related

Help Combine Records

Dec 19, 2007

Hi,all
There are 3 records of my table:
DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10
38 103 1012 22 2133 33
39 103 7 9 99
40 103 10 20

/"DBr" shows how fields should fill/

I want combine these 3 records to one record. What i need to do ?
DID DNom DBr DF1 DF2 DF3 DF4 DF5 DF6 DF7 DF8 DF9 DF10
38 103 1012 22 2133 33 7 9 99 10 20
thx

View 6 Replies View Related

Combine Two Records Into One

Jul 21, 2005

I have two records:

------------------------------------
field1 | field2 | field 3 | field4 |
---------------------------------
A | 1.1 | 1 | |
---------------------------------
A | 1.1 | | 2 |
---------------------------------

Is there any way to combine to records into one like that

------------------------------
field1 | field2 | field 3 | field4 |
------------------------------
A | 1.1 | 1 | 2 |
------------------------------

Thanks in advance for any ideas

View 1 Replies View Related

Combine Records - Please Help Me

Dec 20, 2006

Hello Everybody

I am trying to set up a query for a table which has following 3 fields
GroupNo
Name
Points

which has values in the following manner

GroupNo-Name-Points
204-------Jack---- 20
204-------Ryan---40
204-------Tita-----30

202-------Jack----35
202-------Ryan----24

205-------Jack-----56
205-------Ryan----73
205-------Tita------45

Is it possible to combine the above records by the GroupNo in the following manner with 7 different fields now?

GroupNo---Name1---Points1---Name2---Points2---Name3---Points3
204---------Jack------20---------Ryan-----40----Tita------30
202---------Jack------35---------Ryan-----24
205---------Jack------56---------Ryan-----73----Tita------45

Any help in this regard will he highly appreciated.

Thanks

View 5 Replies View Related

Combine Records With Same Project Name

Nov 22, 2005

I have a DB table that contains two fields: Project and Minutes. The time tracking software records each time I punch in or out under a certain project but records multiple instances for instance:

http://web.cortland.edu/leonardl/access_pro_min_tbl.gif

I want to combine the records that have the same project. Maybe a "make table query"? I am not sure. If anyone could offer any assistance I would be very greatful.

Thanks!

View 4 Replies View Related

General :: How To Combine 3 Field Into 1 In Query

Nov 20, 2014

how to combine 3 field into 1. For instance. I have a customer table with company name, branch, floor, department. then i am gonna create a query called customerextended and combine all these 3 field so that I can select them in the combobox.

I have a sample here but I just dont know how to edit it...

"Company name: IIf(IsNull([department]),IIf(IsNull([companyname]),[floor],[companyname]),IIf(IsNull([companyname]),[department],[companyname] & " " & [department]))"

View 4 Replies View Related

General :: How To Combine Four Fields Into One Field In Same Record

Jul 9, 2014

Using Windows 7,
Access 2010.

Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.

Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.

What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.

View 14 Replies View Related

Queries :: Combine Several Records In A Table Into One Record

Jan 22, 2014

How can I combine several records in a table into one record?

Suppose that I have a table like Table1 in the attached image.

Then I want to combine all records with the same value for Key1 in one record.

The result is shown in Table2 in the attached image.

I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?

Alternatively I could accept to turn to VBA that could do it. Any good links about this?

View 3 Replies View Related

Combine / Concatenate Multiple Values In Different Records That Have Same ID

Mar 13, 2012

I have to concatenate the data in multiple records into one record. They have "skus" associated with them.

I have two columns.

ColumnSku: Which contains a product sku
ColumnModel: Which contains a model numbers

ColumnSku can contain the same sku hundreds of times
ColumnModel can contain the same model several times but not for the same sku

What I need to do is this: For every time a sku is shown in ColumnSku, take the model in ColumnModel and join them together separated by a comma.

For example

ColumnSku|ColumnModel
SKU1111|Model11111
SKU1111|Model22222
SKU1111|Model33333
SKU1111|Model44444
SKU1111|Model55555
SKU9999|ModelHHHHH
SKU9999|ModelJJJJJ
SKU9999|ModelMMMMM

Would end up like this
ColumnSku|ColumnModel
SKU1111|Model11111,Model22222,Model33333,Model44444,Model5 5555
SKU9999|ModelHHHHH,ModelJJJJJ,ModelMMMMM

How do I do this?

View 1 Replies View Related

General :: How To Combine 2 Tables With Same Primary Key To Make One Table

Oct 18, 2012

How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?

View 7 Replies View Related

General :: Concatenate - Combine Dates Into One Cell To Have One Row Of Order Data

Feb 25, 2013

I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.

i.e. order number, price, order date, invoice date

same order number, same price, same order date, different invoice date.

How do I combine the second line with the first so that I only have the data once.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved