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
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.
I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.
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
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?
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
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.
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.
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'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?
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.
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)
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.
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.
Not sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
Hi Im trying to update a row this is the SQL im using
UPDATE HouseEntry SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1) WHERE EntryNo=40;
When I run it I get an error saying "Operation Must Be an Updatable Query"
If I physically put it in the date e.g SET [Date]=#09/15/2005# It works so there must be a problem with my Select Statement. Any ideas??? Cheers Bikeboardsurf
The following is for a successful pet grooming business.
I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits. Thanks, Jed
I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?
I am making a roster for 25 people. In my form, the start of the roster is 0700hours until 1900 hours (increments of 15 minutes each) so I have 48 check boxes, which are ticked when one is rostered on for that time.
I am getting crossed eyed looking across the screen at these 48 tick boxes each for Monday through to Friday.
I have searched high and low and cannot find anything on how to have 24 check boxes (7am to 1pm) in one row and the other 2 dozen (for the next 6 hours until 7pm) on the next row, to be less painful on the eyes.
Can this be done?
I would be grateful for any ideas on how to start.
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
