Access 2010: How To Insert Rows / Columns In Query Design
Thank you for taking the time to report an issue.
What's wrong... Please write below.
How do I insert the desired rows without moving EVERYTHING on the formdown?
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
If so, is there some place that will tell me the order of thecolumns when I run the query?
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!
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
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.
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.
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.