Topics Search

Access 2010: How To Insert Rows / Columns In Query Design

Access 2010: How To Insert Rows / Columns In Query Design
Views: 287
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.
Sponsored Links:

Similar posts...

 

Moving and adding rows of data

Moving and adding rows of data Icon
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?
 

Two Remove those rows in a table which has already occured for a particular value

Two Remove those rows in a table which has already occured for a particular value Icon
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
 

Column display question

Column display question Icon
In Access 2010, I am using a simple query to display several columns, but the order of the columns is not the same as I see in Design Mode. My helper was dragging and dropping columns around in design mode and I am wondering if that caused the problem.

If so, is there some place that will tell me the order of thecolumns when I run the query?
 

Can I perform math on columns in Access?

Can I perform math on columns in Access? Icon
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!
 

Additional columns displayed in access query output

Additional columns displayed in access query output Icon
Have a complex query joining multiple tables and queries. My issue is that I get additional columns when I run the query in access, while these columns are not even included in the query. My Access release is 2010.
 

Cumulated values

Cumulated values Icon
I have a table that goes like this

Name Day Amount
Mike 01/09/2010 +12
Mike 02/09/2010 +19
Mike 03/09/2010. -8
Mike 04/09/2010 -15
Mike 05/09/2010 +22
Paul 01/09/2010 +13
Paul 02/09/2010 -9
Paul 03/09/2010 -8
Paul 04/09/2010 -25
Paul 05/09/2010 +12

[Real table has about 500.000 records]
and I need to calculate, for each name, the cumulated amount and the day over day % amount increase, like this

Name Day Amount Cum %
Mike 01/09/2010 +12 +12 n/a
Mike 02/09/2010 +19 +31 +58% Mike 03/09/2010. -8 +27 -142%
Mike 04/09/2010 -15 +12 +87%
Mike 05/09/2010 +22 +34 -246%
Paul 01/09/2010 +13 +13 n/a
Paul 02/09/2010 -9 +4 -169%
Paul 03/09/2010 -8 -4 -11%
.and so on

How can I achieve this?

This is what I have done:
Make a crosstab query with name on rows, day on columns and amount as value.
This reduces the number of records to about 8000 thus allowing an export to excel.
.But I don't like it! I'm sure access can do this
 

Combining some colums in an Access Table

Combining some colums in an Access Table Icon
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 2010 auto insert from field above issue

Access 2010 auto insert from field above issue Icon
I have just upgraded to office 2010 and I have noticed that in my Access Database, that it no longer auto inserts the next value in sequence in a column of records -

I am a basic user - no programming involved.

Basically, prior to Access 2010 if I had a entered information via a query, and one of the columns was a blank field. If I started the 1st record with say 101 and then cursored down to the same field in the next record and entered 102, a further scroll down would automatically add 103 and so forth.

Access 2010 does not perform this natively - and I have been using Access since Version 1 and it is the 1st time I have come across this.

I could not locate anything in the (very limited) Access Help and I cannot explain why this has changed.
 

Import Worksheet From Excel 2010

Import Worksheet From Excel 2010 Icon
You can share data between Access 2010 and Excel 2010 in many ways. Excel worksheet consists of cells that are organized into columns and rows, Access recognize them as fields and records. Access 2010 provides an easy way to import Excel worksheets, this post will explain in detail how to import worksheet in Access from Excel 2010.
 

Access 2010 More Query Design Options

Access 2010 More Query Design Options Icon
Access 2010 offers many options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to find how many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use Access's query options, you can design almost any query you want.
In this lesson, you'll learn how to modify and sort your queries within Query Design view. You'll also learn how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.