Concatenate Field Or Group By In Report
Dec 15, 2012
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]...
View Replies
ADVERTISEMENT
Oct 30, 2014
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling
etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.
View 2 Replies
View Related
Mar 28, 2013
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies
View Related
Apr 27, 2013
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?
View 3 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
Apr 11, 2013
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.
View 5 Replies
View Related
Jun 29, 2007
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.
View 1 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
Jul 8, 2005
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?
View 3 Replies
View Related
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
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
Jul 10, 2014
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
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
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
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
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
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
Nov 1, 2012
I have a group of inspectors who are assigned a group of buildings. I want to show a count of how many each inspector has. The end result should look like this:
Jones FSL 0: 0 FSL 1: 0 FSL 2: 1 FSL 3: 2 FSL 4: 2 Total: 5
Smith FSL 0: 0 FSL 1: 1 FSL 2: 2 FSL 3: 2 FSL 4: 1 Total: 6
White FSL 0: 1 FSL 1: 3 FSl 2: 0 FSL 3: 0 FSl 4: 0 Total: 4
View 14 Replies
View Related
Jan 6, 2005
Hi guys,
I’m creating a report on which I want to group data base on a field that has two different data one is KEY and OTHER. Now I need to be able to show on the detail section all records base on KEY, and group the rest of the records by OTHER.
Can anyone help me please?
Million of thanks
View 2 Replies
View Related
Oct 18, 2012
I have a database, in this database I have a "group and sort" set up on a report that I built from the database. The report is sorted by the "shape" field, then the "weight" field. The weight field is where I am having an issue. I have values in this field that range from .01 to 9. whatever..... nothing has ever gone over 9.*.
We now have a item with a 10.* value and when access sorts the report the "10" weight item is listed at the bottom of the 1's not after the 9's. I need to have access treat the 10 as a larger number than 9 not as the end of the 1's. So right now 10 is right before it goes to 2, I need it to be after 9. Is this possible ?
View 2 Replies
View Related
Sep 3, 2004
I need to write a report with the following groupings
Customer_GroupHeader
State_Groupheader
Client_Groupheader
Detail Section
Client_Groupfooter
State_Groupfooter
Customer_Groupfooter
What I have noticed, I maybe wrong, the record is a available for the "group_header" section closest to the detai section, in my case, "Client_GroupHeader". However, I need information in the record to format goup header information in the "Customer_Groupheader" and "State_Groupheader. Can anyone help me with this.
thank you
newbie and learning
View 1 Replies
View Related
Mar 25, 2005
I've got an empty pageheader, and a customer group header that repeats on every page. the customer group does not have a footer, and I have a standard agreement paragraph and a signature line in the page footer.
I want to apply a page number to either the top (group header) or bottom (page footer) of my page to reflect the PAGE OF PAGES for the group, not the overall report.
The printed report gets separated into pages for each customer and mailed to them - doesn't make sense to have running page numbers on these - I'd like the customer to be able to see that they're looking at page 2 of 4 of what I mail them, instead of page 202 of 412.
How can I accomplish this?
Sidenote: I ran across a few references to the MS Knowledgebase report samples file (RptSmp00.mdb) during my pre-post answer search. I downloaded - I browsed - I learned a few new tricks. Good stuff.
Slaughter
slaughter at missouri dot edu
View 2 Replies
View Related
Oct 28, 2013
I need to build such a report, Unfortunately, I do not know how to use VBA to create each group (grouping by type) had a different header.
The problem becomes bigger that everything must generate queries dynamically cross, the number of columns in a given type may vary depending on the number or the size of the products in which they occur.
View 8 Replies
View Related
Mar 14, 2013
is there an easy way to do a conditional format on a report where the value is 1,2,3,or 4 from an option group. I want the 1=Yes, 2=No, 3=Maybe etc.
View 1 Replies
View Related
Sep 19, 2013
I have a report that has a number of grouping levels.I would like the highest grouping level to determine a new page.
View 2 Replies
View Related