Multi Table Queries
At my job, I use enter data through generated queries. However it appears
that I can only ENTER data in a query when it is based on two tables. If it
is three or more, it doesn't let me enter any info.
Is this normal? Am I doing something wrong?
>At my job, I use enter data through generated queries. However it appears
>that I can only ENTER data in a query when it is based on two tables. If it
>is three or more, it doesn't let me enter any info.
>Is this normal? Am I doing something wrong?
Yes, that's common. You should only edit data ...Unique records in Pivot Tables
I have data that stores Client Invitation to Tender and
Supplier Contract details. Each Client ITT is uniquely
identified. However we send out the same ITT to many
suppliers so each request for service is given the same
Reference Client/Supplier Contract Name Value Status
AC/001 Client blah 4,000 pend
AC/002 Client blah2 5,000 pend
AS/001 Supplier blah3 3,000 pend
AS/001 Supplier blah3 3,000 pend
When I create my table by Client to get the total value
pending ...how can I get the total hrs of a delay when it is greater than 24
I work for an airline and sometimes I need to know the total hours of a
delay, and when the delay is greater than 24 hours. In my report I hav
something like this:
arrival time in colum A departure time colum B result in colum C
10/23/09 21:00 10/24/09 23:00 2:00
and really the delay was of 26 hours. How can I obtain this?
I already set up my Tools-Options-Canculation to 1904 date system
Format Custom as [h]:mm
No need to use 1904 date system
Microsoft MVP - Excel
"El Ixmahana" <ElIxmahana...only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the
first 5 columns that limits the range of the sort. How do I remove it?
Using Office 2003.
Maybe if you remove the Data|list
Select a cell in that blue box.
Data|list|convert to range
> How do I get the whole spread sheet to sort? There is a blue lox for the
> first 5 columns that limits the range of the sort. How do I remove it?
> Using Office 2003.
...Hide columns according to background fill color
I am having trouble understanding how Excel handles colors.
I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType.
In another module, I use the TermColor variable as follows:
Sub WeedColsByColor(ByRef Clr, ByRef WS)
Dim LastCol, i As Long
LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
'hide columns if they have one of the forbidden colors
Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr))
...Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I
try to access the employee master table.
Either from cards>payroll>Employee, or payroll transaction entry. Anytime I
need to do a lookup on an employee id.
Here is the message:
A Get Change operation on table UPR_MSTR failed accessing SQL Data
If I go to the more information or details button:
[Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF'
[Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS'
It looks like an upgrade script failed...Pivot table novice
As a teacher we tried a new data analysis tool this year in my school
which worked really well... except for the fact that it meant manually
filling in a table.
I'm sure there must be a more efficient electronic way of doing it, but
I'm not really an expert on these things.
I want to be able to put in a list of data: pupils names with scores
from two consecutive tests. Then I want to produce a table with test 1
and test 2 as the headers, and pupils' initials appearing in the
relevant box. I have managed to create a Pivot Table to show what I
want, except of course, it total...Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths.
Is this possible?
-The 1st sub-report has 1 column that occupies the entire width of the page
-The 2nd sub-report can fit 2 columns in the page width
-The 3rd sub-report can fit 3 columns in the page width
Subreports can have any number of columns that don't have to be the same from
one to another. Typically your columns should display across then down in
order to render properly as a subreport.
Microsoft Access MVP
> I want to create a r...Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and
1 from Table 2. All three entries are included for each day. Except somedays
which may have only the one piece of data from Table 2 or none at all. How do
I get the table to automatically fill in all the days whether they have
information or not?
=?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in
> I have a pivot chart which pulls 2 pieces of information from one
> table 1 and 1 from Table 2. All three entries are included for
&...Help to build a table
Please help me to build a table similar to this one
The number on the first column will change after 16 times, it will go in
sequence from 1001, 1002, etc…
The number in the second column will change after 4 times and it will go
from 1 to 4
The number in the 3rd column will go from 1 to 4
2nd and 3rd column will keep the same pattern.
Cabinet Shelf Location
1001 1 1
1001 1 2
1001 1 3
1001 1 4
1001 2 1
1001 2 2
1001 2 3
1001 2 4
1001 3 1
1001 3 2
1001 3 3
1001 3 4
1001 4 1
1001 4 2
1001 4 3
1001 4 4
1002 1 1
1002 1 2
1002 1 3
1002 1 4
1002 2 1
1002 2 2
1002 2...Pivot charts formatting
I want to change the formatting imposed by default in a pivot chart and I
want that changed formatting to be retained permanently.
For example, for my bar chart, for the different series I have selected
colours / patterns but every time I refresh the pivot table / chart; excel
falls back to its own colour coding.
Any way to get around this?
Many thanks in anticipation.
Not directly. The only way to simulate this is to record a macro of the
formatting changes that you make and then set it up to run whenever the
PivotChart calculates. Assuming your pivot chart is on a seperate shee...Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the
columns by right clicking and choosing the desired options. In the tab
that opens there is a slider which is supposed tho set the level of
transparency of the column (selceted area). But so far i couldn't find
a way how to use this slider.
I know that there is an alternative way to get transparent bars by
creating a rectangular object formating it and the use copy -> paste.
But i wonder what is the slider for if you can't use it?
Does anybody know have an idea?
...Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting
feature, except I really need to filter and group the resulting pivot table
in Excel by custom fields. For example, for task usage, I cannot filter my
pivot table to show only the tasks that have costs > $0, thus my table ends
up showing too many lines and a lot of 0's. I would also like to group by
department/ function which I put in a custom text field. How do I do this?
I saw related posts about saving the file as an Access database and then
creating the pivot table by connecting to the datab...Combining Pivot Tables
I have a data set of around 100,000 rows which I have imported into
excel in two sheets (~50,000 rows each).
The data is not in a format that excel can easily parse into a pivot
table directly from the source - it requires some formulae in excel to
be able to use a pivot table.
I have used a pivot table on each of those sheets to summarise the
data, and that works fine.
However, I would like to be able to get a single summary pivot table
from the two sheets (or from the two pivot tables).
Is that possible, and if so, how do I go about it?
"Alan" <...Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message:
Unhandled database exception: A get/change operation on table
'Batch_Headers' could not find a record.
Then we get this message:
This transaction was recovered during normal processing. You may continue
processing this transaction.
You might want to run Check Links on payables. Before doing this, make sure
you have a backup of your dynamics and company databases.
> During Edit Checks, we get the following message:
> Unhandled databas...Column width
In Sheet 1 I have a certain amount of data, I want to select some cells and
copy them to Sheet 2 keeping the same format. When I do this, the fonts and
the colours remain unchanged, but the column width don't. I have tried paste
special, but couldn't figure it out. Is this possible?
Thanks in advance
--Select the target cell and right click >PasteSpecial>All>OK
--Keeping the target selection right click>PasteSpecial>select ColumnWidth>OK
If this post helps click Yes
"Emece"...Grand Substraction instead of Grand Total in pivot table
I created a pivot table in Excel with Visual Report 2007. I'm using an
Outline Code with 2 leaves : Receipts and Expenses.
The pivot table created automatically a Grand Total so that $1000 Receipts
and $800 Expenses => Grand Total = $1800.
My accountant says $200.
Generally, it's possible to create calculated fields in the Excel pivot
tables. But in the pivot table created by the Visual Reprot, all the
Calculated Field options are greyed out.
Thanks for any help
I don't know what visual report is, but suspect that it would be more a
question for them not Excel a...Conditional text field Total
In the detail section of a report I have a text box that based on a condition,
it will be either a 0 or a 1.
I want to total the control at the end of the report. I did this in another
report a couple of years ago but it is not working now.
In the previous report, the ControlSource of the total is =
[TextBoxNameFromDetailSection] and it sums correctly.
What am I doing wrong??
Message posted via AccessMonster.com
Thanks but I see what was missing - "running sum over ...Add values in a column according to value in another column
How can I add the values in a column according to values in another
column? If there is any value in a row in column B, I want to include
the value of the corresponding row in column A. I'm flexible as to
whether this is ANY value (i.e. not empty) or greater than zero.
Maybe something like this =IF(B1="","",IF(B1>0,B1+A1))
"Paul Kaye" <email@example.com> wrote in message
> How can I add the values in a column according to values in another
> colu...How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right
not it defaults to my items field.
or in macro
>I need to make my desricption field my default field. How do I do that? Right
>not it defaults to my items field.
Message posted via AccessMonster.com
Or, if you don't want to use events, simply set your tab order from the form
R...Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation
that has stumped me. Any help would be much appreciated.
I might be able to solve this issue if somebody could show me how to add a
number to a column. For example, if I want Excel to pull data from Column
D, how can I get Excel to realize that column D is really the same thing as
Column A + 3? I know you can use the column() command to get the numerical
value for a column, but is there a way to have it do that in reverse, such
that you could tell it the column number is 4 and it would know that you are
referring...Report to show totals for each day of month
I have an Excel spreadsheet that I use to capture:
Column A) Day of the month (1-May, 2-May, etc.)
Column B) How many clients were in residence on each day (Occupancy)
Column C) Total Capacity (27)
Column D) Shows a "1" if the Occupancy is 90% or more of the Total
Capacity, else "0"
The Totals row shows:
Column B) The average Occupancy for the month
Column D) Totals the times there is a "1" in Column D
Is there a way for me to capture this data in a Report?
I've got a query set up that uses these expressions:
For AdmitDate: <=[Dat...counting dates <= 7 days ago based on criteria in a diff column
I have a spreadsheet that holds all tasks for a project. Column D holds a
catagory and column Q holds the date closed. I need a formula (on a separate
sheet) that counts all tasks of a specific category that were closed in the
past 7 days. I already have a formula that calculates all tasks that were
closed in the past 7 days, just need to add the additional criterion of the
A2 on sheet2 has the specific category for which you want to count the
Ashish Mathur...How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can
I do that easily without going to every cell?
Select your column
then choose edit / goto / special - constants - ok
this will just select the non-formula cells then press the delete key
"EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message
>I want to zero out a column of data, but not the subtotal formulas. How
> I do that easily without going to every cell?
qlso look at:
http://www...excel command that counts conditions met in 2 columns?
is there a form of countif that increments only if conditions are met in two
(or more) columns? e.g., countif(colA = 1 and colB = 2)
You need SUMPRODUCT. Have a look here for some guidance and post back if you
need some more help.
Hope this helps.
"brendalw" <firstname.lastname@example.org> wrote in message
> is there a form of countif that increments only if conditions are met in
> (or more) columns? e.g., countif(colA = 1 a...