Need help with update query involving 3 tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Ball - 29 Nov 2005 12:12 GMT

Here are the pertinent tables and fields

Author
======
AuthorID            AutoNumber - primary key
BookCount            Number
TotalReviews    Number
Other_Fields

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

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 and VC++ 6 DAO to
read the database (read-only) into memory and produce static HTML. I'd
like to start doing some SQL other than just read records into memory.
I managed to do a simple update query involving one table but I'm
getting all kinds of errors when I try this. I've read through this
group looking for answers, but so far every query I've come up with
has gotten an error.

For each Books record, I want to set Books.ReviewCount to the number
of Reviews records where Books.BookKey = Reviews.RBookID

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.

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?

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?

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.

TIA,

-- David

Reply to this Message

Chris2 - 29 Nov 2005 15:08 GMT

> Here are the pertinent tables and fields
>
[quoted text clipped - 4 lines]
> 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
> =====
[quoted text clipped - 8 lines]
> 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
> =======
[quoted text clipped - 4 lines]
> 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
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

David Ball - 29 Nov 2005 23:26 GMT

I said

>> I'm using Access 2000 (running on WinXP SP2 with all updates for
>the
>> OS, VStudio 6, and Office) to maintain the database

You said

>Aren't Access 97 databases read-only for object changes when opened
>in Access 2000?
>
>By "maintain", what exactly do you mean?

The database is in Access 2000 format. I use Access 2000 to
create/update tables and to do data entry. The C++ program has no
trouble using the Access 2000 Format. The C++ runs through the tables,
reading them into memory where it performs a massive data mining
operation and generates over 20000 heavily hyperlinked HTML pages. The
count fields I am adding only need to be correct every few days when
the c++ program is run. The C++ could calculate the counts, but I'm
trying to learn how to do things with SQL. I'm an SQL newbie.

The Visual Studio Integrated Development Environment has wizards for
looking at an Access database table and defining/updating a C++ class
for a table recordset plus generating/updating the code to move the
data between the C++ class and a selected table row. Unfortunately,
this wizard only understands Access 97 format so I have to export a
copy of the database to Access 97 format and run the IDE Wizard on the
Access 97 copy whenever I add new fields that the C++ program needs to
be aware of. The wizard adds the new fields to the C++ programs
recordset class for that table. I can then delete the Access 97 copy
because the C++ program executable itself can work with Access 2000
format database files.

-- David

Reply to this Message