Hey all,
(Access 2000)
I've been having a horror story with this design problem. My Database is
Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
Item in each table needs a unique ID# based on its context.
Primary Keys
AUTHORS = AuthorID - NO Duplicates
BOOKS = AuthorID - Dups OK
BookID - Dups OK
PAGES = AuthorID - Dups OK
BookID - Dups OK
PageNumber - Dups OK
So for instance the pages need to number from 1 to <i>n</i> for each Book of
each Author.
The problem I had after I set this up is that from PAGES to BOOKS
I could NOT use referencial integrity because it could not identify a unique
Primary Key, (because I used Duplicates OK = True in BOOKS)
-> Is this not a valid way link a 3 level database?
I tried using auto-number primary keys but that killed the validation for
duplicates in the table.
-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the
same as one primary Key with Dups = NO so that I can set up integrity
between tables using these two primary keys?
Thanks for any insight!
~Philip 7 5351
Philip wrote: -> Is this not a valid way link a 3 level database?
Oh yes it is.
I tried using auto-number primary keys but that killed the validation for duplicates in the table.
A *very good* observation. Write it down for future reference. :-) -> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the same as one primary Key with Dups = NO so that I can set up integrity between tables using these two primary keys?
Nah. You have *one* primary key, which may consist of more fields, each
with its own index (duplicate or not) but the composite *MUST BE UNIQUE*
otherwise it doesn't work as primary key.
In the design view of the tables, select all rows pertaining to the key,
and only then press the 'key' button to set those as primary key.
--
Bas Cost Budde http://www.heuveltop.org/BasCB
but the domain is nl
"Philip" <Ph****@afterap riltax.com> wrote in message
news:Wq******** ***********@nwr ddc01.gnilink.n et... Hey all,
(Access 2000) I've been having a horror story with this design problem. My Database is Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each Item in each table needs a unique ID# based on its context.
Primary Keys AUTHORS = AuthorID - NO Duplicates
BOOKS = AuthorID - Dups OK BookID - Dups OK
PAGES = AuthorID - Dups OK BookID - Dups OK PageNumber - Dups OK
So for instance the pages need to number from 1 to <i>n</i> for each Book
of each Author.
The problem I had after I set this up is that from PAGES to BOOKS I could NOT use referencial integrity because it could not identify a
unique Primary Key, (because I used Duplicates OK = True in BOOKS)
-> Is this not a valid way link a 3 level database?
I tried using auto-number primary keys but that killed the validation for duplicates in the table.
-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True
the same as one primary Key with Dups = NO so that I can set up integrity between tables using these two primary keys?
Thanks for any insight! ~Philip
Do you realise the difference between a primary key and a unique index? You
can have a primary key PageID which is simply an autonumber to identify the
record, yet still enforce a unique index so that you cannot have a duplicate
AuthorID, BookID, PageNumber.
While looking in the table in design view, select View>Indexes and create a
new one called idxPages then select these 3 fields, one below the other, and
set primary to 'no' but unique to 'yes'.
Will this help you do what you want?
Fletcher
Thank you for the swift response!
I was able to get the relationship working for AUTHOR-BOOK-PAGE with just a
few tweaks
> Do you realise the difference between a primary key and a unique index?
You can have a primary key PageID which is simply an autonumber to identify
the record, yet still enforce a unique index so that you cannot have a
duplicate AuthorID, BookID, PageNumber. While looking in the table in design view, select View>Indexes and create
a new one called idxPages then select these 3 fields, one below the other,
and set primary to 'no' but unique to 'yes'.
Will this help you do what you want?
Thank you for the explanation, another small piece of my ignorance has died
at your hands, however,
I ran into an issue with the second part of the DB and your explation seemed
like it would provide the solution, but I cannot get it to work the way you
described...
The following relationship describes the same AUTHOR DB with two different
Tables
Primary Keys
AUTHORS = AuthorID - DupNO
BOOKCATEGORYS = AuthorID - DupOK
BookCatId - DupOK
BookCatOrder - DupOK
BOOKORDER = AuthorID - DupOK
BookCatId - DupOK
BookOrder - DupOK
Each Author has different categorys for his/her books, each category has a
certain list of books and an order for them. Note: The Categories also have
an BookCatOrder ID
I tried your suggestion and Made The first two Fields (AuthorID and
BookCatID) the only primary's
while I made a multi field index for the two Tables with all three fields,
so those fields would be unique.
This allowed me to create a relationship with integrity but did NOT validate
uniqueness!
I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2
Two different Categories are claiming to be the second in the Category Order
It was only validating the primary keys for uniqueness
-> Are there limitations when using index's with the primary keys on same
fields? I have never used multi-field indexes.
Thanks,
~Philip
"Philip" <Ph****@afterap riltax.com> wrote in message
news:IY******** ***********@nwr ddc02.gnilink.n et... Do you realise the difference between a primary key and a unique index? You can have a primary key PageID which is simply an autonumber to identify the record, yet still enforce a unique index so that you cannot have a duplicate AuthorID, BookID, PageNumber. While looking in the table in design view, select View>Indexes and
create a new one called idxPages then select these 3 fields, one below the other, and set primary to 'no' but unique to 'yes'.
Will this help you do what you want?
Thank you for the explanation, another small piece of my ignorance has
died at your hands, however, I ran into an issue with the second part of the DB and your explation
seemed like it would provide the solution, but I cannot get it to work the way
you described...
The following relationship describes the same AUTHOR DB with two different Tables
Primary Keys AUTHORS = AuthorID - DupNO
BOOKCATEGORYS = AuthorID - DupOK BookCatId - DupOK BookCatOrder - DupOK
BOOKORDER = AuthorID - DupOK BookCatId - DupOK BookOrder - DupOK
Each Author has different categorys for his/her books, each category has
a certain list of books and an order for them. Note: The Categories also
have an BookCatOrder ID
I tried your suggestion and Made The first two Fields (AuthorID and BookCatID) the only primary's while I made a multi field index for the two Tables with all three fields, so those fields would be unique.
This allowed me to create a relationship with integrity but did NOT
validate uniqueness!
I was still able to enter data like this for BOOKCATEGORYS AuthorId | CatId | BookCatOrder 1 | 1 | 2 1 | 2 | 2
Two different Categories are claiming to be the second in the Category
Order It was only validating the primary keys for uniqueness
-> Are there limitations when using index's with the primary keys on same fields? I have never used multi-field indexes.
Thanks, ~Philip
Hi Philip
I did take a second read through and still didn't get it - sorry. I think
the problem is I can't quite understand the table structure (I'm not even
100% sure whether the word 'order' means 'position in a list' or 'order as
in a sales / purchase order'). Perhaps it's too late at night or perhaps
someone else can help out here, but while an AUTHORS table seems straight
forward, I can't see the sense of the structure for BOOKCATEGORYS.
Anyway, my general point was that you could have a single autonumber for
each table as the primary key. This allows you to refer to this record in
another table. If you want to keep a value, or combination of values
unique, then you can use additional indexes. For example,
AuthorId | CatId | BookCatOrder 1 | 1 | 2 1 | 2 | 2
Was your objection that BookCatOrder=2 should occur no more than once in the
table? If you need this to be the case, then have a single index that says
that BookCatOrder must be unique. Your table can contain a number of
indexes - each involving single of multiple fields and each with the ability
to be unique. Obviously only one can be the primary key, but this does not
(and perhaps should not) contain any meaningful data (ie arbitrarily
assigned autonumber).
Fletcher
Fletcher,
I apologize because it DID work exactly as you explained! I think I've been
working on this project far too long, that annexed to my lack of knowledge,
accounts for my misconceptions.
My problem below (where order = order in list btw) was because I index'd all
*three* fields
The two example records are correctly unique! I needed to index AuthorId and
BookCatOrder only. I was still able to enter data like this for BOOKCATEGORYS AuthorId | CatId | BookCatOrder 1 | 1 | 2 1 | 2 | 2
Thank you!
~Philip
"Philip" <Ph****@afterap riltax.com> wrote in message
news:O%******** *******@nwrddc0 3.gnilink.net.. . Fletcher,
I apologize because it DID work exactly as you explained! I think I've
been working on this project far too long, that annexed to my lack of
knowledge, accounts for my misconceptions.
My problem below (where order = order in list btw) was because I index'd
all *three* fields The two example records are correctly unique! I needed to index AuthorId
and BookCatOrder only.
I was still able to enter data like this for BOOKCATEGORYS AuthorId | CatId | BookCatOrder 1 | 1 | 2 1 | 2 | 2
Thank you! ~Philip
Glad you got it to work. Thanks for the feedback.
Fletcher This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: ajikoe |
last post by:
Hello,
Is anyone has experiance in running python code to run multi thread
parallel in multi processor. Is it possible ?
Can python manage which cpu shoud do every thread?
Sincerely Yours,
Pujo
|
by: Frank Jona |
last post by:
Intellisense with C# and a multi-file assembly is not
working. With VB.NET it is working.
Is there a fix availible?
We're using VisualStudio 2003
Regards
Frank
|
by: * ProteanThread * |
last post by:
but depends upon the clique:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=954drf%24oca%241%40agate.berkeley.edu&rnum=2&prev=/groups%3Fq%3D%2522cross%2Bposting%2Bversus%2Bmulti%2Bposting%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den
...
|
by: frankenberry |
last post by:
I have multi-page tiff files. I need to extract individual frames from the
multi-page tiffs and save them as single-page tiffs. 95% of the time I
receive multi-page tiffs containing 1 or more black and white CCITT4
compressed files (frames) inside the tiff. Every now and then I receive a
mixture of black and white CCITT4 and JPEG compressed files, and sometimes
just multi-page tiffs with JPEG only. The code runs great when dealing with
the...
|
by: cody |
last post by:
What are multi file assemblies good for?
What are the advantages of using multiple assemblies (A.DLL+B.DLL) vs. a
single multi file assembly (A.DLL+A.NETMODULE)?
| |
by: Joe |
last post by:
I have 2 multi-list boxes, 1 displays course categories based on a
table called CATEGORIES. This table has 2 fields CATEGORY_ID,
CATEGORY_NAME
The other multi-list box displays courses based on a table called
COURSES. This table has 2 fields CATEGORY_ID, COURSE_NAME.
The CATEGORY_ID is a FK in COURSES and a PK in CATEGORIES.
I want to populate the course list box based on any category(s)
|
by: mimmo |
last post by:
Hi! I should convert the accented letters of a string in the correspondent
letters not accented. But when I compile with -Wall it give me:
warning: multi-character character constant
Do the problem is the charset? How I can avoid this warning? But the worst
thing isn't the warning, but that the program doesn't work! The program
execute all other operations well, but it don't print the converted
letters: for example, in the string...
|
by: bobwansink |
last post by:
Hi, I'm relatively new to programming and I would like to create a C++
multi user program. It's for a project for school. This means I will
have to write a paper about the theory too. Does anyone know a good
place to start looking for some theory on the subject of multi user
applications?
I know only bits and pieces, like about transactions, but a compendium
of possible approches to multi user programming would be very
appreciated!
|
by: Sabri.Pllana |
last post by:
We apologize if you receive multiple copies of this call for papers.
***********************************************************************
2008 International Workshop on Multi-Core Computing Systems
(MuCoCoS'08)
Barcelona, Spain, March 4 - 7, 2008; in conjunction with CISIS'08.
<http://www.par.univie.ac.at/~pllana/mucocos08>
***********************************************************************
Context
|
by: mknoll217 |
last post by:
I am recieving this error from my code:
The multi-part identifier "PAR.UniqueID" could not be bound.
The multi-part identifier "Salary.UniqueID" could not be bound.
The multi-part identifier "PAR.UniqueID" could not be bound.
The multi-part identifier "PAR.PAR_Status" could not be bound.
The multi-part identifier "Salary.New_Salary" could not be bound.
The multi-part identifier "Salary.UniqueID" could not be bound.
The multi-part...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |