Query question on joining information from two tables
Given two tables:
In table Foo:
foo_id foo_desc manchu_id
1 apples 1
2 pears 1
3 apples 2
In table Manchu:
How would I join these two tables in a query to get a result that
looks like this:
1 apples ripe
2 pears ripe
3 apples spoiled
This is a basic inner join on the manchu_id column:
SELECT F.foo_id, F.foo_desc, M.man...Append a framed table
I have a table with a frame all around it.
How do I append a row quickly?
My only solution so far is:
1. mark the last row of the table
2. insert row (shift cells down)
3. copy content from last row to the empty row
4. insert data in the last row
Is there another way?
AFAIK that's the only way to do it. I often
wish MS had an "append" in addition to "insert".
>I have a table with a frame all around it.
>How do I append a row quickly?
>My only solution so far is:
>1. mark the last ...pivot table #15
is there a way to preserve border outlines in a pivot table after you refresh?
Right-click a cell in the pivot table, and choose Table Options
Ensure that Preserve Formatting is turned on, and AutoFormat is turned
off, then click OK
To format cells, enable selection should be turned on.
To enable selection --
From the Pivot toolbar, choose PivotTable>Select
If it's not already activated, click on Enable Selection
To format a section of a pivot table, e.g. subtotals --
Move the pointer to the left of a subtotal heading in the pivot table.
When the black arrow appears (like the on...More table trouble
Consider a grid with 20 spaces, Space A, Space B etc. In each space Iwant to list a persons name and a date. I would like the data entry tobe on a form graphically.Do I set up one table with fields SpaceA-Name, SpaceA-Date, SpaceB-Name, SpaceB-Date etc.? This would certainly make it easier indesigning the form and making sure the data goes to the right field,but it's probably poor table design. Or do I use separate tables? Andthen how do I set up the form with the proper record sources?My brain hurts.Thanks,Dave B On Mar 9, 7:56 am, "Dave" <Dave.Bea...@lhsc.on.ca> wrote:>...Search a Table, find, and copy all data from the row
Would someone be kind enough to help me on this? I want to take a list
compare it to a table, and if a match copy all of the elements from th
table in the same row in which the element match was found:
HORSE BROWN LARGE Worker
DOG WHITE SMALL Pet
When list matches table, I want to copy the information from th
following columns (e.g. WHITE, SMALL, Pet) and place in columns next t
the item in the list, so the list would then look like:
DOG WHITE SMALL Pet
Thank you for any help
Message posted from http://www.ExcelForum.com
If there is on...Bill Frequency
Does or why doesn't Microsoft Money allow for an every other week skip last
frequence when creating new bills? I've never seen it and it's the one
frequency that I really need as that is how my Mortgage account is setup. I
make payments every other week and skip the 5th week in the two months that
The Money recurrence model is **very** simple. Outlook's is much better.
Even it won't handle the model you are suggesting. Why is Money's so simple?
Apparently because they think Most Users Prefer(tm) it this way. It comes up
here often enoug...Include info from data source in data table but not in chart
Need to represent a variance between two pieces of data in data table but do
not want it to show in the corresponding chart.
Hide the series in the chart by formatting it to use no markers, no lines,
no fills, no whatever.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
"UHSH Admin" <UHSH Admin@discussions.microsoft.com> wrote in message
> Need to represent a variance between two pieces of data in data table but
> not want it...Pivot Table with multiple worksheets
Sorry but I have gone through all help programs and cannot find how to make
a pivot table with multiple worksheets.Any help would be great.
You can create a Pivot Table from multiple consolidation ranges, but you
won't get the same pivot table layout that you'd get from a single
range. There's an example here:
If possible, store your data on a single worksheet, or in a database,
and base the pivot table on that. You'll have more flexibility in
creating the pivot table
> Sorry but I have go...Linking/attaching access tables
Is it possible to link access tables to a sql server 2005 db? I can't seem
to find any option in Management Studio.
See details in the BOL how to build a linked server to Access
"John" <firstname.lastname@example.org> wrote in message
> Is it possible to link access tables to a sql server 2005 db? I can't seem
> to find any option in Management Studio.
...Importing of tables and other database elements into a new databas
I have posted a query on the subject but i don't remember the exact date.
How to find our my query and know whether it is answered or not.
On Fri, 26 Mar 2010 22:57:01 -0700, Bobharis
I typically go to groups.google.com and run a query there.
Microsoft Access MVP
>I have posted a query on the subject but i don't remember the exact date.
>How to find our my query and know whether it is answered or not.
How depends on what ... what newsreader are you using?
Have you tried <Find>?
Regar...TABLE W/RUNNING BALANCE
need an excel table. needs the starting balance, the date, vendor name,
amount and have it deducted from the running balance, very simple like a
column a .....date
column b .....name
column c .....amount
column d .....balance
start in row 1 with headings
row 2 with the opening balance in D2
enter in D3 =if(C3="","",D2-C3) this means in plain language
if cell c3 is empty show d3 to be empty, otherwise is c3 has a value
show the baance in D3
hover over the right hand bottom corner of cell D3
the mouse pointer will change to a &qu...Need To Complete Formular To Sort And Show Frequency
Column A is a list of numbers that will be manually entered, processe
and replaced frequently.
I'd like to have Column B represent the sorted list without multiple
in decending order with no blank rows and Column C represent th
frequency of occurences for each value in A.
* represents error message
MORRIGAN HAS OFFERED THE FOLLOWING FORMULAS THAT PERFORM WELL.
AGAIN, THANK YOU MORRIGAN
*Assume row 1 is your header, A2:A7 is the data
B2 = SMALL(A$2:A$7,1+SUM(C$1:C1))
C2 = COUNTIF(A$2:A$7,B2)
C1 can be blank b...Indexing GP tables
Does anyone know if there is any reason why I shouldn't create additional
indexes on GP tables. A customer needs this for report performance for
custom AR reporting. Does this affect GP in any way? Does it impact
See this post from yesterday:
Bud Cool, Accounting System Manager
HDA, Inc. Hazelwood, MO
GP 9.0, SP2
> Does anyone know if there is any reason why I shouldn't creat...which better: Frequency vs Histogram
I read "Explore Histograms" in
http://office.microsoft.com/en-us/assistance/HA011109481033.aspx and found it
educational. However, I can't see a difference in output between using a
Histogram and using the Frequency function.
Can anybody share the pros and cons of each?
I think you have missed the point. The article says that the table
produced by the ATP histogram tool is exactly the same as the table
(with same bin definitions) produced by the FREQUENCY function. The
difference is not in the values calculated, but in the fact that the
histogram table is a static t...Pivot Table Question #8
I have a pivot table that looks a bit like this
Measure Jul-06 Aug-06 Sep-06
Income 20 30 40
Each month the data table that the pivot is based on has another
month's worth of data added i.e for the above example the income for
Oct-06 will be added.
The problem is that in the above pivot I have manually gone into the
Month field and deselected 'All' and then selected 'Jul-06', 'Aug-06',
'Sep-06', so next month when October is added to the data the above
pivot will be unchanged i.e Oct-06 won't appear...No Group dialog box appear when working in Pivot Table
I'm trying to group a range of dates in pivot table.
According to help files a dialog box should appear when
selecting Group..., but nothing happens except a grouping
of the entire marked range.
How do I go about in order to make the Grouping dialog
This might be something particular to my set-up (XL2K), but I have to build
the table initially by specifying the data range *exactly* (click'n'drag on
the worksheet) for grouping dates to be an option. If I use a blanket source
(eg: A:B) it won't cut it. Try it again from scratch, the...Defragmentation frequency
I use my home PC (XP SP3) to burn CD’s and movies.
I start off by transferring 2-4 GB of data from USB drives to the HHD.
After the transfer, I run Disk clean and then Defrag. Often, Defrag shows
blocks of fragmented files. Sometimes I have to run Defrag multiple times in
order to clean them up before burning the CD's.
I was speaking w/ a Dell tech rep yesterday about my new laptop (Windows 7
64 bit) & he indicated that running Defrag too often may damage my system -
for W 7, he said to just run it every other month.
I would appreciate your opinion as to whether t...How do I manipulate pivot table report to include percentage of?
In my pivot table report, I have two variables in the "data" field, TRIALS
and CONVERSIONS. The two variables are summarized by sums, which is what I
want. However, I would like the second variable, CONVERSIONS, to be expressed
as a % of TRIALS.
What I've tried already: In the Pivot Table Field for the CONVERSIONS
variable, I clicked on Options >>; I clicked on Show data as "% of". Base
field=TRIALS. Here's where the problem is (I think): I don't know what to
click on for Base Item; I tried clicking on both (previous) and (next), but I
got back a #...Creating summary table
I'm a pretty experienced user and this may be a simple task, but I
don't know exactly how to start. I have a worksheet where columns
represent different countries and rows different years, and if the
country managed to balance its balance its budget in a given year the
worksheet shows a value of 1 and if not, a value of zero. (The
definition of balanced budget is more complex, but doesn't matter
What I'd like to do is create a summary table that would have two
columns, listing the country and the year in which its budget was
balanced, but omitting any countrie...Frequency within a frequency
I think I have a simple question here, but unable to figure it out
myself, so seeking help on it. I have some accident records with
column heads as "Acc.MP(mile point)", "Severity Index(some number
betw.1-5)". I know the start and end mile point of the route and I can
find the frequency in 1 mile bins using the frequency function. But I
also want to know for each mile, how many 5's are there.
Heres an illustration of it.
Acc.MP Severity Index
S...How do I split a cell in a table
How do I split a cell horizonally in a table in MW Pub 2007? the option is
grayed out. thanks for your help
You can't unless you merged the cells at one time and want to re-split them. You
can create a text box(es) to mimic a split cell.
"suegb" <email@example.com> wrote in message
> How do I split a cell horizonally in a table in MW Pub 2007? the option is
> grayed out. thanks for your help
> Suegb Inc.
...Table creation using SanScript
I have an application (GP add-in) that defines 4 tables. I have a procedure
that is run just before the tables are accessed that opens each of them using
the following command:
open table mytable;
close table mytable;
When I'm in the development environment (Dexterity), the tables are created
fine. When I install my chunk file and run GP, one of the tables is not
created - the other three are. I have deleted and recreated them. Their
definition is identical in Dexterity, that is the parameters of their
definition (table type, etc.).
Does anyone have any idea why one of the tab...Help Pop Up Table
I am creating a form and would like to have a table of
information pop up, perhaps upon entering information in a
not quite sure what you're trying to do but one way to
give information about a specific filed for the user would
be to use a comment. select your cell and goto 'Insert -
You may also consider using 'Data - Validation' and define
a custom error message
>I am creating a form and would like to have a table of
>information pop up, perhaps upon entering information in...Chart Format generated from a Pivot Table Data
After I Format a Chart generated from a Pivot table Data, and I change a
selection, for example "Plant".. Al the information on the chart changes,
according to the new plant selection, but, why the Chart Type and the format
also changes too... I need to keep the same chart type and format to keep
reviewing the information for the rest of the plants.... with the same chart
type and format.
Microsoft admits they goofed that one up:
XL2000: Changing a PivotChart Removes Series Formatting
They suggest you record a macro, so you can r...Pivot Table Show detail to retained Pivot tables formatting
When I have a pivot table summarising the Amount of "Spend" by each team I format the Spend field as a currency field in the data area. Now when I double click on it, it opens this detail in another worksheet but doesnt retain the formatting used in the pivot table.
Its my understanding that this is not possible.
Possible move forward in Excels development? I see pivot tables as one of the most useful area's in Excel.
Windows 98 + Office Pro 97
In addition to posting your comment here, you can send suggestions to: