Temp tables vs Permanent table with deletes
I am looking at changing about 10 procedures that use a Permanent table for
nothing more than temporary storage to load and store various records from a
select statement and run a Join on this table to update other records.
It then deletes all the records it put into the table. The table is used by
multiple procedures at the same time keyed by a Guid it always gets at the
start of the routines.
There are about 4 indexes on this table.
There can be any from 1 to 1000 records involved in this.
I was thinking about dropping the table and just using a temp table. When
the t...Multiple field Table Sort
I am using Access 2007 and am unable to find specific instructions on how
to sort a table by more than one field, I would like to, for example, sort
records by city and then within each city by name.
I know I can do it with a query or report but would like to be able to work
on the records in a specific order.
I have tried advanced filter/sort but it didn t seem to work.
Using a query would be the way to handle it. It is generally a good
idea to regard Tables as being background data storage, and not for
human consumption, and use the other tools available, as ap...Newbie: Create report from four XL tables?
I am working with a database that was old when the term "legacy" was
coined! I can run queries and save the results out as Excel files.
The report generator, though, is the pits and is not user-friendly.
(I'm on my second day of reformatting a 450-page report!)
The data I need for a report would have to be saved out from four
different queries: one query would have one line per key field, the
other three would have multiple lines per key field.
I touched Access briefly several years ago, and know just enough to
get myself in deep trouble. So would it be difficult for a newbie...Can you paste to protected spreadsheet and skip over locked cells?
I have a spreadsheet that has some cells locked and others that are
not. I need to refresh the data on the page every month. I want to be
able to copy the new data and paste it into the spreadsheet. The catch
is that I do not want the locked cells to be changed. Is there a way
to paste so that Excel will ignore those locked cells and skip over
them? At this time it says that cells are locked and does not paste
>I have a spreadsheet that has some cells locked and others that are
>not. I need to refresh the data on the page every month. I want to...how to multiply two columns
Hi, i have two columns (A and B) with 126 datas each. And i want to multiply
column A with Column B and like to displayed in column C. pls. help me to do
Assuming your data starts in A1, then in C1 enter this formula:
Select C1 again and double-click the fill handle - this is the small
black square at the bottom right corner of the cursor, and
double-clicking it will cause the formula to be replicated down column
C for as many data items as you have in column B.
Hope this helps.
it really works..thanks again..but finally have to drag the border of the
cell i...Spreadsheet growth
Has anyone figured out the trick to keep a spreadsheet from "growing"
in size as pivot tables are added (and based on the SAME dataset)? If
my kids grew at the rate that my spreadsheets are growing, I'd have to
sell them for medical experiments!
For example, my dataset has 15,000 rows, 37 columns. It takes up about
7.44 megs on its own.
I create a pivot based on that dataset, to filter the data into
certain categories. From ONE of those categories, I create another
dataset within the same spreadsheet (the second dataset contains about
9,000 of the original 15,000 rows).
...How can I fill in a column with the alphabet
I know there is a way that I can type in a in cell a1,b cell a2, b , cell a3,
c and then drag to the bottom of the column and it will continue with d,
e,f, etc but have forgotten.Can somebody please remind me again.
Tools - Options - Custom Lists.
Should be easy from there...
Excel User - Energy markets
barrfly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4141
View this thread: http://www.excelforum.com/showthread.php?threadid=471751
Use of the f...excel should let me set row amd column maximum sizes
Worksheet size is 65,536 rows by 256 columns. I need one that is 256 rows by
65536 columns (or some where in between)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/community/en-us/default.mspx?mid=441f2d8e-4e4d-49ee-b8d4-5f54b182f23d&...#Error error corrupts records in a table
I have a database in MS Access 97 which has a form consisting mainly
of "Memo" fields. It is accessed by a number of people. Every now and
then, if the database is left open or sometimes upon closing it and
reopening it, it corrupts some of the fields on the form and displays
#Error in the field instead of the actual text.
You can then no longer edit any field on the form nor can you edit any
field from the back-end in the table linked to the form. You cannot
even copy the record directly from the table and paste it back in
after removing the #Error message.
As a result, you h...set up a macro to delete characters in each cell of a column
I wish to delete 3 characters in each cell of a row. A macro qould be much
faster but how i do it?
Dim rng As Range
Dim rcell As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim iLen As Long
Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet4") '<<========== CHANGE
Set rng = SH.Range("A2:M2") '<<========== CHANGE
For Each rcell In rng.Cells
If Not .HasFormula Then
...Collapsing columns in Excel?
I am using Excel XP 2003.
Excel's subtotals feature lets you collapse rows and show summary data. It
displays little "plus" and "minus" buttons to show or hide the extra rows.
I'm sure I have seen a spreadsheet where this same technique has been
applied to columns, but I cannot find out how to do it.
I'm creating a spreadsheet where I have a bunch of data about pupils in a
school. At the end of each year they take 11 exams, and I would like to store
all the grades in separate columns for each subject, but I would like to
collapse them down to display an ...Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a
particular question extend from Q6 to Z505. I need to count the people who
did not answer the question (that is, the people, entered in rows 6 to 505
that left columns Q through Z blank). I'm not sure how to do this. Can
someone offer a suggestion?
You can use a formula like this in Column AA
copy down till AA505
You can use a simple Sum formula now to count the empty ones
Regards Ron de Bruin
"crossingboston" <...Filtering Pivot Tables #2
Filtering Pivot Tables
Does anyone know if it is possible to code the filter criteria for a
pivot table? I have several pivot tables in a single workbook that
look up different data from the same source. One might look up parts
where another is set to look up Sales. The filter criteria are all by
date. I am trying to figure out a way that the user only has to select
the date from of the tables and all the tables' filter of the same
date. Is it possible to code something like this?
There's a sample file here that you could adapt to your workbook:
h...Replace data in a column
Okay, I have a column that has text in it. I want to change all these to the
Identifying number that is found in a different table. So how do I do this?
Current table has:
1 First name
2 New first name
3 Another first name
Need to change the A column in first table to the correspoding column in
1 12 First name
2 15 New first name
3 22 Another first name
So after the switch either I need a new column in the current table so it
look like the second column (there might be more then one match in the
current t...Create Table Script with Variable
Is it possible to do the following when creating a table? I've tried
but it creates the table called @CompanyPrefix_STAFF not JY_STAFF.
Any help would be great.
declare @CompanyPrefix varchar(5)
SET @CompanyPrefix = 'JY'
CREATE TABLE [dbo].[@CompanyPrefix_STAFF] (
[Col1] [int] NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [int] NULL ,
[Col4] [int] NULL ,
[Col5] [int] NULL
) ON [PRIMARY]
You'll need to switch to using a dynamic SQL statement (ie: EXEC a string)
to do that.
"Jon" <...Copying Query Results into a Table
I have a Query which filters out certain records from an existing
Table, how can I copy these filtered out records which were based on certain
criteria, to an existing Table which have exactly the same name of fields as
Ange Kappas wrote:
> I have a Query which filters out certain records from an existing
>Table, how can I copy these filtered out records which were based on certain
>criteria, to an existing Table which have exactly the same name of fields as
Try changing the query to an Append query (using the Query
menu...pivot table sort entries that don't yet appear in table
I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.
How can i set this up to c...Totalling entries in different columns
Guys hope you can help, very new to Excel and failing miserably with
formulas at the moment!
I have a spreadsheet with a fair number of columns. Its split into
Job Titles, Names, Regions and training modules. I then mark x in the
box of which town will hold which training course, see below
Name Job Office Mod1 Mod2 Mod3
Jo Bloggs Engineer London x
Flo Green Sales Manchester x x
Jon Brown Engineer Birmingham x
Pat Orange Admin London x x
What I ne...vertical line between columns in report
I draw a vertical line in the center of a 2 columns of the report
(column spacing). It does not show. If I put the line close to the
right edge of the first column, it shows. But I want the vertical line
in the center of column gap. How to do it?
Are you using code with the Line Method or are you using a line control?
What is the gap? Is this the Column Spacing?
If you have a multi-column report with a column spacing of 0.25" you might
try code in the On Page of
Private Sub Report_Page()
Dim intGap As Integer
intGap = 0.25 * 1440 / 2
Me.Line (Me.Wid...delete row and automatically renumber column
This is probably a piece of cake for this NG : -
(For example) I have a small data base (any # of columns by any # of rows)
I wish to delete an entry and have the left side column automatically
renumber itself rather than having to manually (drag) to renumber the
column. Is this possible? Please help.
The numbers do not change when you delete a row. If you are using a column
with row numbers in in why not substitute the row function in the formula
=row(a5) and copy down
"Graham Hill" <hill...On a column chart, how do you put a percent sign on the top most .
On a stacked column chart, how do you put a percent sign on the top most
number on the vertical (value) axis?
On Jan 13, 3:49 pm, JudyF <Ju...@discussions.microsoft.com> wrote:
> On a stacked column chart, how do you put a percent sign on the top most
> number on the vertical (value) axis?
Use a custom number format, say if the max value was 70:
The only problem with that is that it doesn't align the column of
numbers well, to fix that add a couple spaces after the others.:
I got the percent sign on th...How do I put information into a NEW journal table?
Having created a New table in Outlook 2003 Journal with different column
headings (to record family tree history), how can I put information into each
of the new headings, which do not appear on the Journal Entry form?
You'll need to create a custom fields for the new information.
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips.net/
Outlook & Exchange Solutions Center: http://www.slipstick.com/
Outlook Tips by email:
EMO - a weekly newsletter about Outlook and Exchange:
mailto:EMO-NEWS...Problems with updating category names in pivot tables
I have an issue with pivot tables, previously my pivot table contained months
where each month was written like "January, February" etc, but now I have
changed the way the months are written to "01-january, 02-february". My
problem is that even though the data with the old month names no longer
exists in the pivot table, the pivot table still provides "January, February"
etc. as filtering options in the month category. I've tried updating the
pivot table but it doesnt help. The only way i've been able to get rid of
this was by creating a new pivot t...Linked Table to Outlook contacts
Please bear with me if this is a wrong forum for the
I have created a linked Table to the contacts in my
outlook. The issue is that the linked table does not
contain links to all the fields that are available on the
Outlook Calendar form. I am using Outlook 2003 and Access
2003 to map to outlook.
I am trying to develop a web application that can use my
address to add / update my phonebook based upon my
Is there any way in Access, that I can specify, which
fields to link to (if not all) to an external data source
(Outlook contacts in this case).
...Convert text invoice into a table- please help!
We have an invoice in text format in excel. I need to convert in a
table format to be able to convert it into a pivot
I need macros created which can
1) delete blank rows
2) delete rows which start with certain text in a particular (the
invoice heading appears frequently in every page)
3) each transaction is in 2 lines- need it to be in one line
ROSANA ALAM 36422299601017
FINANCE CONTROLLER 30/03/10
MICROSOFT GULF FZE LLC
MICROSOFT STAFF 1
P O BOX 52244
AGN CODE:01 AGN NAME :DNATA TEMPORARY STATEMENT FOR MARCH
DATE TICKET TRAN...