Table and query question

I would like to create a table that will relate an account to its parent(s).
1 account can have multiple parents, and 1 parent can have multiple 
accounts.
And, a parent can have a master parent.
For ex:
Account 1 can have Parent 1.
Account 2 can have Parent 1 and Parent 2.
Parent 1 can have Parent 2.

And, the queries that I will perform will request these data:
1. give me all parents of Account 1. The query should return Parent 1 and 
Parent 2 (because Parent 2 is the parent of Parent 1)
2. give me all parents of Account 2. The query should return Parent 1 and 
Parent 2
3. give me all parent sof Parent 1. The query should return Parent 2
4. give me all parent sof Parent 2. The query should return NULL

I was thinking to have a table that has 2 columns: Account and Parent, so in 
the above example the table will look like this:
Parent    Account
P1         A1
P1        A2
P2        A2
P2        P1
Is that the best table that can reflect it ? And, how can I return the 
result for question 1 (give me all parents of Account 1) ?

Thank you. 


0
fniles
3/12/2010 4:24:54 PM
sqlserver.programming 1873 articles. 0 followers. Follow

15 Replies
783 Views

Similar Articles

[PageSpeed] 28

Are Parent's parent Accounts?  Or are Parents some other entity, such
as a company?  What probably matters more is what you're going to
store about the parents and the accounts.  If they're all just
accounts, you can do it with one Account table and one cross reference
table.  If Parents are something other than Accounts, then it gets a
little more complicated.

Assuming Parents are just other accounts, here is what you need.  If
an account can have only one immediate parent, then your design is
fine.  If an Account can have multiple immediate parents, then your
design may not work.

If an Account can have only one immediate parent account...

Create Table tblAccount
(
AccountID INT
Parent_AccountID INT
)

....would do the trick with a foriegn key relationship between
Parent_AccountID back to AccountID.

If an account can have multiple immediate parent accounts... you need
two tables.  One with just the account information and Account ID and
another like the one above called something like tblAccountParent that
relates to the account table two times.

-Eric Isaacs
0
Eric
3/12/2010 8:45:03 PM
Please define an Account and a Parent, telling us why and how they are
totally different entities. If they were the same entity, they would
have one and only one name.
0
CELKO
3/13/2010 6:13:59 PM
Thank you for your reply.

Parents are also Accounts, but an account can have multiple immediate parent 
accounts.

So, I will create 2 tables like this:
Create table tblAccount
(
AccountID INT
AccountName varchar(10)
)

ALTER TABLE tblAccount
        ADD PRIMARY KEY (AccountID)

Create table tblAccountParent
(
AccountID INT
Parent_AccountID INT
)

 ALTER TABLE tblAccountParent
        ADD FOREIGN KEY (AccountID, Parent_AccountID)
                              REFERENCES tblAccount(AccountID,AccountID)

ALTER TABLE tblAccountParent
        ADD PRIMARY KEY (AccountID,Parent_AccountID)

Say, tblAccount looks like this:
AccountID    AccountName
A1                NameA1
A2                NameA2
P1                NameP1
P2                NameP2

and tblAccountParent looks like this:
AccountID    Parent_AccountID
A1                P1
A2                P1
A2                P2
P1                P2

How can I write a query that will give me all parents of A1 ? The query 
should return P1 and
P2 (because P2 is the parent of P1)

Thank you.


"Eric Isaacs" <eisaacs@gmail.com> wrote in message 
news:df9f95a1-8b5b-42cf-9cf3-bd51ad624fd6@u15g2000prd.googlegroups.com...
> Are Parent's parent Accounts?  Or are Parents some other entity, such
> as a company?  What probably matters more is what you're going to
> store about the parents and the accounts.  If they're all just
> accounts, you can do it with one Account table and one cross reference
> table.  If Parents are something other than Accounts, then it gets a
> little more complicated.
>
> Assuming Parents are just other accounts, here is what you need.  If
> an account can have only one immediate parent, then your design is
> fine.  If an Account can have multiple immediate parents, then your
> design may not work.
>
> If an Account can have only one immediate parent account...
>
> Create Table tblAccount
> (
> AccountID INT
> Parent_AccountID INT
> )
>
> ...would do the trick with a foriegn key relationship between
> Parent_AccountID back to AccountID.
>
> If an account can have multiple immediate parent accounts... you need
> two tables.  One with just the account information and Account ID and
> another like the one above called something like tblAccountParent that
> relates to the account table two times.
>
> -Eric Isaacs 


0
fniles
3/15/2010 2:14:23 PM
>> Parents are also accounts, .. <<

Then they go into the same table that models accounts.

>> but an account_ can have multiple immediate parent accounts. <<

That is VERY unusual; most account_ing systems have a strict hierarchy
of accounts.


>> So, I will create 2 tables like this: <<

Close, but not quite. Firs t of all, a tables models a set of things,
so it has a collective or plural name. We never put that silly "tbl-"
prefix on names; Tell me what it is and not how it is stored, like the
ISO-11179 rules say.

CREATE TABLE Accounts
(account_nbr INTEGER NOT NULL PRIMARY KEY
account_name VARCHAR (10) NOT NULL,
etc);

The structure is called a Chart of accounts by accountants:

CREATE TABLE Chart_of_Accounts
(parent_account_nbr INTEGER NOT NULL
  REFERENCES Accounts(account_nbr)
  ON UPDATE CASCADE,

 subordinate_account_nbr INTEGER NOT NULL
  REFERENCES Accounts(account_nbr)
  ON UPDATE CASCADE,

 PRIMARY KEY (parent_account_nbr, subordinate_account_nbr),

etc);

SQL Server is going to have trouble with the DRI, but this is how it
should be done. But the important principle is that you do not put
entities and relationships in the same table.
0
CELKO
3/16/2010 4:11:11 PM
Thank you for your reply.

> SQL Server is going to have trouble with the DRI
What did you mean by this ?

Also, how can I write a query that will give me all parents of A1 ? The 
query should return P1 and P2 (because P2 is the parent of P1)


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:1ba67e08-7f65-42e4-afa1-0d9e6b1ef652@k5g2000pra.googlegroups.com...
>>> Parents are also accounts, .. <<
>
> Then they go into the same table that models accounts.
>
>>> but an account_ can have multiple immediate parent accounts. <<
>
> That is VERY unusual; most account_ing systems have a strict hierarchy
> of accounts.
>
>
>>> So, I will create 2 tables like this: <<
>
> Close, but not quite. Firs t of all, a tables models a set of things,
> so it has a collective or plural name. We never put that silly "tbl-"
> prefix on names; Tell me what it is and not how it is stored, like the
> ISO-11179 rules say.
>
> CREATE TABLE Accounts
> (account_nbr INTEGER NOT NULL PRIMARY KEY
> account_name VARCHAR (10) NOT NULL,
> etc);
>
> The structure is called a Chart of accounts by accountants:
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_nbr INTEGER NOT NULL
>  REFERENCES Accounts(account_nbr)
>  ON UPDATE CASCADE,
>
> subordinate_account_nbr INTEGER NOT NULL
>  REFERENCES Accounts(account_nbr)
>  ON UPDATE CASCADE,
>
> PRIMARY KEY (parent_account_nbr, subordinate_account_nbr),
>
> etc);
>
> SQL Server is going to have trouble with the DRI, but this is how it
> should be done. But the important principle is that you do not put
> entities and relationships in the same table. 


0
fniles
3/16/2010 4:53:26 PM
>
>> SQL Server is going to have trouble with the DRI
> What did you mean by this ?

It means he gave you an answer that won't work in SQL Server.  You'll 
get used to his MO of beating you up for not asking questions to his 
liking and then giving answers that can't be implemented with the tools 
you have.

>
> Also, how can I write a query that will give me all parents of A1 ? The
> query should return P1 and P2 (because P2 is the parent of P1)
>

MSDN has a nice example that can get you started.  Although it uses an 
employee hierarchy, the concept is similar.
http://msdn.microsoft.com/en-us/library/ms186243.aspx

Works for me...

Joe De Moor

0
J
3/16/2010 5:20:09 PM
>> [SQL Server is going to have trouble with the DRI] What did you mean by this ? <<

SQL is weaker than other SQLs and has problems with cycles in the DRI
constraints. It will not like seeing two paths from Chart_of_Accounts
to Accounts. Put the constraints in place anyway and comment out one
or both of them. A common kludge is to write a trigger to do the job.
The reason for leaving the comment behind is so that the next person
maintaining your code will know why there is a trigger and what it can
be replaced with when the code is ported to a new platform or releases
of SQL Server that supports the SQL-92 or later Standards.

Write good code first, then kludge and use dialect only when you have
to.

>> Also, how can I write a query that will give me all parents of A1 ? The query should return P1 and P2 (because P2 is the parent of P1) <<

SELECT parent_account_nbr
  FROM Chart_of_Accounts
 WHERE subordinate_account_nbr = 'A1'; -- you made account numbers
INTEGER, but then look for strings?!
0
CELKO
3/17/2010 3:37:42 PM
> SQL is weaker than other SQLs and has problems with cycles in the DRI
> constraints. It will not like seeing two paths from Chart_of_Accounts
> to Accounts. Put the constraints in place anyway and comment out one
> or both of them. A common kludge is to write a trigger to do the job.
> The reason for leaving the comment behind is so that the next person
> maintaining your code will know why there is a trigger and what it can
> be replaced with when the code is ported to a new platform or releases
> of SQL Server that supports the SQL-92 or later Standards.

Yet another reason to use a surrogate key - surrogate keys do not change so 
there will only need to be one copy of the business candidate key 
account_nbr and you will not have the problem with the constraints.

IDENTITY can be used for the surrogate key because it follows the rules for 
a surrogate key, my article here: 
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/03/13/database-design-surrogate-keys-part-1-of-many-rules-for-surrogate-keys-e-f-codd-and-c-j-date-research-and-problems-they-solve.aspx 
gives all the relevant reasons and references back to Codd and Date.

CREATE TABLE Accounts
(
 AccountID INTEGER NOT NULL IDENTITY PRIMARY KEY,
 account_nbr INTEGER NOT NULL UNIQUE,
 account_name VARCHAR (10) NOT NULL);



CREATE TABLE Chart_of_Accounts
(parent_AccountID INTEGER NOT NULL
  REFERENCES Accounts( AccountID )
  ON UPDATE NO ACTION,

 subordinate_AccountID INTEGER NOT NULL
  REFERENCES Accounts( AccountID )
  ON UPDATE NO ACTION
  )

insert Accounts ( account_nbr, account_name )
    values( 1, 'some name' )
insert Accounts ( account_nbr, account_name )
    values( 2, 'other one' )

insert Chart_of_Accounts( parent_AccountID, subordinate_AccountID )
    values( 1, 2 )

select *
from Chart_of_Accounts x
    inner join Accounts par on par.AccountID = x.parent_AccountID
    inner join Accounts sub on sub.AccountID = x.subordinate_AccountID

update Accounts
    set account_nbr = account_nbr + 10

--  relationship is intact....
select *
from Chart_of_Accounts x
    inner join Accounts par on par.AccountID = x.parent_AccountID
    inner join Accounts sub on sub.AccountID = x.subordinate_AccountID


--ROGGIE--



"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:5cd32606-e92d-46ad-ad94-cfa7f01e0462@q2g2000pre.googlegroups.com...
>>> [SQL Server is going to have trouble with the DRI] What did you mean by 
>>> this ? <<
>
> SQL is weaker than other SQLs and has problems with cycles in the DRI
> constraints. It will not like seeing two paths from Chart_of_Accounts
> to Accounts. Put the constraints in place anyway and comment out one
> or both of them. A common kludge is to write a trigger to do the job.
> The reason for leaving the comment behind is so that the next person
> maintaining your code will know why there is a trigger and what it can
> be replaced with when the code is ported to a new platform or releases
> of SQL Server that supports the SQL-92 or later Standards.
>
> Write good code first, then kludge and use dialect only when you have
> to.
>
>>> Also, how can I write a query that will give me all parents of A1 ? The 
>>> query should return P1 and P2 (because P2 is the parent of P1) <<
>
> SELECT parent_account_nbr
>  FROM Chart_of_Accounts
> WHERE subordinate_account_nbr = 'A1'; -- you made account numbers
> INTEGER, but then look for strings?! 

0
Tony
3/17/2010 3:56:02 PM
Thank you.

This is how Accounts look like:
account     account_name
A1             A1 name
A2             A2 name
B1             B1 name
B2             B2 name

This is how Chart_of_Accounts look like:
parent_Account     subordinate_Account
B1                         A1
B1                         A2
B2                         A2
B2                         B1

When I run this query, it only returns B1 instead of B1 AND B2.
SELECT parent_Account FROM Chart_of_Accounts WHERE subordinate_Account = 
'A1'


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:5cd32606-e92d-46ad-ad94-cfa7f01e0462@q2g2000pre.googlegroups.com...
>>> [SQL Server is going to have trouble with the DRI] What did you mean by 
>>> this ? <<
>
> SQL is weaker than other SQLs and has problems with cycles in the DRI
> constraints. It will not like seeing two paths from Chart_of_Accounts
> to Accounts. Put the constraints in place anyway and comment out one
> or both of them. A common kludge is to write a trigger to do the job.
> The reason for leaving the comment behind is so that the next person
> maintaining your code will know why there is a trigger and what it can
> be replaced with when the code is ported to a new platform or releases
> of SQL Server that supports the SQL-92 or later Standards.
>
> Write good code first, then kludge and use dialect only when you have
> to.
>
>>> Also, how can I write a query that will give me all parents of A1 ? The 
>>> query should return P1 and P2 (because P2 is the parent of P1) <<
>
> SELECT parent_account_nbr
>  FROM Chart_of_Accounts
> WHERE subordinate_account_nbr = 'A1'; -- you made account numbers
> INTEGER, but then look for strings?! 


0
fniles
3/17/2010 7:11:57 PM
>> Yet another reason to use a surrogate key - surrogate keys do not change=
 so
there will only need to be one copy of the business candidate key
account_nbr and you will not have the problem with the constraints. <<

I think you need to re-name the columns to reflect what they mean in
your data model. IDENTITY is not an identifier, but a surrogate in
Date's sense (not Codd's):

CREATE TABLE Accounts
(account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
 account_nbr INTEGER NOT NULL UNIQUE, =96- can be referenced
 account_name VARCHAR (10) NOT NULL);

Which would give us this non-table. It has no key!

CREATE TABLE Chart_of_Accounts
(parent_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION,

 subordinate_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION);

Did you mean to have =93PRIMARY KEY (subordinate_account_surrogate,
parent_account_surrogate)=94 in there? Or were you goignto add yet
another IDENTITY column? Or both?

But aside from that, this is not a good example. You added a needless
extra column to accounts and dropped the DRI actions that would be
part of the specs for the Chart of Accounts.  But even worse than
that, consider these statements, which use the ANSI Standard set-
oriented syntax instead of the old Sybase/SQL Server row-by-agonizing-
row restrictions for INSERT INTO:

INSERT INTO Accounts (account_nbr, account_name)
VALUES(1, 'some name'),(2, 'other one');

Accounts
account_surrogate  account_nbr  account_name
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1                      1        'some name'
2                      2        'other one'

Or equally valid, we might have:

Accounts
account_surrogate  account_nbr  account_name
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1                      2        'other one'
2                      1        'some name'

It all depends on the physical state of the machine, and not the
logical model. In fact, we could also write the equivalent statement:

INSERT INTO Accounts (account_nbr, account_name)
VALUES (2, 'other one'), (1, 'some name');

Given a set of (n) accounts, we have (n!) possible orderings. The
means that the next statement is non-deterministic.

INSERT INTO Chart_of_Accounts(parent_account_surrogate,
subordinate_account_surrogate)
VALUES(1, 2); =96- maybe right, maybe not

CREATE TABLE Accounts
(account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
 account_nbr INTEGER NOT NULL UNIQUE,
 account_name VARCHAR (10) NOT NULL);

CREATE TABLE Chart_of_Accounts
(parent_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION,

 subordinate_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION);

INSERT INTO Accounts (account_nbr, account_name)
    VALUES(1, 'some name'),(2, 'other one');

INSERT INTO Chart_of_Accounts (parent_account_surrogate,
subordinate_account_surrogate)
    VALUES(1, 2);

The query you gave seems a bit much, with SELECT *; could we use this
instead?:

SELECT PAR.account_name AS parent_acct, SUB.account_name AS
subordinate_acct
FROM Chart_of_Accounts AS X
     INNER JOIN
     Accounts AS PAR
     ON PAR.account_surrogate =3D X.parent_account_surrogate
        INNER JOIN
        Accounts AS SUB
        ON SUB.account_surrogate =3D X.subordinate_account_surrogate;

Your next statement:

UPDATE Accounts
   SET account_nbr =3D account_nbr + 10;

Will work with an ON UPDATE CASCADE, not have the overhead of extra
columns and express the intended business rule about the Chart of
Accounts and Accounts.

As an aside, Date and Codd both do not care about implementations and
both do not like SQL (Date uses Tutorial D and Codd wrote Alpha). If
you side with Date, then you have to give up NULLs; if you side with
Codd, then you have two kinds of NULLs.

But if you look at Sybase SQL Anywhere (nee WATCOM SQL), you will see
a DRI  implementation done right. The UNIQUE and PRIMARY KEY values
appear only once in their base table. References to the PK are done
with pointer chains that link the FK to that single appearance. Update
and delete cascades are immediate; we are good at chasing pointer
chains from pre-relational DB and file system technology.

Finally, a transaction that should logically do nothing to Accounts
has overhead and will generate a warning:

BEGIN ATOMIC
DELETE FROM Accounts WHERE account_name =3D 'some name'; =96- remove
INSERT INTO Accounts (account_nbr, account_name)
VALUES(1, 'some name');  =96- restore
END;'

This gets tricky in Standard SQL, since constraints can be deferred
and the Chart of Accounts can have various DRI actions. but the
IDENTITY value in Accounts will change because it is tied to the
physical table insertion.

0
CELKO
3/17/2010 8:13:23 PM
>> Yet another reason to use a surrogate key - surrogate keys do not change=
 so
there will only need to be one copy of the business candidate key
account_nbr and you will not have the problem with the constraints. <<

I think you need to re-name the columns to reflect what they mean in
your data model. IDENTITY is not an identifier, but a surrogate in
Date's sense (not Codd's):

CREATE TABLE Accounts
(account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
 account_nbr INTEGER NOT NULL UNIQUE, =96- can be referenced
 account_name VARCHAR (10) NOT NULL);

Which would give us this non-table. It has no key!

CREATE TABLE Chart_of_Accounts
(parent_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION,

 subordinate_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION);

Did you mean to have =93PRIMARY KEY (subordinate_account_surrogate,
parent_account_surrogate)=94 in there? Or were you goignto add yet
another IDENTITY column? Or both?

But aside from that, this is not a good example. You added a needless
extra column to accounts and dropped the DRI actions that would be
part of the specs for the Chart of Accounts.  But even worse than
that, consider these statements, which use the ANSI Standard set-
oriented syntax instead of the old Sybase/SQL Server row-by-agonizing-
row restrictions for INSERT INTO:

INSERT INTO Accounts (account_nbr, account_name)
VALUES(1, 'some name'),(2, 'other one');

Accounts
account_surrogate  account_nbr  account_name
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1                      1        'some name'
2                      2        'other one'

Or equally valid, we might have:

Accounts
account_surrogate  account_nbr  account_name
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1                      2        'other one'
2                      1        'some name'

It all depends on the physical state of the machine, and not the
logical model. In fact, we could also write the equivalent statement:

INSERT INTO Accounts (account_nbr, account_name)
VALUES (2, 'other one'), (1, 'some name');

Given a set of (n) accounts, we have (n!) possible orderings. The
means that the next statement is non-deterministic.

INSERT INTO Chart_of_Accounts(parent_account_surrogate,
subordinate_account_surrogate)
VALUES(1, 2); =96- maybe right, maybe not

CREATE TABLE Accounts
(account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
 account_nbr INTEGER NOT NULL UNIQUE,
 account_name VARCHAR (10) NOT NULL);

CREATE TABLE Chart_of_Accounts
(parent_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION,

 subordinate_account_surrogate INTEGER NOT NULL
  REFERENCES Accounts(account_surrogate)
  ON UPDATE NO ACTION);

INSERT INTO Accounts (account_nbr, account_name)
    VALUES(1, 'some name'),(2, 'other one');

INSERT INTO Chart_of_Accounts (parent_account_surrogate,
subordinate_account_surrogate)
    VALUES(1, 2);

The query you gave seems a bit much, with SELECT *; could we use this
instead?:

SELECT PAR.account_name AS parent_acct, SUB.account_name AS
subordinate_acct
FROM Chart_of_Accounts AS X
     INNER JOIN
     Accounts AS PAR
     ON PAR.account_surrogate =3D X.parent_account_surrogate
        INNER JOIN
        Accounts AS SUB
        ON SUB.account_surrogate =3D X.subordinate_account_surrogate;

Your next statement:

UPDATE Accounts
   SET account_nbr =3D account_nbr + 10;

Will work with an ON UPDATE CASCADE, not have the overhead of extra
columns and express the intended business rule about the Chart of
Accounts and Accounts.

As an aside, Date and Codd both do not care about implementations and
both do not like SQL (Date uses Tutorial D and Codd wrote Alpha). If
you side with Date, then you have to give up NULLs; if you side with
Codd, then you have two kinds of NULLs.

But if you look at Sybase SQL Anywhere (nee WATCOM SQL), you will see
a DRI  implementation done right. The UNIQUE and PRIMARY KEY values
appear only once in their base table. References to the PK are done
with pointer chains that link the FK to that single appearance. Update
and delete cascades are immediate; we are good at chasing pointer
chains from pre-relational DB and file system technology.

Finally, a transaction that should logically do nothing to Accounts
generates a warning by destroying an IDENTITY value and creating a new
one.

BEGIN ATOMIC
DELETE FROM Accounts WHERE account_name =3D 'some name'; =96- remove
INSERT INTO Accounts (account_nbr, account_name)
VALUES(1, 'some name');  =96- restore
END;'

0
CELKO
3/17/2010 8:16:45 PM
> I think you need to re-name the columns to reflect what they mean in
> your data model. IDENTITY is not an identifier, but a surrogate in
> Date's sense (not Codd's):

There is no need to rename anything - explain why.

The surrogate key is part of the logical model otherwise it would break
Codds Information Principle.

> Which would give us this non-table. It has no key!
> CREATE TABLE Accounts
> (AccountID INTEGER NOT NULL IDENTITY PRIMARY KEY,
> account_nbr INTEGER NOT NULL UNIQUE, �- can be referenced
> account_name VARCHAR (10) NOT NULL);

Huh? A table is only a table so long as it has a key.

There is one business key on this table - account_nbr, there is also a
surrogate key on this table AccountID.

Why do you think this is not a table? What part of the Relational Model
aspires you to think the above is not a table?

> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION);

Oh - one mo, did you mean there is no candidate key on Chart_of_Accounts?
What sort of database professional are you if you cannot recognise the
candidate key on this table is parent_AccountID, subordinateAccountID -
after all, this represents there cannot be more than one
subordinate_AccountID per parent_AccountID

The rest of your post is just b0ll0cks aiming to distract from the actual
point in hand that you do not understand nor accept surrogate keys.

I'll use as proof your miscomprehension and utter lack of ability in
understanding a surrogate key, though I will acknowledge that finally and we
are talking finally you accept the IDENTITY property is not a manifestation
of the physical hardware (although you have published that "fact" you
completely failed to research (ring a bell?).

> Finally, a transaction that should logically do nothing to Accounts
> generates a warning by destroying an IDENTITY value and creating a new
> one.
>
> BEGIN ATOMIC
> DELETE FROM Accounts WHERE account_name = 'some name'; �- remove
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES(1, 'some name');  �- restore
> END;'

Yet again you provide proof you've not understand --> AT ALL <-- surrogate
keys because you provide what you think is supporting facts for your
anti-surrogate rants; however, your example shows one thing that surrogates
NEVER give the same value - thank you for the example but there is no need
because the majority of folk on this forum know that the IDENTITY property
will NEVER give the same value twice unless a database administrator
physically reseeds the IDENTITY property.

Seriously - you are trying to bout above your weight - you will never win on
this one - you are too arrogant and have just spent the last 20 years basing
your books and papers on something that is completely un-true - that is what
happens when you think you know everything. Actually you know very little in
terms of Database theory - that is quite evident the more I delve into Codd
and Date's work - I would suggest you do the same otherwise your
embarrassment on this forum will increase - I'm not moving, perhaps now is
the time for you to s0d off and pester another product?

--ROGGIE--


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:7125e975-9f61-49bf-8bcd-bac57b8be261@l24g2000prh.googlegroups.com...
>>> Yet another reason to use a surrogate key - surrogate keys do not change
>>> so
> there will only need to be one copy of the business candidate key
> account_nbr and you will not have the problem with the constraints. <<
>
> I think you need to re-name the columns to reflect what they mean in
> your data model. IDENTITY is not an identifier, but a surrogate in
> Date's sense (not Codd's):
>
> CREATE TABLE Accounts
> (account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
> account_nbr INTEGER NOT NULL UNIQUE, �- can be referenced
> account_name VARCHAR (10) NOT NULL);
>
> Which would give us this non-table. It has no key!
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION);
>
> Did you mean to have �PRIMARY KEY (subordinate_account_surrogate,
> parent_account_surrogate)� in there? Or were you goignto add yet
> another IDENTITY column? Or both?
>
> But aside from that, this is not a good example. You added a needless
> extra column to accounts and dropped the DRI actions that would be
> part of the specs for the Chart of Accounts.  But even worse than
> that, consider these statements, which use the ANSI Standard set-
> oriented syntax instead of the old Sybase/SQL Server row-by-agonizing-
> row restrictions for INSERT INTO:
>
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES(1, 'some name'),(2, 'other one');
>
> Accounts
> account_surrogate  account_nbr  account_name
> ============================================
> 1                      1        'some name'
> 2                      2        'other one'
>
> Or equally valid, we might have:
>
> Accounts
> account_surrogate  account_nbr  account_name
> ============================================
> 1                      2        'other one'
> 2                      1        'some name'
>
> It all depends on the physical state of the machine, and not the
> logical model. In fact, we could also write the equivalent statement:
>
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES (2, 'other one'), (1, 'some name');
>
> Given a set of (n) accounts, we have (n!) possible orderings. The
> means that the next statement is non-deterministic.
>
> INSERT INTO Chart_of_Accounts(parent_account_surrogate,
> subordinate_account_surrogate)
> VALUES(1, 2); �- maybe right, maybe not
>
> CREATE TABLE Accounts
> (account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
> account_nbr INTEGER NOT NULL UNIQUE,
> account_name VARCHAR (10) NOT NULL);
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION);
>
> INSERT INTO Accounts (account_nbr, account_name)
>    VALUES(1, 'some name'),(2, 'other one');
>
> INSERT INTO Chart_of_Accounts (parent_account_surrogate,
> subordinate_account_surrogate)
>    VALUES(1, 2);
>
> The query you gave seems a bit much, with SELECT *; could we use this
> instead?:
>
> SELECT PAR.account_name AS parent_acct, SUB.account_name AS
> subordinate_acct
> FROM Chart_of_Accounts AS X
>     INNER JOIN
>     Accounts AS PAR
>     ON PAR.account_surrogate = X.parent_account_surrogate
>        INNER JOIN
>        Accounts AS SUB
>        ON SUB.account_surrogate = X.subordinate_account_surrogate;
>
> Your next statement:
>
> UPDATE Accounts
>   SET account_nbr = account_nbr + 10;
>
> Will work with an ON UPDATE CASCADE, not have the overhead of extra
> columns and express the intended business rule about the Chart of
> Accounts and Accounts.
>
> As an aside, Date and Codd both do not care about implementations and
> both do not like SQL (Date uses Tutorial D and Codd wrote Alpha). If
> you side with Date, then you have to give up NULLs; if you side with
> Codd, then you have two kinds of NULLs.
>
> But if you look at Sybase SQL Anywhere (nee WATCOM SQL), you will see
> a DRI  implementation done right. The UNIQUE and PRIMARY KEY values
> appear only once in their base table. References to the PK are done
> with pointer chains that link the FK to that single appearance. Update
> and delete cascades are immediate; we are good at chasing pointer
> chains from pre-relational DB and file system technology.
>
> Finally, a transaction that should logically do nothing to Accounts
> generates a warning by destroying an IDENTITY value and creating a new
> one.
>
> BEGIN ATOMIC
> DELETE FROM Accounts WHERE account_name = 'some name'; �- remove
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES(1, 'some name');  �- restore
> END;'
>

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:7125e975-9f61-49bf-8bcd-bac57b8be261@l24g2000prh.googlegroups.com...
>>> Yet another reason to use a surrogate key - surrogate keys do not change 
>>> so
> there will only need to be one copy of the business candidate key
> account_nbr and you will not have the problem with the constraints. <<
>
> I think you need to re-name the columns to reflect what they mean in
> your data model. IDENTITY is not an identifier, but a surrogate in
> Date's sense (not Codd's):
>
> CREATE TABLE Accounts
> (account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
> account_nbr INTEGER NOT NULL UNIQUE, �- can be referenced
> account_name VARCHAR (10) NOT NULL);
>
> Which would give us this non-table. It has no key!
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION);
>
> Did you mean to have �PRIMARY KEY (subordinate_account_surrogate,
> parent_account_surrogate)� in there? Or were you goignto add yet
> another IDENTITY column? Or both?
>
> But aside from that, this is not a good example. You added a needless
> extra column to accounts and dropped the DRI actions that would be
> part of the specs for the Chart of Accounts.  But even worse than
> that, consider these statements, which use the ANSI Standard set-
> oriented syntax instead of the old Sybase/SQL Server row-by-agonizing-
> row restrictions for INSERT INTO:
>
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES(1, 'some name'),(2, 'other one');
>
> Accounts
> account_surrogate  account_nbr  account_name
> ============================================
> 1                      1        'some name'
> 2                      2        'other one'
>
> Or equally valid, we might have:
>
> Accounts
> account_surrogate  account_nbr  account_name
> ============================================
> 1                      2        'other one'
> 2                      1        'some name'
>
> It all depends on the physical state of the machine, and not the
> logical model. In fact, we could also write the equivalent statement:
>
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES (2, 'other one'), (1, 'some name');
>
> Given a set of (n) accounts, we have (n!) possible orderings. The
> means that the next statement is non-deterministic.
>
> INSERT INTO Chart_of_Accounts(parent_account_surrogate,
> subordinate_account_surrogate)
> VALUES(1, 2); �- maybe right, maybe not
>
> CREATE TABLE Accounts
> (account_surrogate INTEGER NOT NULL IDENTITY PRIMARY KEY,
> account_nbr INTEGER NOT NULL UNIQUE,
> account_name VARCHAR (10) NOT NULL);
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
>  REFERENCES Accounts(account_surrogate)
>  ON UPDATE NO ACTION);
>
> INSERT INTO Accounts (account_nbr, account_name)
>    VALUES(1, 'some name'),(2, 'other one');
>
> INSERT INTO Chart_of_Accounts (parent_account_surrogate,
> subordinate_account_surrogate)
>    VALUES(1, 2);
>
> The query you gave seems a bit much, with SELECT *; could we use this
> instead?:
>
> SELECT PAR.account_name AS parent_acct, SUB.account_name AS
> subordinate_acct
> FROM Chart_of_Accounts AS X
>     INNER JOIN
>     Accounts AS PAR
>     ON PAR.account_surrogate = X.parent_account_surrogate
>        INNER JOIN
>        Accounts AS SUB
>        ON SUB.account_surrogate = X.subordinate_account_surrogate;
>
> Your next statement:
>
> UPDATE Accounts
>   SET account_nbr = account_nbr + 10;
>
> Will work with an ON UPDATE CASCADE, not have the overhead of extra
> columns and express the intended business rule about the Chart of
> Accounts and Accounts.
>
> As an aside, Date and Codd both do not care about implementations and
> both do not like SQL (Date uses Tutorial D and Codd wrote Alpha). If
> you side with Date, then you have to give up NULLs; if you side with
> Codd, then you have two kinds of NULLs.
>
> But if you look at Sybase SQL Anywhere (nee WATCOM SQL), you will see
> a DRI  implementation done right. The UNIQUE and PRIMARY KEY values
> appear only once in their base table. References to the PK are done
> with pointer chains that link the FK to that single appearance. Update
> and delete cascades are immediate; we are good at chasing pointer
> chains from pre-relational DB and file system technology.
>
> Finally, a transaction that should logically do nothing to Accounts
> generates a warning by destroying an IDENTITY value and creating a new
> one.
>
> BEGIN ATOMIC
> DELETE FROM Accounts WHERE account_name = 'some name'; �- remove
> INSERT INTO Accounts (account_nbr, account_name)
> VALUES(1, 'some name');  �- restore
> END;'
> 
0
Tony
3/18/2010 7:49:43 AM
>> There is no need to rename anything - explain why. <<

ISO-11179 rules and basic data modeling say that a data element is
named for what it is by its nature. If you meant the postfix =93-ID=94 to
mean IDENTITY, that is like putting =93int-=94 or other data type
information in a name. If you meant it to be =93identifier=94 then it is
wrong, because the Account number is the only attribute that
identifies an account in the Accounts table. You did say that you
wanted to use it as as surrogate, so tell us that is what it is.

>> There is one business key on this table - account_nbr, there is also a s=
urrogate key on this table AccountID [sic]. <<

Business key? You have not spent much time with Date yet! For him,
there are keys, primary keys, surrogate keys, super keys and maybe
some other flavors, but business key is not one of his terms.

>> Why do you think this is not a table? <<

Because this creates duplicate rows:
INSERT INTO Chart_of_Accounts VALUES (42,43), (42,43);

Because this creates an invalid relatiosnhip
INSERT INTO Chart_of_Accounts VALUES (42,42);

And there is there are missing constraints. I think that you should
have at least had this:

CREATE TABLE Chart_of_Accounts
(parent_account_surrogate INTEGER NOT NULL
 REFERENCES Accounts(account_surrogate)
 ON UPDATE NO ACTION,

subordinate_account_surrogate INTEGER NOT NULL
 REFERENCES Accounts(account_surrogate)
 ON UPDATE NO ACTION,

PRIMARY KEY (parent_account_surrogate, subordinate_account_surrogate),
CONSTRAINT No_Self_Subordinates
CHECK (parent_account_surrogate <> subordinate_account_surrogate)
);

I would cascade updates and deletes. I would also use the nested sets
model to avoid longer cycles than the simple case covered by
No_Self_Subordinates, but that is another topic.

>> Oh - one mo, did you mean there is no candidate key on Chart_of_Accounts=
? What sort of database professional are you if you cannot recognize the ca=
ndidate key on this table is parent_AccountID, subordinateAccountID - after=
 all, this represents there cannot be more than one subordinate_AccountID p=
er parent_AccountID <<

What kind of programmer are you if you expect the computer to add code
you never wrote? You need to fix the DDL.

>> The rest of your post is just b0ll0cks aiming to distract from the actua=
l point in hand that you do not understand nor accept surrogate keys. <<

I have a good understanding of most access methods in use. And I don't
mind a surrogate in Codd's terms. But if you wanted to make a case for
surrogacy, this is a terrible example because of the simple natural
key. A better example would be a data warehouse table with a multiple
column natural key and a perfect or (better still) minimal perfect
hashing function.

>> we are talking finally you accept the IDENTITY property is not a manifes=
tation of the physical hardware (although you have published that "fact" yo=
u completely failed to research (ring a bell?). <<

Sorry, but it is a table property internal to the machine that counts
physical insertion attempts to that base (physical) table. It is not
an attribute of an entity or relationship modeled by the table. Does a
squid have an IDENTITY on it? Does an automobile have an IDENTITY when
it comes from the factory? Nope. But an IDENTITY can be put on the
tables for both automobiles and squids. Just like an index or other
access method. Ever seen Oracle programmers use ROWID?

>> however, your example shows one thing that surrogates NEVER give the sam=
e value - thank you for the example but there is no need because the majori=
ty of folk on this forum know that the IDENTITY property will NEVER give th=
e same value twice unless a database administrator physically reseeds the I=
DENTITY property. <<

There is a little problem with that. I will ignore the many posting
over the years where someone moved a DB, forgot to handle the IDENTITY
correctly in the transfer and got everything re-numbered. I will
assume that people never do a re-set on the counter. But what if the
same entity appears in more than one DB? With hashing, I can get the
same surrogate anywhere because it is an algorithm, independent of
particular hardware. This is the local versus global problem for
entities.

0
CELKO
3/20/2010 8:09:06 PM
It is very evident you still have no concept of what a surrogate key is, you 
have also no idea that IDENTITY is nothing whatsoever to do with hardware - 
you've even had Kalen Delaney respected international author - a leading 
figure on SQL Server since the product began tell you that - it is the 
arrogant man who will not accept he is wrong - the very arrogant man.

Business key is a well known term - perhaps not in your four walled class 
room, but in database design - its one of the candidate keys that is exposed 
to the user of the application hence the term "business key".

The surrogate key is NOT a business key because it is never exposed to the 
Business user - I disagree with Date because exposing the surrogate key 
outside the confines of the system implementation as a whole would be a bad 
idea because the surrogate key is not an attribute within the business and 
nor should it be.

An automobile in the outside world does not have a surrogate key - that is 
true; that is because the surrogate key is used internally within the 
confines of the system - it should never be exposed to the user for that 
exact reason - because the values of a surrogate key should never be 
repeated.

In terms of adding ID to the column name - the forum is scattered with 
numerous examples of you doing that as well - it is only recently you've 
refrained from using the suffix ID on the primary key and instead now use 
_nbr.

So to follow your concept of naming you would now instead write AccountKey 
and VINKey because that is what the attribute is being used for - huh?

What is the point in cascading updates? The surrogate key values are not 
allowed to change!

Look - you have to stop confusing hardware with software - its easy - just 
go and do some research and stop this guessing!

--ROGGIE--

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:ce1a91c6-ed58-40ea-9d97-aacfb39dd186@c16g2000yqd.googlegroups.com...
>>> There is no need to rename anything - explain why. <<
>
> ISO-11179 rules and basic data modeling say that a data element is
> named for what it is by its nature. If you meant the postfix �-ID� to
> mean IDENTITY, that is like putting �int-� or other data type
> information in a name. If you meant it to be �identifier� then it is
> wrong, because the Account number is the only attribute that
> identifies an account in the Accounts table. You did say that you
> wanted to use it as as surrogate, so tell us that is what it is.
>
>>> There is one business key on this table - account_nbr, there is also a 
>>> surrogate key on this table AccountID [sic]. <<
>
> Business key? You have not spent much time with Date yet! For him,
> there are keys, primary keys, surrogate keys, super keys and maybe
> some other flavors, but business key is not one of his terms.
>
>>> Why do you think this is not a table? <<
>
> Because this creates duplicate rows:
> INSERT INTO Chart_of_Accounts VALUES (42,43), (42,43);
>
> Because this creates an invalid relatiosnhip
> INSERT INTO Chart_of_Accounts VALUES (42,42);
>
> And there is there are missing constraints. I think that you should
> have at least had this:
>
> CREATE TABLE Chart_of_Accounts
> (parent_account_surrogate INTEGER NOT NULL
> REFERENCES Accounts(account_surrogate)
> ON UPDATE NO ACTION,
>
> subordinate_account_surrogate INTEGER NOT NULL
> REFERENCES Accounts(account_surrogate)
> ON UPDATE NO ACTION,
>
> PRIMARY KEY (parent_account_surrogate, subordinate_account_surrogate),
> CONSTRAINT No_Self_Subordinates
> CHECK (parent_account_surrogate <> subordinate_account_surrogate)
> );
>
> I would cascade updates and deletes. I would also use the nested sets
> model to avoid longer cycles than the simple case covered by
> No_Self_Subordinates, but that is another topic.
>
>>> Oh - one mo, did you mean there is no candidate key on 
>>> Chart_of_Accounts? What sort of database professional are you if you 
>>> cannot recognize the candidate key on this table is parent_AccountID, 
>>> subordinateAccountID - after all, this represents there cannot be more 
>>> than one subordinate_AccountID per parent_AccountID <<
>
> What kind of programmer are you if you expect the computer to add code
> you never wrote? You need to fix the DDL.
>
>>> The rest of your post is just b0ll0cks aiming to distract from the 
>>> actual point in hand that you do not understand nor accept surrogate 
>>> keys. <<
>
> I have a good understanding of most access methods in use. And I don't
> mind a surrogate in Codd's terms. But if you wanted to make a case for
> surrogacy, this is a terrible example because of the simple natural
> key. A better example would be a data warehouse table with a multiple
> column natural key and a perfect or (better still) minimal perfect
> hashing function.
>
>>> we are talking finally you accept the IDENTITY property is not a 
>>> manifestation of the physical hardware (although you have published that 
>>> "fact" you completely failed to research (ring a bell?). <<
>
> Sorry, but it is a table property internal to the machine that counts
> physical insertion attempts to that base (physical) table. It is not
> an attribute of an entity or relationship modeled by the table. Does a
> squid have an IDENTITY on it? Does an automobile have an IDENTITY when
> it comes from the factory? Nope. But an IDENTITY can be put on the
> tables for both automobiles and squids. Just like an index or other
> access method. Ever seen Oracle programmers use ROWID?
>
>>> however, your example shows one thing that surrogates NEVER give the 
>>> same value - thank you for the example but there is no need because the 
>>> majority of folk on this forum know that the IDENTITY property will 
>>> NEVER give the same value twice unless a database administrator 
>>> physically reseeds the IDENTITY property. <<
>
> There is a little problem with that. I will ignore the many posting
> over the years where someone moved a DB, forgot to handle the IDENTITY
> correctly in the transfer and got everything re-numbered. I will
> assume that people never do a re-set on the counter. But what if the
> same entity appears in more than one DB? With hashing, I can get the
> same surrogate anywhere because it is an algorithm, independent of
> particular hardware. This is the local versus global problem for
> entities.
> 
0
Tony
3/21/2010 8:44:50 AM
>> In terms of adding ID to the column name - the forum is scattered with numerous examples of you doing that as well - it is only recently you've refrained from using the suffix ID on the primary key and instead now use "_nbr". <<

Yep!  And I am going thru the files for my books when I get a chance
and correcting code to ISO-11179 conventions. I had not written SQL
PROGRAMMING STYLE back then when the meta-data rules did not yet
exist. Now I have the fun of cleaning up eight books that are still in
print. I guess this is how people who are now cleaning code of
UPPERCASE, camelCase, PascalCase, and Hungarian notation feel, but
they will do it mechanically.

Proper data element names is not just a mechanical thing (damn it!),
but semantics. You have to think about the nature of the data element
and look for assumptions based on local context. My favorite example
is "sex", which is the term used by the ISO Standard. How many
property postfixes can you add to that attribute to get a valid data
element name?

sex_flg = 'yes' or 'no'
sex_date = the last time you had some
sex_freq = how often?
sex_pref = preference code
sex_size = I am not going to go there. I did a database of porno
actors when I lived in Los Angeles and this was a column.
sex_code = this is what ISO really means
sex_id = absurd! Sex is not unique to each person; it is an attribute
with a finite scale.

I get my laugh with this, but most people see the point of the
ISO-11179 names.

>> So to follow your concept of naming you would now instead write AccountKey and VINKey because that is what the attribute is being used for - huh? <<

Since "-key" is a meta-data property, it cannot be part of a data
element name. It tells you how the attribute is used in the local
context, not what its nature is. We want to know about account qua
account.

Since VIN (ISO-3779) is a global identifier, just "vin" is fine for a
column name. I go over this in SQL PROGRAMMING STYLE and cover a lot
of the research.

The better question is why use "account_nbr" and not
"account_id" (note the lowercase to show attribute property rather
than uppercase to show a proprietary data type)?

The banking industry uses the term "account number" instead. This is a
tag number rather than an identifier. Therefore, we have both the
industry and the meta-data people on our side.

0
CELKO
3/21/2010 11:30:51 PM
Reply:

Similar Artilces:

Filter Question!
I have a table [tblAccountStatus] that has around 6 fields for receiving payments 2 of the fields are [ClientID] (Number) and [ModeOfPayment] (Text) I have created a query using these to fields , made a combo box using this query ,put it on my form and made Control Source [ModeOfPayment] Now what I would like to do is when I enter the ClientID on to my form the query will only show [ModeOfPayment] that has been entered with that [ClientID] Hence when I select Mr Tom Cruise , I will get a selection my from my Combo Box as the names of the checks I have entered under his ClientID Hope thi...

Data table in chart?
This is a multi-part message in MIME format. ------=_NextPart_000_01A9_01C69782.28506D60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have some line chart portraying responses to 20 items. I would like to put a table under each chart the shows the value of not = only the responses that are charted but also the percentage difference = between the two. My problem and the question is that if I use the 'data table' option in = creating the chart the row with the difference is not there. Then if I = try to paste a table under ...

Combobox for Table, From Table
Hi, The online help function tells me I cant make a combobox which finds its entries from a table and stores its entries in the same table. I want users to be able to enter areas where clients live. The combobox needs to fill the Area column in the appropriate table. But I want the combobox to be filled with previous entries from the same column, with the provision that a new entry can be made. The project is expanding thats why I need this. Or do I need to make an extra table with areas that can be filled with a separate command, which is then used as a source for the com...

Copy data from on table to another ?
I have lost a lot of data in my backend, but it is only data from a few columns, in one tabel, that I have lost. My tabel is called taCustemor, and the columns in that table is called fakFirm, fakAdress. I have use a custemornumber as a primarykey. So now I want to copy only these columns from my backup of the backend to my backend in use. I'm sure that I can do this buy using SQL, but I'm not sure how to make this querie. Can some one out there help me ? You want to use an append query. See Help file for info. In the database where you want the data to be when you're done, ...

pivot table again
i have a range of fields in a pivot table some are qty others are value how can i split the table into two seperate sections one showing qty and the other showing values (if that makes sense) If your quantities and values are in the same column, you'd need to use a "helper column" that differetiates them (Qty vs Amt) Then you can include that field in the pivot table before tha field containing the quantities and amounts. Does that help? *********** Regards, Ron XL2002, WinXP "max power" wrote: > i have a range of fields in a pivot table some are qty other...

Landscape Table on Portrait Page
Is it possible to create a landscape table on a portrait page, i.e. a page with a normal portrait header and footer? I am aware that one can change the text direction of each cell in the table to simulate a landscape table, but I would like to know if there is not an easier way. See http://word.mvps.org/FAQs/Formatting/LandscapeSection.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Johann Swart" <JohannSwart@discussions.microsoft.com> wrote in message news:1119FBC3-2001-48C7-B02E-07D85C7F444E@mic...

Where is tables in reports
Is there anyway to get tables into reports? If there is none then is there a work around. The forms I have to make use many block cells. In word tables makes things easy. ...

Ensuring only one commission per product in Access Table
Good afternoon, Please can someone help me. In my Access Database I have a table called tblCommission, with the fields: Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $) I need a method to ensure that each client can have a commission set for every product - but that a client cant have to commission's for one product: eg: I need a method to prevent this: Comm_ID Client_account Product_code Commission Exchange 1 000001 Coke 3 $ 2 000001 Coke ...

How do you create a table with a shaded header in Word 2007 or 201
Hi.. Can anybody tell me, how you create a table, and then shade the first row in it to something like gray, then set the grid lines to gray or something, and maybe set the alignment in it to centered... This is 2007 or 2010, not 2003 in which it was possible to do so. Thanks. Select the first row of the table and then right click with the mouse and select Borders and shading. For the alignment, select and right click and select Cell alignment -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting ba...

Recreating HR tables
I've been having some problems with HR. I've had to create some tables so the program would run. I simply copied the tables from another compagnie where is was saying I had missing tables. I'm wondering if there is a list of Tables for HR and Canadian Payroll or if it were possible to recreate these missing tables another way? I currently have this error as well: Could not find stored procedure 'Compagnie.dbo.hrCompDoesHrCompExist' ...

Table Expert
When a case is resolved, there is a dropdown to select the amount of "Billable Time" for the case. I have searched tables but cannot find where this data is stored. Is there anyone that know the schema where this data is kept? Bryon, When you resolve a case, the resolution screen fields are added into the activity table as a resolution type of activity. The Billable Time value appears in the TimeSpent field. (While building my auditing application, VAST, I've had to become much more of a CRM schema expert that I ever wanted to be - by the way, you can see these reso...

Pivot table subtotals 100% #2
This is what I am aiming for but how can I get 100% in subtotals for first year and the increase %age for the second and subsequent year please 2002 2003 Apples excellent 15 22.72% 16 24.23% good 21 31.82% 25 37.88% poor 30 46.46% 39 60.39% 66 100% 80 122.5% Greens excellent 12 17.65% 37 54.42% good 27 39.70% 51 74.98% poor 29 42.65% 47 69.12% ...

Table info
In Vendor Maintenance window , there are multiple Address ID's. In which table i can find this information. Great Plains 7.5 Thanks in advance Deepwater, PM00300 holds all the address information for each address ID. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html "Deepwater" <Deepwater@discussions.microsoft.com> wrote in message news:F9296B22-2B44-4D64-9E3C-DC5FE4B07028@microsoft.com... > In Vendor Maintenance window , there are multiple Address ID's. In which > table i can find this...

Query will not sort
I have made a query and it will not sort one of my fields. I thought it was a simple query but maybe not. Field: Area, Sort Ascending Field: OrderNumber, Sort Ascending Field: Status, Criteria Like "*Y*" Field: Week 1, Criteria Is Null It will do everything except sort on the order number. -- Lenee Lenee Please post the SQL statement of your query. ?Are you saying that the query should sort on OrderNumber first? If so, why is Area first? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services ...

Extender tables #2
Hello! We are implementing an integration from Microsoft CRM into Microsoft Great Plains and this would include writing CRM fields to Extender Fields. Are there any available SDK's for Extender or maybe any table lists? Thanks, Andi Saldana There are but I think only partners can get them. Please send me a message and I'll send you what I have. "Andi Saldana" wrote: > Hello! > > We are implementing an integration from Microsoft CRM into Microsoft Great > Plains and this would include writing CRM fields to Extender Fields. Are > there any avai...

Pivot Table Axis format
Hi. I have a macro that creates a pivot-table and pivot chart. The X-axis is a date. In the raw data, the date is formated as month/day. This is the way I want it on the chart. However, the X-axis formats with month/day/year. How can I force this to display correctly? Thanks, Mike. Do you see a grey little dropdown under the X axis (where you can choose your dates to show)? If yes, then rightclick on it. Select the top option: "Format pivotchart field" then click on the Number button on the next dialog. There are a bunch of date formats that you can choose from. Maybe...

Outlook Rules Query
Hi group... My email rules do not work if Outlook 2002 is set to send/receive automatically on startup. However... they do work if Outlook is set up to NOT send/receive on startup and I click send/receive shortly after starting Outlook 2002 However 2... they do work if the "Run Now" option is chosen so I do know that they actually do work. Is this normal... can someone suggest the correct settings to resolve this Thanks in advance Dave G ...

If Formula Question #2
Hi There I am struggling with what appears to be an easy formula. Here is what I am trying to do: If the values of cell vale of B4 begins with either 20 or 30 then the (B20*0.5) Basically if a part number is for example 20-504 or 30-555 then we need to give it 50% discount. We have many part numbers beginning with 20 or 30 that need 50% discount. Any help or advice offered will be most welcome. Many thanks in advance. -- Kind Regards Ady Use the following: =IF(OR(LEFT(B1,2)="20",LEFT(B1,2)="30"),C1*0.5,C1) "Ady" <ady@nospam.com> wrote in me...

Inserting table to other table
I have a form which consists of a table. I want to insert some selected value into other table when i update and at the same time I want it to save into my own table too... May I know the way please..... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200704/1 I'm having trouble visualizing your situation. Could you provide an example of data? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner http...

Relay question #2
Hello, I will appreciate your help in the next problem: Exchange server 2003. The domain name is abc.com and i created another domain for some user accounts: xyz.com with the recipient policies. All ok. I have users that belongs to both companys and they needs to send mails from both domains. For one user I configured account abc.com as exchange account and then the xyz.com as pop account. I used outlook 2003. I allowed the rely permission for this user and his pc. The problem start when i send mail from the xyz.com account to an external account. At destination, the mail arrives as if it...

table to new table
hi thanks for your time and help i have an access table which is an automatic update so its data is constantly changing but the fields stay the same. i need to be able to transfer the data at the click of a button from the linked table into an access table without deleting the data already stored in the main table. i have tried looking at the transfer spreadsheet but this does not work. thanks for the help hi, Rivers wrote: > i have an access table which is an automatic update so its data is > constantly changing but the fields stay the same. > i need to be able to transfer...

Pivot Table not sorting correctly
In column A I have several of the same names listed more than once spelled exactly the same but when these line are pulled into a pivot table each is displayed seperately. I followed the same exact procedure for this table as I have for the others that I displayed correctly. What causes those lines to be displayed on separate lines? Perhaps some of the entries have space characters at the end, and others don't. Marian wrote: > In column A I have several of the same names listed more than once spelled > exactly the same but when these line are pulled into a pivot table each ...

Pivote table adding Fileds
Dear All Happy new year I had creat pivote table on table that was exported from Access (it was exported from query that based on relation beteen tabled ) ,if i make refreshing data on the same filds the new data will be refrexhed withot any problem , but when i add new filds in the query and exported again (with the same name and the same orginal fields but with one or two more additional fileds ) if i make refresh he can get and match the old filds but not the new filds , so i can not add this filds to the pivote table unless i restart the pivote table from the start , MY Question is , is...

DelayLoad questions
I am tryng to delay load a DLL and have a few questions hopefully somebody can answer. If I correctly setup a DLL to be delay loaded would it be required to be in my PATH on startup even though I am not invoking any functions defined in the DLL at startup time. Is there some tool I can use (maybe process monitor or something along that line) that will show me that my DLL has not been loaded even though my program is running. Lastly, is there a preferred method to delay loading a DLL. Is it better to to do it programatically or through options in Visual Studio? Thanks in advance for all tha...

TAble Insert
I have a person table that has id, firstname, lastname, address, city, state, zip. I have set all fields except id to have required = no and allow zero length = yes. But when I try the following query it will not insert the record because the firstname is not filled out. Any ideas? INSERT INTO person ( id, lastname, firstname, address, city, state, zip ) VALUES (1783, 'Hacienda La Daniela', '', '5310 SW 192 Avenue', 'Sw Ranches', 'Fl', '33331'); Try inserting Null instead of a zero-length string: INSERT INTO ... VALUES (1783, 'Hacienda...