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

Transpose In Access??

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

Access Version Of Transpose?

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

Transpose

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

Transpose Subform?

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

Transpose Column To Row

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

Query Transpose Help

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

Query Help, Transpose?

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

Transpose Rows To Cols

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

Transpose Weekday Columns To Rows

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

Queries :: Transpose Table Columns Into Rows

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

Queries :: Transpose And Concatenate Query Results

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

Queries :: Transpose Vertical Text Values To Horizontal

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

Modules & VBA :: Extract Variable Count Of Columns And Transpose It To Another Table

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

Queries :: Daily Activities - Group As Weekly Total And Transpose For Reporting

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

Modules & VBA :: Copy Data In Sheet1 Using Sheet 2 And Access Table Data?

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

Can't Use Data Access Pages To Enter Data

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

Data Access Pages - Need An Access Client?

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

Imort Data From Access To Access

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

General :: Access Data Export Into Excel As Data Linked To Excel

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

Tables :: Access 2003 Table Data Import Into Access 2007 Table

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

Forms :: How To Copy Ms Access Forms Field Data Into Ms Access Table

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

Tables :: Data Lost In Access 2000 Tables With Access 2010

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

Help With Data Access

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

Data Access Pages

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

Data Access Pages Again..

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







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