Null or NOT in another table

Hello.

I have a query that verifies that required fields in my records are
populated and correct.
Right now it returns the records that have required fields=NULL. some
of the fields need to be verified for legal values from another table
as well. So I need to return values where the value of field [Tag]!
[function_code] is not in  [WM_GOC_Conversion]![Function_Code]

My current query is a datasheet with autoformat that marks the null-
fields with yellow background. I'd like to be able to mark the wrong
information with red text, but first I need to have a query that
return the records! :-)
0
atledreier
5/3/2010 11:57:56 AM
access.queries 6343 articles. 1 followers. Follow

5 Replies
712 Views

Similar Articles

[PageSpeed] 14

On Mon, 3 May 2010 04:57:56 -0700 (PDT), atledreier
<atledreier@gmail.com> wrote:

Are those FunctionCode values required to come from the Conversion
table. or is that optional?

-Tom.
Microsoft Access MVP


>Hello.
>
>I have a query that verifies that required fields in my records are
>populated and correct.
>Right now it returns the records that have required fields=NULL. some
>of the fields need to be verified for legal values from another table
>as well. So I need to return values where the value of field [Tag]!
>[function_code] is not in  [WM_GOC_Conversion]![Function_Code]
>
>My current query is a datasheet with autoformat that marks the null-
>fields with yellow background. I'd like to be able to mark the wrong
>information with red text, but first I need to have a query that
>return the records! :-)
0
Tom
5/3/2010 1:47:34 PM
atledreier wrote:
>I have a query that verifies that required fields in my records are
>populated and correct.
>Right now it returns the records that have required fields=NULL. some
>of the fields need to be verified for legal values from another table
>as well. So I need to return values where the value of field [Tag]!
>[function_code] is not in  [WM_GOC_Conversion]![Function_Code]
>
>My current query is a datasheet with autoformat that marks the null-
>fields with yellow background. I'd like to be able to mark the wrong
>information with red text, but first I need to have a query that
>return the records! :-)


That sounds like what the unmatched query wizard would
translate into an outer join.  Without seeing your original
query, all I can say is that you might be able to get that
data from a query something like:

SELECT Tag.*, WM_GOC_Conversion.Function_Code As Test
FROM Tag LEFT JOIN WM_GOC_Conversion
		ON Tag.function_code = WM_GOC_Conversion.Function_Code
WHERE Tag,f1 Is Null
		OR Tag,f2 Is Null
		  . . .
		OR WM_GOC_Conversion.Function_Code

To display that the way you want, you need to use a form
where you can utilize Conditonal Formatting to get the
colors.  For the missing value fields, use the condition Is
Null and set the BackColor to yellow.  For the function code
field, use the Expression Is: option with the expression
[Test] Is Null and set the ForeColor to Red

-- 
Marsh
MVP [MS Access]
0
Marshall
5/3/2010 2:18:50 PM
Validations inter table are generally made trough data relation.

As example, to be sure that a table1.f1 value is not in table2.f2, someone 
MAY come with a design implying a merge (with UNION ALL) of the two tables 
into one, where the new field f3, filled with values from f1 and f2, would 
not allow duplicated values. But that works only if table1 and table2 are 
compatible (or if they share in  common a portion of the fields, ie, if they 
have in common a "sub-type").


With Jet 4.0, you can write more generic table validations rules, BETWEEN 
TABLES, with the CHECK constraint. There is no User Interface, though, so 
you have to use DDL-SQL. As example, to enforce the previous 'rule', someone 
may add the following rule, to table1:

    CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT 
table1f1NotInTable2F2 CHECK(0=(SELECT COUNT(table2.f2) FROM table2 WHERE 
table2.f2 = table1.f1))"

and, to table2:

    CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRAINT 
table2f2NotInTable1F1 CHECK(0=(SELECT COUNT(table1.f1) FROM table1 WHERE 
table1.f1 = table2.f2))"


You can drop such a CHECK constraint with (since there is no user interface 
to drop it otherwise) something like :

    CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT 
table1f1NotInTable2F2"

as example.

Note that the check constraint name has to be unique, over the whole 
database.
Note that a check constraint fires when data is appended, or modified, but 
not on deletion. The check is made with values AFTER the tried 
update/insertion is, temporary, done.
Note that no VBA function must be involved in the CHECK constraint (ex, 
impossible to use Nz( ), while iif( ) is ok ).
Note that MS SQL Server does not allow this kind of CHECK constraint 
involving other tables (or other rows of the same table); you would a 
trigger, instead.



Vanderghast, Access MVP


"atledreier" <atledreier@gmail.com> wrote in message 
news:7508ed8f-c33a-452e-b2dc-d79346d08c80@r34g2000yqj.googlegroups.com...
> Hello.
>
> I have a query that verifies that required fields in my records are
> populated and correct.
> Right now it returns the records that have required fields=NULL. some
> of the fields need to be verified for legal values from another table
> as well. So I need to return values where the value of field [Tag]!
> [function_code] is not in  [WM_GOC_Conversion]![Function_Code]
>
> My current query is a datasheet with autoformat that marks the null-
> fields with yellow background. I'd like to be able to mark the wrong
> information with red text, but first I need to have a query that
> return the records! :-) 

0
vanderghast
5/3/2010 2:22:53 PM
Vanderghast: I'm going to use a lookup in a form from now on, i just
need this to clean up the data already in the table

Marshal Barton: My original query:

SELECT Tag.Tag, Tag.Description, Tag.Tag_cat, Tag.Function_code,
Tag_diverse.Disiplin, Tag.System, Tag_diverse.Comm_Pkg, Tag.Area,
[Tag_format_in/_br/_te].Fire_area, Tag.Loop
FROM ([Tag_format_in/_br/_te] INNER JOIN Tag ON [Tag_format_in/_br/
_te].Tag =3D Tag.Tag) INNER JOIN Tag_diverse ON Tag.Tag =3D
Tag_diverse.Tag
WHERE (((Tag.Loop) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Description) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Tag_cat) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Function_code) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag_diverse.Disiplin) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.System) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag_diverse.Comm_Pkg) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Area) Is Null) AND ((Tag.Flag) Is Null)) OR ((([Tag_format_in/
_br/_te].Fire_area) Is Null) AND ((Tag.Flag) Is Null));

Basically checking one after the other for NULL values, and making
sure the 'Flag' field is null for each test.

So in addition I need to check that Function_code is valid from the
WM_GOC_Conversion.Function_code.

I also need a few other checks in there, but they may be more
complex...

I have a table with fields Area and Fire_area. The user select an area
in a combobox and a second combobox allow the users to select one of
the appropriate fire_areas.

P01 -A1
P01-A2
P02 -C1
P02 -C2
....and so on.

Earlier I had one lookup with all the areas, and one with all the
fire_areas, and no validation between them. I'd like to check if all
the fire-areas are legal too.


On 3 Mai, 16:22, "vanderghast" <vanderghast@com> wrote:
> Validations inter table are generally made trough data relation.
>
> As example, to be sure that a table1.f1 value is not in table2.f2, someon=
e
> MAY come with a design implying a merge (with UNION ALL) of the two table=
s
> into one, where the new field f3, filled with values from f1 and f2, woul=
d
> not allow duplicated values. But that works only if table1 and table2 are
> compatible (or if they share in =A0common a portion of the fields, ie, if=
 they
> have in common a "sub-type").
>
> With Jet 4.0, you can write more generic table validations rules, BETWEEN
> TABLES, with the CHECK constraint. There is no User Interface, though, so
> you have to use DDL-SQL. As example, to enforce the previous 'rule', some=
one
> may add the following rule, to table1:
>
> =A0 =A0 CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRA=
INT
> table1f1NotInTable2F2 CHECK(0=3D(SELECT COUNT(table2.f2) FROM table2 WHER=
E
> table2.f2 =3D table1.f1))"
>
> and, to table2:
>
> =A0 =A0 CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRA=
INT
> table2f2NotInTable1F1 CHECK(0=3D(SELECT COUNT(table1.f1) FROM table1 WHER=
E
> table1.f1 =3D table2.f2))"
>
> You can drop such a CHECK constraint with (since there is no user interfa=
ce
> to drop it otherwise) something like :
>
> =A0 =A0 CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTR=
AINT
> table1f1NotInTable2F2"
>
> as example.
>
> Note that the check constraint name has to be unique, over the whole
> database.
> Note that a check constraint fires when data is appended, or modified, bu=
t
> not on deletion. The check is made with values AFTER the tried
> update/insertion is, temporary, done.
> Note that no VBA function must be involved in the CHECK constraint (ex,
> impossible to use Nz( ), while iif( ) is ok ).
> Note that MS SQL Server does not allow this kind of CHECK constraint
> involving other tables (or other rows of the same table); you would a
> trigger, instead.
>
> Vanderghast, Access MVP
>
> "atledreier" <atledre...@gmail.com> wrote in message
>
> news:7508ed8f-c33a-452e-b2dc-d79346d08c80@r34g2000yqj.googlegroups.com...
>
>
>

> > Hello.
>
> > I have a query that verifies that required fields in my records are
> > populated and correct.
> > Right now it returns the records that have required fields=3DNULL. some
> > of the fields need to be verified for legal values from another table
> > as well. So I need to return values where the value of field [Tag]!
> > [function_code] is not in =A0[WM_GOC_Conversion]![Function_Code]
>
> > My current query is a datasheet with autoformat that marks the null-
> > fields with yellow background. I'd like to be able to mark the wrong
> > information with red text, but first I need to have a query that
> > return the records! :-)

0
atledreier
5/4/2010 8:49:40 AM
If all the values are in the same record, you can add what is called a table 
validation rule (which is, really, a RECORD validation rule), which *has* a 
user interface, in table design, to specify it. Basically, if you type the 
content of your WHERE clause there, you will then ENFORCE that condition to 
stand for each and every record in the table. It is in the table properties 
sheet (NOT field properties), under Validation Rule, that you would have to 
paste the expression. You can add a custom text, in Validation Text, to 
report error, that is, when the condition is not met while a new record is 
appended, or modified.

For the second question, it is a matter to define a relation between your 
actual table and the table defining all possible fire area values, and to 
ENFORCE the relation (not necessary to cascade it, for deletion or update, 
but need to be enforced to make sure the value in the field is always one of 
the referred fire area values stored in the reference table). It is under 
Database Tools tab (Access 2007), bring the two tables, drag fire area field 
from the reference table to main table, and edit the relation. Check the box 
"Enforce Referential Integrity", once you are sure the matching fields are 
properly involved by the relation.


Vanderghast, Access MVP


"atledreier" <atledreier@gmail.com> wrote in message 
news:1b503fc8-e4f8-4811-b769-856d9e4da304@b7g2000yqk.googlegroups.com...
Vanderghast: I'm going to use a lookup in a form from now on, i just
need this to clean up the data already in the table

Marshal Barton: My original query:

SELECT Tag.Tag, Tag.Description, Tag.Tag_cat, Tag.Function_code,
Tag_diverse.Disiplin, Tag.System, Tag_diverse.Comm_Pkg, Tag.Area,
[Tag_format_in/_br/_te].Fire_area, Tag.Loop
FROM ([Tag_format_in/_br/_te] INNER JOIN Tag ON [Tag_format_in/_br/
_te].Tag = Tag.Tag) INNER JOIN Tag_diverse ON Tag.Tag =
Tag_diverse.Tag
WHERE (((Tag.Loop) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Description) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Tag_cat) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Function_code) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag_diverse.Disiplin) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.System) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag_diverse.Comm_Pkg) Is Null) AND ((Tag.Flag) Is Null)) OR
(((Tag.Area) Is Null) AND ((Tag.Flag) Is Null)) OR ((([Tag_format_in/
_br/_te].Fire_area) Is Null) AND ((Tag.Flag) Is Null));

Basically checking one after the other for NULL values, and making
sure the 'Flag' field is null for each test.

So in addition I need to check that Function_code is valid from the
WM_GOC_Conversion.Function_code.

I also need a few other checks in there, but they may be more
complex...

I have a table with fields Area and Fire_area. The user select an area
in a combobox and a second combobox allow the users to select one of
the appropriate fire_areas.

P01 -A1
P01-A2
P02 -C1
P02 -C2
....and so on.

Earlier I had one lookup with all the areas, and one with all the
fire_areas, and no validation between them. I'd like to check if all
the fire-areas are legal too.


On 3 Mai, 16:22, "vanderghast" <vanderghast@com> wrote:
> Validations inter table are generally made trough data relation.
>
> As example, to be sure that a table1.f1 value is not in table2.f2, someone
> MAY come with a design implying a merge (with UNION ALL) of the two tables
> into one, where the new field f3, filled with values from f1 and f2, would
> not allow duplicated values. But that works only if table1 and table2 are
> compatible (or if they share in common a portion of the fields, ie, if 
> they
> have in common a "sub-type").
>
> With Jet 4.0, you can write more generic table validations rules, BETWEEN
> TABLES, with the CHECK constraint. There is no User Interface, though, so
> you have to use DDL-SQL. As example, to enforce the previous 'rule', 
> someone
> may add the following rule, to table1:
>
> CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT
> table1f1NotInTable2F2 CHECK(0=(SELECT COUNT(table2.f2) FROM table2 WHERE
> table2.f2 = table1.f1))"
>
> and, to table2:
>
> CurrentProject.Connection.Execute "ALTER TABLE table2 ADD CONSTRAINT
> table2f2NotInTable1F1 CHECK(0=(SELECT COUNT(table1.f1) FROM table1 WHERE
> table1.f1 = table2.f2))"
>
> You can drop such a CHECK constraint with (since there is no user 
> interface
> to drop it otherwise) something like :
>
> CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT
> table1f1NotInTable2F2"
>
> as example.
>
> Note that the check constraint name has to be unique, over the whole
> database.
> Note that a check constraint fires when data is appended, or modified, but
> not on deletion. The check is made with values AFTER the tried
> update/insertion is, temporary, done.
> Note that no VBA function must be involved in the CHECK constraint (ex,
> impossible to use Nz( ), while iif( ) is ok ).
> Note that MS SQL Server does not allow this kind of CHECK constraint
> involving other tables (or other rows of the same table); you would a
> trigger, instead.
>
> Vanderghast, Access MVP
>
> "atledreier" <atledre...@gmail.com> wrote in message
>
> news:7508ed8f-c33a-452e-b2dc-d79346d08c80@r34g2000yqj.googlegroups.com...
>
>
>

> > Hello.
>
> > I have a query that verifies that required fields in my records are
> > populated and correct.
> > Right now it returns the records that have required fields=NULL. some
> > of the fields need to be verified for legal values from another table
> > as well. So I need to return values where the value of field [Tag]!
> > [function_code] is not in [WM_GOC_Conversion]![Function_Code]
>
> > My current query is a datasheet with autoformat that marks the null-
> > fields with yellow background. I'd like to be able to mark the wrong
> > information with red text, but first I need to have a query that
> > return the records! :-)

0
vanderghast
5/4/2010 1:01:21 PM
Reply:

Similar Artilces:

How to get Table Description Field Text to show on Forms? Hovering? Right-Click?
Can I get the fioeld description I enter in the Table Design View to show on Forms or Queries? When the mouse hovers over the field or field name?? When the user right clicks on the field or field name?? At the bottom of the screen when the mouse hovers? How? Thanks for any help. Mel Ken, I've done some programming but not much in Access. How do I (where do I click) to add a function to the "standard module" ??? And, am I correct in understanding that the first function would be called each time the db opened? And the 2nd function would be called each time the form op...

Another Hibernation problem
Tom and al! - sorry to bother you again. My system is now quite stable but occasionally I get another unexpected shutdown soon after returning from hibernation: and this shows up in the event viewer as the event preceding the shutdown. Previously I had a video driver problem that conflicted with something. Would you now say I now have a DHCP server conflict? Regards Mervyn (follows event viewer paste) Event Type: Warning Event Source: Dhcp Event Category: None Event ID: 1003 Date: 29/01/2010 Time: 08:41:06 User: N/A...

Products/Services Table
I am using Access 2007. I have a database for my Products/Services industry. I have a table containing my products/services, as well as a Work Order Form that is linked to the Products/services table from which I can choose via drop down the products that I am selling to that customer. The Product/services table contains 3 fields; Product, Cost & Price. The Work Order Form also contains those three fields, and when I choose a product from the drop down, the corresponding Price field fills in as well. Now, sometimes (after bargaining of course!) I want to change the price for ...

Item.content field in RMS Items table
I was taking a look at the structure of the item table in RMS 1.3, Headquarters. At the end of the record is a field called "Content". It is an ntext field. I don't see anyplace on the Item Properties form where this field is referenced. Is this an obsolete field, and if so, would it be safe for me to utilize this field for some of my custom information? -- Bill Yater The Worth Collection byater@worthltd.com Bill, There are a few fields in the Item table that either have never been implemented or are no longer in use. It should be safe to use contents. We have used th...

Formatting data table
I have a data table shown under a diagram. However it won't display all the decimals. I.e. in the input to the diagram for january 2006 is 1.123, but only 1.1 is shown in the data table. The only solution seems to be to enlarge the diagram but this is not an option in this particular case! As a last resort I could embed a table under a traditional diagram without the data table, but that is second best. Looking forward to any help If you right click on the data table you can change the font size by clicking on 'format data table' and the font tab. You can type in the size ...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Tables in Publisher
I'd like to set up a table in Publisher with lines on the bottom of every cell - like a list. When I select the table and ask for a line on the bottom, it only gives me a line on the bottom border of the whole table. Is there a quick way to get a line on the bottom of every cell without having to select every single cell and add the line? Thanks. silver wrote: > I'd like to set up a table in Publisher with lines on the bottom of every > cell - like a list. When I select the table and ask for a line on the bottom, > it only gives me a line on the bottom border of the who...

Processing Functions in a Table
Hi, I'm running Access 2003. I have a table that contains the following information: Item Count --- --- Postings =DCount("*","tblDocBoat","[ToPost]=True") Floating Copies =DCount("*","tblFloatingCopy","[Approved] = False") Releases =DCount("*","tblReleaseReq","[Processed] = False") Is there a way when I can get the functions to process, rather than showing the code, when viewing the table through a form or simply viewing the table? i.e: Item Count --- --- Po...

Block entry into a cell if another cell has a value
I am working on a W-4 Federal Tax Form created in Excel. I want to be able to block an entry in one cell (Total number of allowances) if another cell (Write *Exempt* here) has a value, specifically Exempt, in it and vise versa if the cell (Total number of allowances) has a value, specifically a number, in it I want block any entry into the cell (Write *Exempt* here) I hope this makes sense to somebody out there. Old Navy, You can use Data - Validation. I'll use A1 and B1. Select A1. Data Validation - Custom: =B1<>"" If you want to appear a little more refined, you ca...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

I want to allow mail forwarding for another domain
I have a user setup in AD with mail forwarding to an external contact/email. Only the users added to the "Accept messages from" are able to send mail using my smtp connector, so when an internal user sends mail, it forwards properly. When an external domain sends mail to this user, it does not forward. I cannot add a @domain.com to the list of Accept messages from. I tried adding the domain to the recipient policy but it doesn't work. Any help appreciated. Gruen What you're observing is by design because external mail isn't authenticated. -- Ed Crowley MVP - Excha...

How can I copy a macro into another workbook?
I have a workbook with macro already assigned, how can I copy this into another workbook? Basically you just open the module in which the macro resides and select the text from Sub macro() to End Sub. Copy it and paste into a module in your other workbook. But......nothing is rarely that simple<g> Is the macro workbook-specifice or worksheet-specific? Does it refer to named ranges in the original workbook? If you could post the code perhaps we could get a better idea of how to copy and edit if needed. Gord Dibben MS Excel MVP On Wed, 14 Mar 2007 13:46:18 -0700, jbeene <jbe...

Moving SQL Databases to another drive results in error
I am trying to move the CRM SQL Databases to another drive that has more space and I get a replication error. Is there any documentation to help me move these successfully?? McKenzie This is a multi-part message in MIME format. ------=_NextPart_000_005D_01C5A44F.29F39C90 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable you can do this but you need to disable replication and re-enable = afterwards Use SQL Enterprise manager, select the database you want to move and = then=20 choose the option (after right click) to detach the database,then ...

Create appointments for another user
Is it possible to create an appointment from another user. I need to be able to create a case for a CSR and if necessary, also create an appointment on his/ner calander. Thanks in advance. -Chris Chris, You can create the appointment or case, after you created it (for yourself) you can assign it to someone else or to a queue (if you have enough rights for it). The assign option should be in the action menu. Hope this helps, Rob Bakkers Avanade Netherlands "Chris Pelon" wrote: > Is it possible to create an appointment from another user. I need to be able > to creat...

Creating data in one from from another
I have a DB that has two seperate data forms i want to be able to open one form from the other and insert some detaisl from the first form into it. eg. Form one has asset field there is a button to open second form i want it to link with the form record and input the asset number. -- Thanks for you help One way of doing this is by 'pushing' the value like this: DoCmd.OpenForm "Firstformname", acNormal Forms!Secondformname.Fieldname = Forms!firstformname.Form.fieldname Replace the firstformname with your mainform and the fieldname you want to push (e.g. assetnumber...

Message to signal the event you have switch to another program ====
Message to signal the event you have switch to another program ==== Hi, I want to be to capture the event that the user has either switch to another program, or the current program has "lost focused" either because the user has mouse clicked outside the current program or has minimized it. How should I do this? Is there a windows message for this? What type of application, dialog or doc/view? take a look at WM_ACTIVATEAPP notification, wparam = FALSE means deactivated AliR. <ratcharit@gmail.com> wrote in message news:1140041070.660365.71960@g43g2000cwa.googlegroups.com......

APA Style Table in Excel
Is there a way to left align digits in excel and still have decimal places in line? Example: Instead of: 0022.11 1122.01 Have: 22.11 1122.01 Thanks, Dan One way: Format/Cells/Number/Custom ???0.00 Left align the cells. In article <s0Gsb.21268$8x2.8238628@newssrv26.news.prodigy.com>, "Dan" <dslaugh@yahoo.com> wrote: > Is there a way to left align digits in excel and still have decimal places > in line? > > Example: > > Instead of: > > 0022.11 > 1122.01 > > Have: > > 22.11 > 1122.01 > > Thanks, &g...

Table Borders
I am new to Publisher and I need to create a table with double line borders. I can't seem to figure out how to do this. When I go to line style the double line is greyed out and if I go into format table the option to have a double in is not there. Is it possible to have a table with double line borders? Thanks in advance for your help. Regards, Dee If you simply want a double border around the table and not the cells, draw a box around the table. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dee" <Dee@disc...

Move incoming mail to another mailbox
Hi all! One of our employees is leaving the organisation. It is possible that we will be recieving mail on his address so we want to move all mail on former.employee@our.company to another mailbox. I would of course like to this on the server but I can't find any goodies about this in the system manager. Would also like to create an auto-reply on this email address stating that the employee no longer work for us. Thanks in advance. Regards - Jonas Sackerud Two choices as I see it: 1. Keep the mailbox and simply grant another user access to the mailbox so that new mail can be ch...

Need to normalize some tables after database has been split
Hi there I realize that I have to normalize some of my tables in my database but I have split the database. My question is, what is the best way to do this? All these tables have been referenced in the forms in the database. How do I change the forms without recreating them all over again? Thank you for any help in this matter. On Wed, 19 May 2010 22:24:01 -0700, forest8 <forest8@discussions.microsoft.com> wrote: >Hi there > >I realize that I have to normalize some of my tables in my database but I >have split the database. > >My questio...

Another simple "average of..."?
(Using Excel 2002) Is there a way to have Excel automatically enter the number of cell (with entries in them) into another cell. A simple example: Let's say I have values entered into D5, D6, and D7, but D8-D14 are blank. I have the total number of entries in D15 now as 3 (3 entries are filled in), but I'd like that to change (to 4, as 4 entries are no filled in) automatically when I enter a value into D8. Then when I enter another value into D9, I'd like D15 to change to "5", and so on... Is there a way Excel can do this? My spreadsheet is more complex or I woul...

summary table that updates.....
I need a spreadsheet that updates a master list I have in another sheet in the same workbook that updates itself once I have made a change to the master list. for example: master list: rcp 24" 300 lf mh 5' 1 ea inlet type R 2 ea rcp 24" 50 lf inlet type R 4 ea mh 4' 2 ea mh 5' 5 ea rcp 30" 300 lf summary list: rcp 24" 350 lf rcp 30" 300 lf mh 5' 6 ...

Percents in Pivot Table
Hi. I have a pivot table Month Billing Category Count of Incoming Imei Sum of Billing Amount of Incoming Imei % of Billing Amount August Bounce 3 $300 0.03% 0.04% MIN 1,027 $30,810 10.60% 4.40% TIER1 15 $595 0.15% 0.09% TIER2 163 $13,760 1.68% 1.97% TIER3 397 $49,980 4.10% 7.14% TIER4 2 $555 0.02% 0.08% TIER5 3 $400 0.03% 0.06% August Total 1,610 $96,400 16.61% 13.78% September MIN 1,163 $34,890 12.00% 4.99% TIER1 22 $840 0.23% 0.12% TIER2 471 $40,545 4.86% 5.79% TIER3 674 $86,685 6.95% 12.39% TIER4 3 $1,290 0.03% 0.18% TIER5 4 $575 0.04% 0.08% September Total 2,337 $164,825 24.1...

copy rows to another workbook
Hi, I'm stuck with this problem: I would speed my daily work if I could automatically copy some rows of my current worksheet (a table with 20 columns and 100 rows) in another workbook. I thought to create a VBA macro that 1 - triggers when a button is pressed 2 - ask user for the name and location of the new workbook (a dialog box)? 3 - create such workbook 4 - copy there the rows with a "S" on the first cell of every row (column A) 5 - automatically reset the column A to blank cells. Any hints or link to some VBA code to look at? Thanks Monica How about this: Record a...

FilteredActivityParty table in RS report
When using RS to query the CRM database, I occasionally need the FilteredActivityParty table to find records in which certain users are participating. However, because of the JOIN with FilteredActivityPointer and the Party table being a N:N table, performance of my reports goes down the drain. Does anyone have tips to use the Party table w/o losing too much performance? On Apr 26, 4:17=A0am, Daniel <Dan...@discussions.microsoft.com> wrote: > When using RS to query the CRM database, I occasionally need the > FilteredActivityParty table to find records in which certain users ...