Rows to columns
Two Remove those rows in a table which has already occured for a particular value
I have one table which consits of 5 columns . I have imported it in access database.
I have two columns say column A and D which consists of integer values and the values of the columns are say in the range 1 - 100 ( both the columns ). so there is a row say in column A value is 38 and in column D corresponding value is 37.
So now in the output there should not be any other record with having value 38 and 37 either in column A or D .ie. all those rows which contains these values other than this rows should be omitted.
Similarly for other rows and values also .I tried with the following query but didn't worked
System Requirements for Complex Large Queries
If I have 13 sheets linked from excel containing about 15,000-20,000 rows and 15ish columns. A 3 have about 35,000 rows, 1 has 85,00 rows and 12 columns, 10 columsn and 2 have 400,000 (2-3 columns) then I create multiple quries then from those quries make more quiries.
Can a 2.8ghz, i7 (not sandy) 4gb system handle this OR would adding more RAM help?
It's going really slow right now making the 1st query from the table with about 85,000 rows and 12 columns. Can't wait to combine that with the 20,000 row database with 15ish columns.
Can I perform math on columns in Access?
I bet this has been asked b4 >
how can I perform basic math on Access db columns? I need to multiply a constant by the present column value ($).
the kicker is also only doing partial rows, I.e. some rows X .96, some rows x .99, etc.
if you could hi-lite rows/columns, then do math would be the ticket!
Between: how on earth could MS build access without this basic function? they want you to buy Excel and import/export!
Colouring Rows & Columns
Does anybody know if you can colour rows or columns in Access?
I know you can colour every-other row, but this isn't what I need to do. I need to be able to colour certain rows or columns
I have a query with six columns. The first column is item description and the next five columns are numeric fields. In some rows, certain numeric fields have zero and the other columns have some integers. In some rows all the five columns have have zeros.
Is it possible to hide the rows only where all the five numeric field collumns have zeros
Moving and adding rows of data
I have a form with a dozen rows of data in the center of the form. Each row has seven columns: a label and four to six text boxes that display data from a table. I want to insert new rows with similar data at the top of these columns, moving the existing rows down.
How do I insert the desired rows without moving EVERYTHING on the formdown?
Same contents in all rows
I am very new to Access and I tried to make a project database based on one of built-in templates. Then I entered design view to add new columns, but the new columns wont work properly. When I enter data in my new column, it automatically fills all rows in that column with the same data. The other columns from the template works fine.
How to get Top 25 rows by each group
I have a table in Access 2007 with almost 200,000 rows with multiple columns for which I need to find the top 25 rows for each group.
The important columns are STOREID (store ID), CUSTID (Customer ID), and QTY (Order amount). I want to select 25 rows per store that reflect the top 25 customers in descending order by their order amount values.
SELECT TOP 25. only gives me the top 25 rows regardless of store so I'm missing something.
Access 2010: How To Insert Rows / Columns In Query Design
Access 2010 offers almost every control on the ribbon which you might need to use while setting up query in query design mode. Many a time while writing conditions for setting up query in a required way, new rows and columns are to be added. This post covers how to add new rows and columns in a query design.
Merging multiple rows into one row with several columns
In a long-term project to update a legacy system we need to create a duplicate output file (output) of the old system, using values made by the new system (input).
This means the following;
1. Several rows from output have to be combined into one row with several added columns in input.
2. Criteria for 'merging' rows are based on the values of several columns in input, if these all match the row can be merged.
3. When there are over 4 identical rows in input for one merge, the data from any of these rows is fine. Result in output is limited to 3 extra columns.
4. A final row in output needs to display the true amount of rows in input.
I've added a simplified and shortened example of the two files in question as an attachment to this posting.
My first instinct, based solely on experience with excel macro's; 1. Concatenate the rows that need to be identical for the merger. Save in temporary row.
2. Create a new table. For each row check if the value in the temporary row has been used before, then write the value in the proper column and update the counter.
How to change only some rows in table ?
I have a accdb database . It's really only one table with several columns and several thousand rows.
I need to make a single change to the database.
I want to multiply by 1,73 the values in two columns called "Amp" and "Ixx" but only in those rows where the value of the third column "Napon" is "660". In the other rows the values must remain the same.
Converting multiple columns into one row
I need help on converting multiple columns into one row (actually two rows).
I have a Access table with 26 columns UnitID, Date, 1, 2, 3, 4,.,24.
(data in column "1" means KWh used in hour 1, and samething appplies to each hour until hour 24)
I want to convert this table to four columns (rows expands to 24 times larger since columns of hours changes to rows)
UnitID, Date, Hour, KWh_used
Table before converting is below
UnitID date 1 2 3 . 24
and, a table structure I want to see after conversion is below
unitID Date Hour KWh
Could you please help me using Visual Basic (in Access 2007) to convert multiple columns of KWh used in each hour into two columns, Hour and KWh used
HELP! Table SQL
I have one table with 5 columns (one is empty) and 12 rows, I have another table with 4 columns and 12 rows. I want to update the first table, the empty column, with the 3 columns from the second table.
Both tables have a record that a different Serial by row.
Combining some colums in an Access Table
I have a table in Access with 184,000 rows or records of data and 20 columns or field names. When looking at the table ten of the columns represent the identifying information for the data stored in the other ten columns.
I need to convert the last ten columns of data into one column with a second column added to identify the original field name. Then end result would be onecurrent record of data being converted into ten records with the original 10 columns of identifying data, a new column containing the original field name and a column for the data value from the original column identified in the new column.
I have been able to do this in Excel using a consolidation pivot table, un-checking the rows and columns then clicking on the final sum value to get a detailed list of the records. Unfortunately there still appears to be a cap because I get a message saying some of the data has been dropped after a little over a million rows.
I am using the 2010 version of Access and Excel.
This is the smaller table. I have several others that have up to 200 columns that need to be converted the same way.
Access Report Design Requirement
I Need a help regarding the design of report in Access 2007.
I need to make a report , with Specified ROWS & COLUMNS filling A4 size page , whether there is no data in all the fileds.
I need to print the report in that format because it is the approved format from my client.
I know if there is sufficient data is there to fill the page, the ROWS & COLUMNS will come. But if I have less datas only to print, I need the page filled with the rows and columns blank.
If it is possible to design the report like this Kindly advise me
Rearrange Table Data
I thought I was in for an easy task today - but I'm hitting that dreaded stone wall again.
I have a table of data that has been imported from Excel. The data is formatted in the table as such:
P A R T
I / PRICES
As it sits, there are 53 columns by 151 rows
I need this to "pivot" into the following columns:
Part Weight Price
So, three columns, approximately 7200 rows.
Is there any easy way to reorganize my data like that? Every way I have tried has faild
Handling a vast matrix of numbers
I have five columns of data extending to 60,000 rows. The first column indicates a high-order category, eg a State of the Union like NY.
The second column indicates townships, e.g. NYC, Albany, Ithaca etc The third column indicates municipalities eg Manhattan, Queens etc The fourth and fifth columns contain raw data like name and number (the number of entries differs randomly for each municipality).
I am at a loss as to how to handle this best. Specifically, should try to transpose the 60,000 rows into making them columns? Should I create a Pivot Table, and if so how?
Showing Columns & Rows in Crosstab query
I have the following code for one of my Crosstab queries. My question relates to the 'PIVOT' part of the code. Am I able to include another PIVOT argument that will show all the rows? Do I add 'AND to the argument? I need to show all rows and columns
Two table formats merged into one access table
I wanted to see if it would be possible to have two table formats merged into one access table. I currently have two table formats that I want to merge and export into excel for reporting purposes instead of copying and pasting.
(The first table has 4 columns and 4 rows and the second table has 6 columns and rows ranging from 1 to 10 rows).
Currently, both tables are separate and have different formats but I'm looking for a way where I can use SQL (or any other method) to grab information from both tables and load them into this table.
Creating rows,columns and formatting name
I am currently working on an automated letter and have in the most completed the automation. I have only two more issues to resolve and it can go live - I've searched the web and got no joy.
It's a two parter! The first query relates to inserting a table and fields. I'd like my letter to automate a table; so 1 row and 5 columns, with the rows and columns adjusting to fit the information contained in each field. I've tried
WrdApp.Selection.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= 5