Queries :: How To Concatenate Row Values Using A Query
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 Replies
ADVERTISEMENT
Feb 2, 2005
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:
dbo.Value1 + '/' + dbo.Table2.Value2 + dbo.vieTable3.Value3
..in an attempt to give the result Value1/Value2 or value1/Value3 according to the
particular record.
Unfortunately it doesn't work! ...just returns blank results when the query view is run.
Any suggestions would be greatly appreciated
thanks....nick
View 4 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
Nov 3, 2014
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.
View 11 Replies
View Related
Mar 8, 2014
i need to Concatenate a fields data into one cell in a query according to linked table ID....
View 3 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
Sep 6, 2014
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.
View 5 Replies
View Related
Jan 15, 2015
In Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).
Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...
This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:
"These were the votes of the owners of fractions:
O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78
How can I do it?
View 1 Replies
View Related
Jun 18, 2013
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
View 5 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
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 14 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
Aug 6, 2015
I have a Query contains field that is :
Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")
but [SerName] is a text type field that is not Primary Key.
I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :
Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")
but this returns all [NAll] records. I use concatenate :
Code:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
[Code] .....
View 14 Replies
View Related
Feb 13, 2014
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.
View 14 Replies
View Related
Sep 21, 2011
I have written a query to concat two strings,
select concat([first_name] & ', ' & [last_name]) as [Employee Name] from [employee_details]
THe above query is returning the result correctly only if both first_name & last_name fields are not empty. If the fields are empty, it is dispalyingcomma (,).
View 1 Replies
View Related
Jan 1, 2013
How do I concatenate a row in a query and put in a text box in a form? I have a Query returning up to 10 dates the row is called [Date] I need to put them all together with a space and a comma between them and place that in a text box on a form.
View 2 Replies
View Related
Mar 14, 2007
I have a query which select the records who have a certain criteria.
The result is something like this.
A 0012345
A 0032432
Note that the records that have this criteria will be smaller or equal than 2.
I want the query to give me this answer instead.
A0012345, A 0032343
(in the same field that is).
Is this possible, if so can someone help me. My email address is russcami@yahoo.com
Thanks
View 2 Replies
View Related
Jul 2, 2014
I have a parameter set on the query at run which filters by Region i.e. the box pops up and they enter a criteria..I would like to have a field entered next to Email that would take all the Email Addresses in my query result and concatenate them so essentially I have an email list I can pop in Outlook.
Or alternatively, a solution to allow access to these email lists via a button on a form. It has to be something very simple for an end user to access. For instance right now, to get there list they hit a button to run the query then enter the parameter then if desired, hit another button to export to excel. Today, they use Excel for everything and are used to having the concatenated email addresses in one cell in excel. For this reason, I am trying to have the email addresses concatenated in the query results to mimic what they are used to today. But I am not sold on it if there is a better way that is easy for them.
View 14 Replies
View Related
Mar 27, 2014
Watch the below tables.
I have a large ACCESS list as per the left one and I would like to make it as per right.
The problem is that I want to concatenate the "invoices" in one record separated with a coma , .
This is the only way I can compact the long list into a short one and make it comprehensible.
The invoices , in one cell, very rarely are more than 4 or 5 items.
So room for 6 is just perfect.
View 8 Replies
View Related
Jul 20, 2005
Here's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
View 3 Replies
View Related
Sep 5, 2014
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.
View 3 Replies
View Related
Mar 11, 2014
I want to add a parameter to a query that lets the user enter a number to filter on the 'Top Values' section of the query.
I.E the query shows sales by product, the user enters 50 and it shows the top 50 records.
View 1 Replies
View Related
Aug 14, 2015
I Want to Create an Query to Find Different Values in A Table/ Query
Like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1
Feb 2012 Ankur School 1 Post 1
Mar 2012 Ankur School 2 Post 1
Apr 2012 Ankur School 2 Post 2
May 2012 Ankur School 2 Post 2
June 2012 Ankur School 3 Post 2
Now i want in result of Query is only like
Month Year Name School Post
Jan 2012 Ankur School 1 Post 1 (Starting Ledger)
Mar 2012 Ankur School 2 Post 1 (Change in School)
Apr 2012 Ankur School 2 Post 2 ( Change in Post)
June 2012 Ankur School 3 Post 2 (Change in School)
View 1 Replies
View Related
Dec 18, 2014
I have created a query that counts the AppSizerPerUD for every User Drive (UserDriveID). Now I want to assign these values to the other query (UserDrive Usage) for every UserDriveID. So I want to create a new column in the UserDriveUsage query that will contain the AppSizePerUD. In this case in the first two rows must be 23.6, then one row 18.38, the third one 45,39 etc.I need something like a VLOOKUP in Excel. I have tried DLOOKUP but probably I am not uisng it correctly.
View 3 Replies
View Related
Aug 2, 2014
I have a table that has a list of tasks and checkboxes attached to them to be checked once the task is completed. I need to run a query on the table that will only bring me back the tasks with a completion that is false.
Everything that I read online indicates that this is a difficult task for access. Maybe I can accomplish this in SQL view instead of design? If I put false is all of the yes/no fields, the query brings back nothing.
View 14 Replies
View Related