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

Reports :: Training Database - Group Report By Field List Row Source

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

Reports :: Control Group Expression For Group In Report?

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

Reports :: Concatenate Fields In Access Report

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

Modules & VBA :: Concatenate Data In One Cell In A Report / Query

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

Forms :: Concatenate Entries From A Single Field Based On Another Field

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

Concatenate Into A List In A Single Field

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

Concatenate Multiple Records Into Memo Field

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

Combo Box-concatenate Field And Hide Column

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

Query Field To Concatenate Fields With Nil Values

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

Modules & VBA :: Concatenate Multiple Rows Into One Field

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

Reports :: Concatenate Multiple Rows Into 1 Field

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

Tables :: Concatenate Field Names But For Each Record Order Can Be Different

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

Tables :: Survey File - Concatenate 2-3 Field Values In Table

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

General :: Concatenate Two Text Fields Into A Memo Field In Query?

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

Queries :: Concatenate Related Records Into One Expression / Field In A Form

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

Modules & VBA :: Concatenate Data From Multiple Rows Into Single Row / Field

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

Queries :: Concatenate Field Data Into One Cell In Query According To Linked Table ID?

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

Group Counting In A Report?

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

Group Data In A Dynamic Report

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

Group And Sort Configuration On A Report

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

Availability Of Records In Report Group Headers???

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

Reset Page Numbers For Each Report Group

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

Reports :: How To Use VBA To Generate Report With Different Headers In Group

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

Reports :: Option Group Value To Text On Report

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

Reports :: Split Report Into Pages By A Group

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







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