Transpose Data In MS Access?
Feb 21, 2012
My task is to allocate Tasks to People for any/every day of a single week. There are up to 100 different tasks and about 1000 people to choose from but a typical allocation would involve perhaps 200 people. I have successfully used a form that presents a persons Name at the top of the form, and shows, for each Day: whether they are available, a combo box to select Tasks from a predefined list and a Time when they should start the Task. A report easily prints this by Name, without any transposition, and it is great for each person to see Task they have to do on which day and the time they should turn up. However, when it comes to supervision on each day, the manager wants to see a layout with column headings Task, Time1, Day1, Time2, Day2, . . . Time7,Day7 with a list of Tasks with assiciated Names for each of the days. One of the threads presents a solution that uses Dlookup to fetch the data but I believe that worked because the size of the data was determinate.
I have had to create a cludge using a subform/table to store the data as Task, Day1Name, Day1Time etc, using some VB routines, but the user has to take great care, for instance when a person is allocated a different task, the user has first to select a different line on this second table. It is even worse when the allotted task has to change or is deleted. The info is bound to be inconsistent.To sum up, there are only 7 days, not a perpetual calendar, but tasks and people are many and varied.
View Replies
ADVERTISEMENT
Aug 9, 2007
Hey,
This is not really a transpose, i think.
I don't have much practical experience with Access database so please help with the simplest solution..
I have a table with some data and I want to convert (or create a new one) table into a different format.
my table has the following format...
serial_n 100 500 1000
95x45 1 2 0.9
96X15 3 0.1 0.5
change to this format
serial_no (these two columns will have new headings)
95x45 100 1
95x45 500 2
95x45 1000 0.9
96x15 100 3
96x15 500 0.1
96x15 1000 0.5
I hope, I have explained it well...
Sonia
View 7 Replies
View Related
Feb 28, 2006
Hi,
I've got two tables in Access. The first contains 300 column headers running from top to bottom, i.e:
Title
First Name
Surname
..
..
The second table contains the corresponding data entries for 1000 customers running from left to right, i.e:
MR Trevor Smith ....
MRS Mary Jones ....
Ideally i'd like to append table 2 to the bottom ot table 1 so the correct data field is under the right column header but I can't do this. Is there a transpose style funtion in access which will mean I can convert table 1 to run from left to right instead of top to bottom?
Thanks
J7
View 1 Replies
View Related
Nov 12, 2007
I have a table with one field and 83 records. I need to transpose this one column to 83 columns, so I would have a table with 83 columns and one row…. Is there any way to do this?
Appreciate
Maryam
View 1 Replies
View Related
Apr 14, 2006
Hi - Question number two..
Is it possible to transpose the subform data set. I have a subform which lists alleles. There are a variable number of alleles in the subform, which is linked to a second subform. Each allele has a size and a frequency.
It is listed in a tabular format, but the default is top to bottom layout. Is there anyway to display this left to right?
Cheers,
View 2 Replies
View Related
Nov 12, 2007
I have a table with one Field and 83 records. I need to transpose this one column to 83 columns, so I would have a table with 83 columns and one row…. Is there any way to do this?
Appreciate
Maryam
View 4 Replies
View Related
Apr 7, 2006
Hi,
I've attached a small JPEG file showing some query results and some data transposing I'm trying to work inside of Access. The top box shows the how my query results currently come up. The lower box shows how I would like to transpose this data so it can easily be exported to other things. I can handle the export part, but I am having trouble with getting the results to fit into my desired format while still in Access. I don't want to convert it outside of Access b/c that would add another step of things to do.
Any ideas would be appreaciated..
Thanks
View 2 Replies
View Related
Jul 10, 2006
Not sure if transpose is the right term but what I want to do it the following:
I have the following tables (in bold) and members
horse
id
trainer
..
..
prev_form
id
date
horse_id
position
..
..
race
id
date
...
..
race_starter
race_id
horse_id
what I am trying to do is get a query that for each race_starter in a race will display in one row:
race_id,horse_id,date,horse.trainer...,prev_form.i d[0],prev_form.position[0]....,prev_form.id[1],prev_form.position[1]....,.......prev_form.id[4],prev_form.position[4]...
So a horse has many prev_form entries. I want to display the last 5 before the race date but need it to be in one row with the rest of the data. Above [0] would be most recent and [4] would be 5th most recent.
Any clues on how this could be done.
Thanks in advance,
blocka.
View 1 Replies
View Related
Apr 15, 2008
Hi there
Sorry if this a stupid question that has beeen asked loads of times but all the threads of seem always seem to be trying to do the opposite of what i need to do. Im not sure transpose is the right word
I have a table with four rows
Color
------
red
orange
green
blue
and i would like to transform the data as the following in a query
color1 color2 color3 color4
----- ---- ----- ------
red orange green blue
Is this possible with a query?
Thanks
lpj
View 3 Replies
View Related
Dec 18, 2007
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.
View 2 Replies
View Related
Jun 18, 2013
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
View 11 Replies
View Related
Jul 24, 2014
I am trying to generate a simple report that can be imported to excel for analysis. Currently the database documents failure causes associated with a repair. I need a "Report" that does not duplicate the failed items in the event there is more than one cause associated with the failure. some repaired items may have up to 3 causes of failure. What I would like to do is have the query show one record instead of three records (for the cases were three causes of failure were identified for a repair)
Current query format
Code:
facilityID Failure Cause
1 Electrical Surge
1 Mechanical Wear
1 Corrosion
2 ......
2 ......
.... ......
Desired query format
Code:
FacilityID Failure Cause(s)
1 Electrical surge, Mechanical wear, Corrosion
2 .....
.... .....
I was reading about a SQL transpose call but I do not need all fields transposed (only the failure causes transposed and concatenated into one field).
Test database attached with a query that shows the data I want to transform into the desired format.
View 6 Replies
View Related
May 25, 2013
I have a Volunteer Database that has teams. There is only one team leader but some of the teams have two or three assistants. Currently I have a query that lists all the assistants for each team vertically.What I want to do is list the Assistants horizontally under headings like Assistant 1; Assistant 2; and Assistant 3 for each team on one line.
View 6 Replies
View Related
Mar 14, 2014
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
View 1 Replies
View Related
Jun 5, 2013
Query of daily activities spent hours
1) to be group as weekly total
2) then need to transpose it for reporting.
My table fields are Date, SpentHrs and Code (activity code description).
View 11 Replies
View Related
Aug 5, 2014
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected
View 14 Replies
View Related
Apr 14, 2012
I am trying to set up some data access pages as data entry into a table with access 2000. I can see the records in both the data access forms and the HTML forms, but cannot create new records into the table. I have tried to change the property to DataEntry etc... but nothing seems to work.
View 2 Replies
View Related
Oct 10, 2005
I've made an adp (access project ) file. I want to permit some users to look in the data but i'm not certain they have access installed, can i give them read-only rights by placing a data-access page in a network map? Can they use the functionality of a data-access page without having access installed?
View 1 Replies
View Related
Mar 13, 2008
i have identical access databases with different data .I d like to unify the to one .How can i import only DATA from one to other
View 3 Replies
View Related
Oct 21, 2012
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
View 5 Replies
View Related
Apr 26, 2015
i have a database in access 2003 when i open it with access 2003 it shows data in table but when i open same table in access 2007 it shows only header rows , no data
how can i see this data into access 2007 or excel 2007.i want to link these table data with excel 2007 or access 2007 but with above problem i can't do it
View 1 Replies
View Related
Dec 29, 2014
I have a table and a form. I need to input data in the form and click on a button to add this data into the existing table.
This is part of a bigger application which will be compiled and given away for use.
tblProduct
id = autonumber
name = text
InHand = integer
Price = float
frmNew
txtname = textbox
txtInhand = textbox
txtPrice = textbox.
I need to coy frmNew.txtname into tblProduct.name and so forth.
View 9 Replies
View Related
Oct 9, 2012
We have an Access 2000 backend database resides in a network server drive while users connect to the backend tables with a mde file on their computers. All users have Access 2010 and the mde file was converted from the 2000 frontend with Access 2010.
The database have been running for a few weeks but recently users have been complaining about record lost on the backend table.We have also experienced one incident of data corruption where the main table could not be opened. After Compact & Repair, the table could be opened but a few records were showing xxxxx on all the fields and we have to delete and re-enter these records.Would they relate to using 2010 mde converted from 2000 frontend when the backend is still in 2000? I am a little nervous about converting both the frontend and backend to 2010 since I have heard various issues on the new version.
View 1 Replies
View Related
Nov 2, 2006
Hi
Pretty new to access so hope these aren't daft questions!
I've got a table of documents, linked to 2 different tables which relate whether the document is a trial document (yes/no) and what cancer site it refers to (a list of 10 options). I need to be able to give users via a form the initial option of choosing to list a set of documents based on whether they are in a trial or not and also filtered by cancer site.
I've got stuck with trying to do this. I've been able to provide a form with a subtable that shows a list of documents by cancer type (by using a combo box), but I don't know how to further filter the list so that users can also filter by trial status.
Also another silly question - Within my table design for the documents, i use the old trick of setting up cancerid field to show the actual cancer name, not the number. However when I try this for trialid, which is a tickbox, it displays -1 or 0 not a yes/no option.
any help would be great. Thanks Jon
View 2 Replies
View Related
May 13, 2005
Hello Experts..
I am new to Access. I have Access 2003.
When I try to create a data access page using Wizard, I am getting the following message.
'The wizard is unable to create your data access page'
When I click on Database and select Refresh, I get the other msg
"Microsoft office access is unable to connect to the data source specified in the connection string of this page"..
I am very badly stuck here..Please help.
Thanks in advance...
Arun.
View 1 Replies
View Related
May 13, 2005
I am getting very frustrated(and surprised) to see there was no answers to my question regarding Data Access Pages yet. I searched the web and manuals and help in Access, but no luck yet...
Can anyone tell me if Data Access Pages really works? I just came to know about it while developing MS Access application...I just thot it will be cool to go web with less effort...
Thanks very much...
------------------------------------------------------------------------
Hello Experts..
I am new to Access. I have Access 2003.
When I try to create a data access page using Wizard, I am getting the following message.
'The wizard is unable to create your data access page'
When I click on Database and select Refresh, I get the other msg
"Microsoft office access is unable to connect to the data source specified in the connection string of this page"..
I am very badly stuck here..Please help.
Thanks in advance...
Arun.
View 7 Replies
View Related