David Ball said:
Here are the pertinent tables and fields
Author
======
AuthorID AutoNumber - primary key
BookCount Number
TotalReviews Number
Other_Fields
David Ball,
BookCount is not really an attribute of Author.
From the description below, you do not yet have control of the
schema of the database, but if you do gain control of it, consider
removing this column and calculating it when necessary.
Books
=====
BookKey Autonumber - primary key
AuthID1 Number - Matches an Author.Authorid
AuthID2 Number - Optional Additional Author or NULL or 0
AuthID3 Number - Optional Additional Author or NULL or 0
.
.
.
AuthID10 Number - Optional Additional Author or NULL or 0
ReviewCount Number - number of reviews of this book
Other_Fields
This table is not normalized. It has repeating columns each storing
the same data element. First, this is the same attribute as
Author.Authorid, but it has multiple different names. If you gain
control of the schema of the database, consider removing
Books.AuthID(n) altogether. Then have a "BookAuthors" table that
represents a many-to-many relationship, thus:
BookAuthors
BookKey -- FK to Book \
AuthorID -- FK to Author / Composite PK
SeqNbr
This table will allow many books to have many authors, and will show
the clear sequence of credited authors (no matter how many there
are). It will also facilitate standard use of aggregate functions.
SELECT COUNT(BA1.BookKey)
FROM BookAuthors AS BA1
WHERE AuthorID = <some parameter>
This query is enormously simpler than what will be necessary for
counting the repeating AuthID(n) columns of the Books table above
(I'm not exagerating). It is so much simpler that it justifies
calculating this value only when it is needed, and not storing it at
all in Author.
Reviews
=======
ReviewID Autonumber - primary key
RBookID Number - matches a Books.BookKey
Other_Fields
I'm using Access 2000 (running on WinXP SP2 with all updates for the
OS, VStudio 6, and Office) to maintain the database
Aren't Access 97 databases read-only for object changes when opened
in Access 2000?
By "maintain", what exactly do you mean?
For each Books record, I want to set Books.ReviewCount to the number
of Reviews records where Books.BookKey = Reviews.RBookID
If at all possible, calculate this value only when needed from the
underlying data.
Then, for each Author record, I want to set Author.BookCount to the
number of books which have one of the fields Books.AuthID1 through
Books.AuthID10 match Author.AuthorID and set Author.TotalReviews to
the sum of all the Books.ReviewCount in the matching books.
If at all possible, calculate the BookCount value only when needed
from the underlying data. Counting the occurences of a value across
repeating columns is difficult, none of the solutions are good ones,
and represents a substantial performance hit.
Also:
Using DAO 3.5 for Access 97, it should be possible to create a new
QueryDef, load the .sql property with an UPDATE statement, and then
order it to execute.
Currently, While I can read the Access 2000 data format from DAO in my
VC++ 6 code, The VStudio 6 ClassWizard only recognizes Access 97 so I
have to export to an Access 97 database to bind fields in the
ClassWizard. I've heard that versions beyond Access 2000 can use the
Access 2000 data format, but I've heard nothing about them being able
to export to Access 97 format so I'm stuck with Access 2000 unless
someone can tell me how to get the ClassWizard in VStudio to recognize
later versions or that versions later than Access 2000 can still
export the database to Access 97 format.
BTW, since I moved and many of my computer books are packed away, can
someone tell me how to execute an update query from DAO in VC++ 6?
I am not a VC++ programmer, so I can only offer peripheral advice on
these matters.
My installation of Access 2000 came with a file called DNJET.chm.
This is the Microsoft Jet Database Engine Programmer's Guide, for
Jet 3.5. It is one version behind the current JET 4.0, but much of
it is still applicable. It is true that most programming examples
are in VB, but Chapter 11 is "Programming with DAO in C++ and J++".
There are examples of running the basic queries, including "Updating
Records from the Employee Database" for C++.
Sorry to be such a bother but I've spent hours trying to figure this
out and getting errors that make no sense to me. BTW, what is
"aggregation" that keeps showing up in error messages?
Without your error message, your DDL, your sample data, and your
code, that's a difficult question to answer (not being a VC++
programmer is an additional handicap to me, but even if I was one, I
wouldn't be able to tell).
Can anyone recommend a good CURRENT book on SQL? I have books on
Access 2000, but they seem to gloss over SQL Queries and concentrate
on things like VBA.
Books: General: Beginner
Database Design for Mere Mortals by Michael J. Hernandez
SQL Queries for Mere Mortals by Michael J. Hernandez, John L.
Viescas
Books: General: Intermediate/Advanced
Advanced SQL Programming, For Smarties, 3rd Edition, by Joe Celko
SQL Puzzles and Answers, by Joe Celko
Books: Access : Intermediate
Access Cookbook by Getz, Litwin, and Baron
(Compilation of solutions, listed by task-category)
Books: Access: Advanced
Building Microsoft Access Applications, by John L. Viescas
Access Database Design & Programming by Steven Roman (2nd or 3rd
Edition)
Access Developer's Handbook (for your version of Access)
Websites:
http://www.mvps.org/access
http://allenbrowne.com/
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Top
Sincerely,
Chris O.
