I'm a newbie to dB programming. I set up a set of tables (see below
after the RL signoff) to capture the relationship between a group of
people buying and selling stocks, and it worked well (using Visual
Basic Access for the front end). Now I want to extend this
architecture of tables to a group of people buying and redeeming CDs
(Certificates of Deposit) at banks.

Everything worked fine for this CD extension, I set in up in no time,
but I noticed that when a user enters data, sometimes the same
combination of Bank Account number and CD identifier (CD ID) will be
entered by mistake, which under the below architecture will create a
new record. I don't want this.

I have various ideas on how to fix this programmically (such as a
simple lookup table so when the combination of particular Bank Account
and CD ID are found, a warning box will tell the user that this record
already exists).

However, I can't, under the below architecture, seem to do this via
"relationships" in Access. I tried various combinations, but each
time something went wrong. My most promising (I thought) attempt was
to replace in the critical linking table "AccountStocks" below (please
substitute "CD" for "stocks" below, because the architecture for CD
and Stocks is exactly the same) with a new primary COMPOUND key
comprising: StockSymbol (i.e. CD Symbol) and AcctID. However, this
compound key creation did not give me what I wanted, in that the form
I used did not allow me to create a new record twice having a
different StockSymbol/CD ID but the same AcctID. I can fix this by
playing around with the form, by redesigning it so that CD is no
longer in a relationship with the AccountStocks/AccountCD table, but
rather is simply another field, or part of a combo box or list box,
with a message box warning as I discussed above, and perhaps that's
the solution (a programmical solution in my mind), but I wonder if
there's something else I'm missing from an architecture point of
view. Can I do something like what I have in mind by a judicious
selection of keys (relationships)?

FYI here is the SQL query linking the "Transactions" table with the
"AccountCD" table (the junction table) (maybe the SQL query is wrong?
But it doesn't seem to impact what happens further upstream, and it
looks pretty generic to me--just selecting all the fields in both
tables and equi-joining them with a common Account CDID from the
parent (AccountCD) table).


SELECT AccountCDs.AccountCDID, AccountCDs.CDSymbol, AccountCDs.AcctID,
CDTransactions.Account_CDID, CDTransactions.dateDeposit,
CDTransactions.Principal, CDTransactions.[interest rate],
CDTransactions.TransactionsPK
FROM AccountCDs INNER JOIN CDTransactions ON AccountCDs.AccountCDID =
CDTransactions.Account_CDID;

RL


tblPersons

On Dec 29, 6:51 am, raylopez99 <raylope... (AT) yahoo (DOT) com> wrote:
Hi,

Since you asked in a theory newsgroup, you're likely to get
a theory-based answer. The short, theory based answer is
that checking for this programmatically is not the right way
to go. Reasons include that there may, over time, be various
places the data can be entered, and thus *every* program
written, at every place such data is entered, will have to
enforce this constraint, and you will get data corruption if
there are any places you miss, or any bugs in any of the
implementations you come up with. If you use the DBMS
to enforce the constraint, this will not be the case.

If you need help with Access-specific issues, you'll do
best to ask in an Access-specific newsgroup. I would
recommend you mistrust any Access-specific answers
you get in a theory newsgroup, and I would also advise
you to mistrust any theory advice you get in an an
Access-specific newsgroup.

HTH


Marshall

On Dec 29, 5:42*pm, Marshall <marshall.spi... (AT) gmail (DOT) com> wrote:

Thanks. I could not get the compound key to work in Access, which is
a bit strange (the restriction on whether a foreign key is a duplicate
or is unique is rather hidden). After many permutations, I gave up,
but it could be a peculiarity of Access.

RL

raylopez99 wrote:

Having read over your original question again, it seems you are trying
to declare a uniqueness constraint as a foreign key reference.

I don't know the specifics in Access, but 'uniqueness constraint',
'alternate key', 'unique index' etc. are some likely candidates to look for.

Just some nits:

raylopez99 wrote:

This means you want to constrain your data.

Good to warn your users they are about to violate a constraint, not
good to guard your data in a UI.

Why this tbl prefix? Should I call you guyRay?





--
What you see depends on where you stand.

On Dec 30, 10:40*am, mAsterdam <mAster... (AT) vrijdag (DOT) org> wrote:

OK wise guy <g>, I kindly beseech thee to give me a simple schema that
will do the following: "to declare a uniqueness constraint as a
foreign key reference" that allows me to do the following:

Table Certificate of Deposit provides a series of fixed income
products (CDs, Bonds, notes, etc, that are portable, in that you can
deposit them anywhere)--hereinafter "CD". Primary key right now is
"CD SYmbol" (alphanumeric designator of the product:
XYZABC_Bond_matures_2008)

Table Bank has a primary key comprising: An account number that is
unique to every person--hereinafter "Bank"

Now I want to create a junction table called "AccountCD" that combines
the primary key of both the two tables above.

I do this, but unfortunately in Access I cannot enter a combination of
"CD" and "Bank" that is unique. THat is, either the table allows me
to enter the same CD with the same Bank twice***, or, if I play around
with the keys, it forbids me from entering either (1) the same bank
twice or (2) the same CD twice. BTW, this last restriction (1) I can
live with, but I don't want it--that is, I can set up a AccountCD
table that has only one record per bank, and different CDs in the
record, but what I want (because I'm too lazy to change my schema now)
is a series of unique combinations of records comprising: "BankA + CD
123", "BankA + CD ABC", "BankB + CD 123", "BankB + CD ABC", but not
"BankA + CD 123" twice. Got it? If it helps, my relationships flow
1 to many from CD/Bank (one) to AccountCD (many).

*** That is, the way the schema is now, I can enter any combination of
bank and CD I want, without restriction, even if it's a double: ie.,
"BankA + CD 123", "BankA + CD 123".

Got Access?

LOL. Access is great for rapid coding at the GUI level--you should
see what I've come up with in only a few days, but it's kludgey trying
to debug stuff.

BTW, I notice a tension between not just front end GUI and back end dB
schema people, but between putting data traps and data crunching code
in the GUI front end versus writing a SQL query or otherwise dealing
with the data in the back end. In fact, some people (the posters at
microsoft . public . access . formscoding and elsewhere) imply and
state that it's better to deal with this stuff at the front end, since
SQL "puts too much load onto the servers" (or in particular Access), a
preformance issue, while others, I suspect you guys, think that the
front end technique is too much of a maintenance problem or is bad
design.

RL

raylopez99 wrote:

Why on earth would anyone declare a uniqueness constraint as a foreign
key reference? They are different things.

[snip]


That's rather like asking: "Got herpes?"


Access is an end-user reporting tool. It pretends to be a bunch of other
things but it does those other things very poorly.


The whole point of using a database management system is to centralize
data management so that no user or application can subvert the integrity
of the data for any other user or application. But then again, you are
not using a database management system--you are using an end-user
reporting tool with a built-in file processor.

raylopez99 wrote:
the "t" in c.d.t. stands for "theory". Experienced carpenters can build
quite serviceable toolsheds and doghouses without having to consult or
be, an architect. You could probably even build a birdhouse with a
Leatherman, Dremel and some determination.

Since I don't particularly understand how what you define as CD's, Banks
and Accounts all fit together, and also, like yourself, am too lazy to
find out, my off-the-cuff observation that you've buggered it up
probably isn't worth much.


rpl

raylopez99 wrote:

Where did you get the quoted part? It is unclear.

Why not call it 'Persons' if - as I read from this -
it is supposed to hold data about persons?

Do I get you right? Two foreign keys in the junction:
AccountCD.CD_symbol referencing 'Certificate of Deposit' and
AccountCD.Bank_account referencing 'Bank', and
'AccountCD' should have the combination of AccountCD.CD_symbol
and AccountCD.Bank_account as primary key.

If I may read this as: It won't allow me to define a primary key as a
combination of columns: How sad (NOT kidding).
I suspect, that if you use a DBMS as a back-end instead of the
built-in, you can.

No, not at hand.

It is a matter of focus and scale. Using your editor
to edit textfiles with data is one end of the scale.
While it is obviously not a good way to protect your data
against mistakes, it suffices for a lot of my
personal data.


--
What you see depends on where you stand.

On Dec 30, 9:09 am, raylopez99 <raylope... (AT) yahoo (DOT) com> wrote:
If you want wrong answers quickly, then by all means enforce data
integrity in the front end.


Marshall