Rows to columns;
I have a table with three fields
ID,Desc,Code
1,aaa,code1
2,bbb,code2
3. aaa,code3
4, bbb,code4
I need a query creating these two columns:
Desc,Code
aaa,code1;code3
bbb,code2;code4
how can I do this?
I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.
I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.
Is there a way to create a query that will transpose table columns into rows:
Genes Cytoband
xxxx yyyyyy
xxx yyyyyy
xxx yyyyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyyy
xxxxxxx yyyyy
xxx yyyyyy
xxxx yyyyy
xxxxxxx yyyyyyy
xxxx yyyy
xxxxxx yyyyyy
xxxx yyyyyy
xxxxxxx yyyyyy
Is there a way to display the table in a single row separated by commas:
xxxx yyyyy, xxxx yyyyyyy, xxxxxxx yyyyyyyy, xxxxx yyyyyyy
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
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.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
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 affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
I am using Access 2007. I have an ODBC source feeding data into a table, to which I made a crosstab query that displays the useful data from that source. I need a way to add data(new field), using a form, that is a associated with the lines in the query. I've tried creating another table that uses the sample ID as the primary key, but I need it to automatically create a new row for each query line. Is there a way to do this, or better way to add data to a query. Examples are below.
The ODBC data looks something like this
SampleID PeakName Conc
sample1 THF 50
sample1 MEOH 10
sample1 propanol 25
sample1 butanol 15
sample2 THF 21
sample2 MEOH 15
sample2 propanol 17
sample2 butanol 23
I have the query look like:
SampleID THF MEOH propanol butanol (I need to be able to add a field here)
sample1 50 10 25 15
sample2 21 15 17 23
Linked table I created:
SampleID LIMS #
sample1 1234567
sample2 4567891
The problem is, for this table I have to manually select each sample ID. I want it to automatically add the sample IDs as new ones are querried.
i'm using Access 2010 and i created a main form , from which i want to let users to open new table with fixed fileds and rows, also the name of the table will be as per user choice. so if a user want to open new table from the main menu, he will have the choice to press on a command button and a dialoge box will ask to name the table. the new table will then be open with the same fileds and rows names( name only) . the new table will be edited using a form.
View 3 Replies View RelatedOK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
What is the correct way to save the values in a listbox column to the fields of a table? I can use rec("field1") = Me.list16.ItemData(Varitem)and that records the bound column to field 1 but how can I save the unbound columns to other fields in the table?
View 4 Replies View RelatedHi all,
Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc
transform it to
701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....
Thanks in advance
I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.
I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.
I have three columns that I need repeated over and over to reflect their work history.
this is what im working with
Name or ID will be the key
Job start date
Job title
department
currently every one of theese fields is in a column but every entry has its own row.
I need a single row for every employee and the columns to repeat to the right to reflect every move.
Can someone help me :D I'm stuck :confused:
I doesnt matter if the output is in something other than access
I have a two tables in my database related to employee ratings
Employee
TokenID
Division
Band
PMRating
TokenID
H1
Now this statement
Code:SELECT DISTINCT Employee.Band FROM Employee
This would display me all the band levels within Employee table in each row..
Is there any way I could built a query to the Band names as seperate columns?
i'm doing this so as to display the average H1 rating for each Band level ..that is grouped by Employee.Division..
Hello All,
I have created a table in access with the following column headings:
ID, A, B, C, D, E.
Column ID contains 9 digits text;
Column A column contains email address;
---------------------------------
Column B contains Currency ($); |
Column C contains Currency ($); | Source Fields
Column D contains Currency ($); |
---------------------------------
Column E contains order number;
Now I need to normalize this table to produce a new table with this structure - UNION QUERY is ruled b/c i have more than the three source fields:
ID, Source, and AMT
Where -
ID: would equal to ID;
Source: would be the value of the heading such as B or C or D;
AMT: would be the currency value of each corresponding column.
Help with the Module or any other way to do this will be greatly appericated.
Thank you.
-testcode
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:
AgentID Skill_1 Skill_2 Skill_3 Skill_4
100 51 52 53 54
I want to create a new table that arranges the data like this:
AgentID Skill
100 51
100 52
100 53
100 54
Does anyone have any suggestions how I can do this?
I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:
Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000
There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:
Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget
Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?
I have extraction report from calculation program and dont have access to tables. When I import it to MS Access it looks like attached Sheet1. Is there a simple SQL query or VBA code which will allow me to import the data to new Sheet2 table? Fields 1,7&13 will always be the same and I want them to be column names, Fields 4, 10 &14 contains data which will be different each time I delete the old calculation from Sheet1 and import a new one. I would like to be done in Access as I have to import few hundred calculations and store it in Sheet2 table.
View 2 Replies View RelatedHi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.
What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.
It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...
Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!
The fields:
Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:
thank you thank you thank you thank you thank you !!
Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.
Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add
Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By
Field: Pending
Table: qryStatusRptA
Total: Sum
Field: Overdue
Table: queryStatusRptA
Total: Sum
Field: TotalRecords: Count(*)
Table:
Total: Expression
Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)
Many thanks,
Christine
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
I have a table with 8 columns titled - Destination, Mon, Tues, Wed …to.. Sun. This is shown as "OriginalTable" worksheet in the attached example workbook. I want to create a new table from this original table that looks like "NewlTable" worksheet in the attached example. So I will have a new table that has 3 columns titled - Destination, Day, Weight. Weekdays will become rows so each destination will have 7 rows, one for each day of the week. The measure will be listed against each day of the week. Can someone please look at the attached example for me and help me with this.
Many thanks in advance.
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
2. Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
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 really appreciate it. Thanks.
Hi all,
I have a query which runs off a table. I have the following Fields as columns in query: WeekID, A, B, C, D, E
For each week, the letters correlate points given. So for week 1, "A" could have 1 point, "B" could have 3 points, etc.
I want to run a Query that will show A, B, C, D, E as rows like the following:
Letter_Week 1_____Week 2_____Week 3
A_______1_________5___________3___
B_______3_________4___________2___
C_______2_________1___________7___
D_______6_________3___________3___
D_______3_________1___________4___
I can't run a crosstab because it will only crosstab values within all of A, B, etc.
Any idea guys? Thanks in advance, as always.
Caliboi
I have a file structured as listed below. I need to pivot Patient Id, Patient Name, Send Reason, Provider Name and Visit Number into columns and then insert the values into the corresponding columns.
My data set contains about 1,000 rows, so I cannot simply paste special in Excel.
Patient ID 111111
Patient Name Doe, Jane
Send Reason Cannot resolve provider
Provider Name Doe, John
Visit Number ABCD#F1234567
I have tried Pivot and Transform, neither of which seems to work.