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.
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.
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] ....
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 been struggling with combining multiple rows into one row for an order summary report.
Specifically, I have three tables (Products, Orders, and OrderDetails)
Products
=======
ProductID = AutoNumber
ProductName = String
Orders
======
OrderID = AutoNumber
OrderDate = Date/Time
OrderInfo = Memo
CustomerName = Text
CustomerAddress = Text
PaymentDetails = Text
OrderProductDetails
=========
OrderNumber = Number (Linked to OrderID)
ProductNumber = Number (Linked to ProductID)
Notes = Memo (Notes on product customization)
The OrderProductDetails table is in many to one relationships with both Orders and Products table. If I place an order with 3 products, the OrderProductDetails datasheet would be:
OrderNumber ProductNumber
========================
1 78 (Product 1)
1 89 (Product 2)
1 56 (Product 3)
On my report, instead of 3 entries for this order, I would like show just one entry under a column "Products Ordered" ---> Product 1, Product 2, Product 3.
I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.
I am using Access 2010.
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
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 RelatedI have a query that returns numerous rows of data per person per day.
Simplified Example ( ';' used to show columns):
LastName ; Date ; DrillNumber ; Score
Smith ; 18/9/14 ; 0 ; 100
Smith ; 18/9/14 ; 1 ; 150
Smith ; 18/9/14 ; 2 ; 120
Doe ; 18/9/14 ; 0 ; 150
Doe ; 18/9/14 ; 1 ; 150
Doe ; 18/9/14 ; 2 ; 100
etc.
I'm trying to create a report that shows each of the scores per drill in the report per person (Dont worry about the formatting):
Name: Smith
Date: 18/9/14
Drill & Score:
0 - 100
1 - 150
2 - 120
However, when I drag the field lists into the report Im getting a new report entry for each drill eg.
Smith
18/9/14
0 - 100
Smith
18/9/14
1 - 150
etc.
Any way to get all drills to show in the one report entry per person? So all data for Smith is in the one report or report section, and then all the data for Doe.
Hello,
I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.
This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.
I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.
**Account Number and Sub ID fields in both tables are not unquie.
Data Table: (this table has alot more data but this gives you the idea.)
Account_NUM - Sub_ID - Data1
1234 | 3 | 123 N Inc.
1234 | 3 | 666 DRN
4567 | 4 | 543 S Way.
7890 | 5 | zzz ABC
Notes Table: (Yes the notes field is a MEMO field)
Account_Num - Sub_ID - Notes
1234 | 3 | notes1
1234 | 3 | notes2
1234 | 3 | notes3
The output I am trying to Get:
Account_Num - Sub_ID - Data1 - Notes
1234 | 3 | 123 N Inc. | notes1 notes2 notes3
1234 | 3 | 666 DRN | notes1 notes2 notes3
4567 | 4 | 543 S Way. | NULL
7890 | 5 | zzz ABC | NULL
Thank you for any advice or thoughts.
Joe
Hello All,
I am trying to figure out the best was to combine fields from multiple rows into one row & field.
Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1
What I want is one row and the 3 footnotes combines into one field:
Product........................................... .......Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1
Any help would be greatly appreciated.
I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.
I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).
The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color.
i.e.
3 = green
2 = yellow
1 = red
I have four forms named [Information Form], [Evidence Form], [Subject Form], and [Vehicle Form]. The Final Form is a Tabbed Form named [New Incident]. The tabbed form has four tabs, with each tab displaying one of the forms above. I also have a Print Command Button named [Print All] underneath the tabbed portion of the [New Incident] form.
Also, All of the four forms are set to data entry - yes, so that on open they will be clean and read for new record entry. I need the [Print All] button to do, just that, print everything that has or has not just been entered into the four forms, even if some are printed blank because they were not necessary for this particular report. One thin of note is that each form on the tab has a field for the "Incident Number" which will be the same for all four forms.
I have two address fields that I want to tack together due to space contraints.
=([add1] & " " & [add2])
Seems to work in a query but not report?
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?
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 have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
I have a datase that has the following fields.
Name
Comment1
Comment2
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.
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.
Code:
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;
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.
e.g.
ITEM
LOCATION
DATE
GORP
EAST
1/1/2012
GORP
EAST
2/1/2012
[code]...
Hi,
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.
Is this possible?
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
In access database we register the following fields:
Company name - Address - Artickle - Number of copies
We then want access to print a report with these informations, except for the number of copies, which should be the number of duplicates that is to be printed.
I want to count based on 1 (same) field from each of two tables, based on if ID is in "X".
select a.groupid, count(a.id), count(b.id)
from ta a, tb b
where value in ('a','b','c')
group by a.groupid
Thats sort of the psuedocode but Im not getting the right results.
Any help would be appreciated.
I have
Field1
Field2
Field3
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
[Field1] & " " & [Field3] & " " & [Field2]
In a form or a report
Field4 should show as Values of those fields
Monkey Dog Cat
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 RelatedI'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