Combining Fields From Multiple Rows Into One Row & Field
Jun 5, 2007
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:
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:
V.I. Capital Appreciation Fund.................3, 5 ,1
I have three tables, with the following data (fields separated by "-" here): Unit - Customer Number - Customer Name - Type of A/R - Total A/R Unit - Customer Number - Customer Name - Type of A/R - Total Overdue Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names: Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this: Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled English 1A........6 English 1B........12 English 1C........20 English 1D........25 Reading 1.........4 Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith English 1A.......English 1B Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled English 1A/English 1B........18 English 1C.......................20 English 1D.......................25 Reading 1/Reading 2..........19
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.
In the attached db example on the case form there is a section for technicians to go in and take credit for steps that they performed as part of the overall case
So clv1 might be done by User A Then clv2 might be done by User B
But the next case it might be switched.
I need a method getting the sum of the total clv's field for each technician in two different ways 1 would be the total clvs for USer A for the current month, 2nd would be the total for the year- or actually a prompt for a date range
Tried using the query wizard but it doesnt combine the names Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.
Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?
Well the db example is imb so it cannot be attached
Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):
UserA - Hours - Date - ProjectA UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectB UserA - Hours - Date - ProjectB UserB - Hours - Date - ProjectA UserA - Hours - Date - ProjectA
What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.
I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.
I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.
Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?
Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
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
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Hello, I have a big table like this: ID Name Race1 Race2 Race3 -- ---- ----- ----- ----- 1 a Asian russian
How do I create a query to be like this (either in query or report)? I like to combine three fields (race1, race2, race3) in one field (Race), and break any value of races to the 2nd line, 3rd line, if there is a value... ID Name Race -- --- ---- 1 a Asian Russian
In Oracle and SQL Server, we can combine a few rows to be columns-like, but in Access, is there any way to do that? and how?
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
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I have an issue I can't quite get my head around. I have a table with four coloums that list Emplyees by Number. I need to pull thase number and combine them into one colomn through a querry (I think). I don't need to join the coloums in the same record. Example...
Now... leader Pilot Admin Facilitator 11800 11801 11802 11803
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
Hay Folks, I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form. The problem. I cant get the expression right to show real values instead of key-values. Here's the sample expression for the control-field: =[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]
Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]... SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;