Queries :: Convert Multiple Rows Of Data Into 1 Row
Oct 11, 2013
I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing. I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, QtySo I have multiple rows of ItemIDs for all the BeadPartID & Qtys.Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.
In the attached Database (Access 2007), there are 2 tables - "Source" and "Expected_Output"
1: Table "Source" contains values CONCATENATED in a single row with concatenation string ' OR name = ' With limited knowledge in Access, i tried to perform text-column trying to see if some delimiter could be removed, it messed-up the expected output.
In the attached DB, table "Expected_Output" contains the required output.
2. DB should have the ability to: the values that we obtained in the "Expected_Output" should be converted from multiple rows to single row with concatenations as in the "Source" table Once again i tried using the ' OR name = ' in the query for concatenation and failed again.
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 a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT 292020 M 5/11/2012 ($33.95) 292020 11 5/14/2012 $33.95 292020 A 5/30/2012 ($33.95)
I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?
(Sorry if this is a documented question , I've tried searching but couldn't find anything )
I am trying to merge data from multiple rows into one row for each unique ID. I can replicate on how to do this on a table, maybe a query or something like that. Attached img shows
And I'd like to convert it to this: Member_IDDiagnosis - 1Diagnosis - 2Diagnosis - 3Diagnosis - 4 10000 HypothermiaFrost Bite 10001 Fatigue DehydrationExhaustion 99999 Exhaustion Hypothermia
The columns don't line up well but I am looking for each diagnosis to move into one of the 4 columns, depending on whether it is the first, second, third or fourth diagnosis associated with the member.
I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result: PersList T1Component Andrew Med 1 Andrew Med 2 Brett Med 1 Brett Med 3 Brett Med 4 Charles Med 2 Duane Med 1 Duane Med 4
Quy 2 Result Regimen T2Component Goody1 Med 1 Goody1 Med 3 Goody1 Med 4 Goody2 Med 1 Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return Person Regimen Andrew Goody2 (he has med 1, 3, and 4) Brett Goody1 (he has med 1 and 2)
"Bad" would return Person Charles Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
I have a query which is used to pull data from 2 tables.
1. Only show the records that have 2 or more same Pat#, FName and LName. If it is a single record I would like to hide it.
2. Need to see the different dates amd know the difference.
Currently Query Looks like this.
Pat# FName LName Reason Status Date 00001 John Doe 1 1 11/13/2014 00002 Sally Doe 2 1 11/25/2014 00003 Bill Bates 1 1 11/04/2014 00003 Bill Bates 2 1 11/07/2014 00004 Jenny West 1 1 04/03/2014 00004 Jenny West 2 1 04/10/2014
The Signup reason number 1 represents the signup date and number 2 represents the time they left. SignupStatus number 1 represents a group.
So I am hoping my end goal it would look something like this.
Pat# FName LName Date 1 Date 2 Difference 00003 Bill Bates 11/04/2014 11/07/2014 3 Days 00004 Jenny West 04/03/2014 04/10/2014 7 days
I am trying to avoid using VBA or SQL. I do not know where to input it.
I tried to use the “Find Duplicates” query wizard but this did not work because it finds duplicates like last name first name, but it does not separate the dates.
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3 1 x 1 m 1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3 1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.
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
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start 1st Obs End 2nd Obs Start 2nd Obs End 3rd Obs Start 3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
Hello, I'm helping someone can help me with a little problem I am having with a peculiar data arrangement in Access. I have the following scenario. My table is set up with the following columns:
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet, A1: Smith, John A2: 111 Pine St. A3: San Diego, CA A4: (555) 128-549 A5: Jones, Sue A6: 222 Oak Ln. A7: New York, NY A8: (555) 238-1845 A9: Anderson, Tom A10: 333 Cherry Ave. A11: Chicago, IL A12: (555) 581-4914
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows: Smith, John 111 Pine St. San Diego, CA (555) 128-549 Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845 Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But, My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type Candidate short text Unit short text EV1 Date Date EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date TH1 10 07/05/2015 25/05/15 TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Date' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query: SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate FROM [Candidate AC Dates] GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
I'm trying to make a sub form that displays the hours of an employee selected in a listbox. I've got most of it working but having a bit of an issue.
The info for thre query is in 3 tables: tblStaff (name etc) tblShifts (start and end times for days that this employee works) tblDays (a list of days names so I can use numbers elsewhere)
My query looks like this:
Code: SELECT tblDays.dayName, IIf(Nz([startTime],"")="","NWD",[startTime]) AS start, IIf(Nz([endTime],"")="","NWD",[endTime]) AS [end], tblStaff.staffName FROM tblDays LEFT JOIN (tblStaff RIGHT JOIN tblShifts ON tblStaff.staffPK = tblShifts.staffFK) ON tblDays.dayPK = tblShifts.workingDay WHERE (((tblStaff.staffName)=[Forms]![frmMain]![lst_myTeam] Or (tblStaff.staffName) Is Null));
This worked fine with a single user and some test data - it correctly displayed all days of the week, with start/end times on Mon and Tues where I had entered shift information, and "NWD" against all other days.
However, when I add a couple more employees to the mix it shows the correct info for the first employee, but anybody else it will only display days where person 1 doesn't have any hours. I haven't entered any hours for the new employees, but the query should still display Mon-Sun with NWD in every column. It shows Wed-Sun but Mon and Tues are missing.
I've tried different join types but they all come back with "ambiguous joins" error when I try to run.
