Queries :: Concatenate Multiple Rows Without VBA
Sep 23, 2014
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
View Replies
ADVERTISEMENT
Nov 16, 2013
I have a table:
"tb_skybox_Types", with the fields, [Policy_URN], [Box_Type]
Sample:
Policy_URN Box_Type
001 Standard
001 Sky HD
002 Sky +
002 Sky + HD
I'm mail merging this, so naturally it's giving me a row per box type against which I need to be one. Desired output:
Policy_URN Box_Type
001 Standard | Sky HD
002 Sky + | Sky + HD
I'm then going to make this into a temp table, then match the URN back to the customer table to get a single row per customer.
View 3 Replies
View Related
Jun 18, 2013
My database has a 'markets' table, where there could be multiple markets per campaign.
So right now in my report, it's displaying like this
campaign 1
Market 1
Market 2
Market 3
Etc.
Is there a way, to Concatenate multiple rows into 1 field. So it would look something like this
Campaign 1
Market 1, Market 2, Market 3
That would save a lot of space in the report, and make it easier to read.
View 11 Replies
View Related
Nov 4, 2013
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
[Code] ....
View 2 Replies
View Related
May 2, 2014
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.
View 3 Replies
View Related
Jan 31, 2014
A third-party application I use exports data with 3 header rows. I usually have to go into Excel and concatenate the rows into a single header row, then export the file to Access to apply queries. I want to minimize the steps in this process, so I'm wondering if it's possible to do this concatenation in Access (with or without VBA).
View 13 Replies
View Related
Mar 16, 2007
Hi,
Is it possible to update multiple rows in a Table at one time using Update Query?
Ta
Kasey
View 5 Replies
View Related
Jun 10, 2013
I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT
292020 M 5/11/2012 ($33.95)
292020 11 5/14/2012 $33.95
292020 A 5/30/2012 ($33.95)
View 3 Replies
View Related
Oct 11, 2013
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
View 7 Replies
View Related
Dec 22, 2014
I have a table with multiple rows per member that I would like to convert to 1 row per member. I have a table that looks a bit like this:
Member_IDDiagnosis
10000Hypothermia
10000Frost Bite
10001Fatigue
10001Dehydration
10001Exhaustion
99999Exhaustion
99999Hypothermia
And I'd like to convert it to this:
Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4
10000 HypothermiaFrost Bite
10001 Fatigue DehydrationExhaustion
99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
View 3 Replies
View Related
Apr 2, 2014
I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds
Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result:
PersList T1Component
Andrew Med 1
Andrew Med 2
Brett Med 1
Brett Med 3
Brett Med 4
Charles Med 2
Duane Med 1
Duane Med 4
Quy 2 Result
Regimen T2Component
Goody1 Med 1
Goody1 Med 3
Goody1 Med 4
Goody2 Med 1
Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return
Person Regimen
Andrew Goody2 (he has med 1, 3, and 4)
Brett Goody1 (he has med 1 and 2)
"Bad" would return
Person
Charles
Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
View 4 Replies
View Related
Nov 26, 2014
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date
00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014
00003 Bill Bates 1 1 11/04/2014
00003 Bill Bates 2 1 11/07/2014
00004 Jenny West 1 1 04/03/2014
00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference
00003 Bill Bates 11/04/2014 11/07/2014 3 Days
00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
View 5 Replies
View Related
May 13, 2013
I have a linked table. The table looks like:
Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3
I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:
Course name | Course Grade
Student 1
Student 1
Student 2
Student 2
What is the best way to do this?
View 8 Replies
View Related
Jan 31, 2014
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
Sample Data:
IDFullContent
459Authorized Privileges:
460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466UPGRADE VOLPRO WORLD
467Default Privileges:
Output Expected:
"
ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CM KRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,GROUP,GRPNAM,GRPPR V,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER...etcc "
The delimiter can be anything.
View 1 Replies
View Related
Feb 11, 2015
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3
1 x
1 m
1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3
1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
View 2 Replies
View Related
Mar 25, 2013
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I am currently using Access 2010.
View 5 Replies
View Related
Apr 10, 2014
I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.
View 9 Replies
View Related
Jul 16, 2014
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....
etc.
I could then quickly count how many obs periods started within the desired month.
View 14 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
May 19, 2015
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
View 14 Replies
View Related
Mar 9, 2007
Hi All,
I can use some help with a jumping off poit.
*Please see attached file*
I have 2 tables tblSFDC and tblTheCall
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
*Please see attached file*
TIA
Detrie
View 8 Replies
View Related
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
Mar 5, 2014
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.
ConcatRelated("License & Chr(13) & Chr(10)","Export_Car","[Dealer_ID] = """ & [Dealer_ID] & """")
My question is how do I add more fields to concatanate, such as Make and Model.
View 1 Replies
View Related
Aug 21, 2013
How to concatenate a number of fields from a table into a query.
The fields are:
Prefix
Firstname
MiddleInitial
MiddleInitial2
MiddleInitial3
Surname
Note that there will be varying combinations: some people will have a Prefix, Firstname then surname (no middle initials), so I would want to display "Mr Joe Bloggs". But others could have no Prefix but two middle initials, so "Joe P J Bloggs".
View 2 Replies
View Related
Jul 1, 2013
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
View 2 Replies
View Related
Jul 24, 2014
I am trying to generate a simple report that can be imported to excel for analysis. Currently the database documents failure causes associated with a repair. I need a "Report" that does not duplicate the failed items in the event there is more than one cause associated with the failure. some repaired items may have up to 3 causes of failure. What I would like to do is have the query show one record instead of three records (for the cases were three causes of failure were identified for a repair)
Current query format
Code:
facilityID Failure Cause
1 Electrical Surge
1 Mechanical Wear
1 Corrosion
2 ......
2 ......
.... ......
Desired query format
Code:
FacilityID Failure Cause(s)
1 Electrical surge, Mechanical wear, Corrosion
2 .....
.... .....
I was reading about a SQL transpose call but I do not need all fields transposed (only the failure causes transposed and concatenated into one field).
Test database attached with a query that shows the data I want to transform into the desired format.
View 6 Replies
View Related
