relationship

[Guest]

ok. i have two tables.
one containing the fields Book ID (primary field), Book Title, Book Cost,
Book Retail Price.

another table containing, Book ID (primary field), Book Title, Book Author,
Book Cost, Retail Price, Unit of books, publisher, year of publication.

i already have the first table filled with information imported from an
excel spreadsheet (the book title and cost and retail price and id number)

the new table i made with those 7-8 fields, i would like to relate them
together in such a way as if i enter for e.g. book ID no. 001 (in table mode
or in a query mode), it would at least half of the record from me with the
corresponding data from book id no 001 from the first table (book title, book
cost, book retail price) automatically

please need help asap.
how would that be possible in Simple terms.
thanks

 

[Guest]

You should not be storing the same information in more than one table, except
for a foreign key, *unless* you need to maintain historical information. The
Book Title is likely to remain constant, so it should only be stored one
time, in one table. An example of storing the same information in more than
one table is seen in the sample Northwind database. Here, you might notice
that UnitPrice is stored in the Products table as well as the Order Details
table. The reason is that the price stored in the Order Details table is the
price that was in effect at the time of the sale.

Your new table is not properly normalized. You should have a separate table
of Publishers, and a separate table of Authors. Something like this:

tblAuthors
pkAuthorID (primary key)
AFirstName
ALastName
+ any other attributes about the author that you wish to store.

tblPublishers
pkPublisherID (primary key)
PubName
+ any other attributes about the publisher that you wish to store.

tblBooks
pkBookID (primary key)
Title
fkAuthorID (foreign key to tblAuthors.pkAuthorID)
fkPublisherID (foreign key to tblPublishers.pkPublisherID)
YearPublished
Book Cost
Retail Price
+ any other attributes about the book that you wish to store.


I suppose that Book Cost and Retail Price could vary from vendor to vendor,
so you'd need to consider a redesign to capture various vendors and prices.
Also, this simple design allows for each book to only have a single author
(without repeating the book record in tblBooks, or attempting to use multiple
values in the AuthorID field). If you need to accomodate more than one
author, then this requires the use of a third "join" or "intersection" table
to create what is known as a many-to-many relationship.

The foreign keys are the same data type as the corresponding primary keys,
but without setting the field as a primary key. If you use an autonumber
primary key, then the corresponding foreign key needs to be a Number / Long
Integer data type (remove the default value of 0 for this field). You might
want to consider setting the required property to yes for foreign key fields,
unless you are well versed in working with outer joins in queries.

Once you create relationships between the tables (Tools > Relationships),
you should see that the foreign key fields are autopopulated when you add a
new record via a subdatasheet in a table or query. I recommend ALWAYS
checking off the option to Enforce Referential Integrity.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

yes the tables you have suggested are almost like exact copies of the 3
tables i have publishers (fkey-pub ID) authors (fkey-author id), booklist
(fkey - book id)
and i made the appropriate basic relationships from pub id and author id to
mention in the booklist table

theres a new table, like i said, that i imported from an excel spreadsheet.
i could easily just type and copy the information into each record manually
but i was wondering if there was an automatic way to do it for me.
it sounds a bit complex;
the new table i imported (bcz i was working on a diffrent project for sales
of books in excel) only contains book id, author, cost price, retail price.
it currently has a list of 20 books with the author, cost price, and retail
price filled in.

and like i said, the booklist table has a bunch of fields like the ones u
mentioned
tblBooks
pkBookID (primary key)
Title
fkAuthorID (foreign key to tblAuthors.pkAuthorID)
fkPublisherID (foreign key to tblPublishers.pkPublisherID)
YearPublished
Book Cost
Retail Price
+ any other attributes about the book that you wish to store.

i understand that i could just do it manually, but would there be an
automatic way of say for e.g.
if i just typed book id 001 (which is the primary key - every book is unique
obviously) and it brings along the rest of the information (book author ,
cost, retail price) from the record of book 001 from that other table i
imported into the database and saved it as a 4th idol table.

 

[Guest]

theres a new table, like i said, that i imported from an excel spreadsheet.

Is this new table just considered a temporary table? If so, and you need to
transfer the data from this table into an existing table, then you can use an
append query. Are you familiar with using append queries? This might be a
case for relaxing a required value for any fields that you do not have the
current information from a temporary table to append into.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

yes, i think you understand exactly what im talking about now
it is just a temporary table... after all the infro is transfered into the
booklist, all future information will be generally stored in the book list
directly. but for the first 20 books id like to have it transfered in such a
way using relationships or queries or whatever needs to be done.
u mentioned append queries.
i am not so familiar with the more complex queries so could you please
expand on that and tell me what to do?

thanks so much in advance
hfarouki

 

[Guest]

An append query is considered an action query. Other examples of action
queries include: Update (used to update existing records), Delete (used to
delete records) and Make Table (used to make a new table). An Append query is
used to add new records to an existing table.

Here is a KB article with some fairly limited information:
HOW TO: Convert a Select Query to an Action Query (in Access 2000)
http://support.microsoft.com/?id=304355

I added the parenthesis around the "in Access 2000" part, since this
information applies equally well to other versions of Access. This link
provides a bit more information:

http://office.microsoft.com/en-au/assistance/CH063653171033.aspx

The first choice takes you to an article that should be quite helpful:
http://office.microsoft.com/en-au/assistance/HA011860631033.aspx


Note that action queries do not return a visible recordset when run.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

yes! perfect those articles helped alot
especialy:
http://office.microsoft.com/en-au/assistance/HA011860631033.aspx

i just have one glitch.
it automatically inserted the info i wanted of the 20 books with the cost
price and retail price in the books table from tht imported temporary table..
but wen i run the append query or open it again later (i managed to open it
again by right clicking and opening in design view) but if u normaly click to
open it or press run query an error msg comes up:

Msoft office access cant append all the records in the append query (even
though it did update everything i wanted)
it goes on to say:

msoft office access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 24 records to the table due to violations, 0 record(s) due
to lock violations, and 0 record(s) due to validation violations. Do you want
to run the query anyway?
to ignore the error(s) and run the query click yes (then theres the no
button as well)
n.b when i press yes, nothing opens (the query is suposed to open but it
doesnt)

i went back and made sure the field types were the exact same and it stil
brings tht error msg.

so what i did. is made an apend query like u suggested and followed
instructions from tht article on the link u suggested which i have copied
above.

i made an append query begins with the imported table and the 4 fields i
wanted for the 20 records to be copied into the Booklist table. and the
"append to" fields in the query corresponding to the 4 fields from the import.

so why does the error msg come up and doesnt allow me to open the query.?

 

[Guest]

I think you mentioned in an earlier post that you were appending the primary
key value, correct? This should work only the first time. If you then
attempt to run the append query a second time, with the same data in the
source table, one would expect a key violation, no? By definition, a primary
key MUST be unique and non null. You cannot add the same PK value twice. I'm
thinking that you might have tried to do this by "opening" the query more
than one time.

so why does the error msg come up and doesnt allow me to open the query.?

Action queries do not return a normal recordset like a SELECT query does.
So, your two choices include either opening the query in design view, or
running it.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

PS.
The same error would occur if you have some other field set with a unique
index: Indexed (No Duplicates). In such a case, the append operation can be
run only one time, until you change the source data.

I hope that makes sense.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html

 

[Guest]

oh okay i understand now.
gr8. ya replacing both book ids more than once since both are primary keys.
only can happen once.. i just tried it.
i then removed the field of the book id from the apend query and only kept
the cost, retail columns and it allowed me to run the query without any
errors..
but everytime i ran it.. it added the 20 books with the information relating
to it.. so idecided tto return it back to how it was with that error msg.

just one last thing... now my autonumber... the next record i type in from
th book list aftr th 20 books starts at 81 bcz i ran the query with no errors
3 times wen i tried it wen i took off the book id field from the query.
so is there a way to say... restart the numbering for autonumber of the
primary field ? cz rite now i have from book id 1 to 20. then the new ones
start from 81...82...etc

 

[Guest]

but everytime i ran it.. it added the 20 books with the information relating

to it.. so i decided to ....

Wouldn't you have been quite surprised if you had gotten any other result?
After all, an append query is designed to add records to a table.

so is there a way to say... restart the numbering for autonumber of the
primary field ? cz rite now i have from book id 1 to 20. then the new ones
start from 81...82...etc

You usually should not worry about gaps in an autonumber field. An
autonumber is considered a *meaningless* surrogate key. People get into
trouble as soon as they attempt to assign any meaning what-so-ever to an
autonumber data type. On your forms, I recommend setting the visible property
for an autonumber field to No.

In answer to your question, if you compact the database (Tools > Database
Utilities > Compact and Repair Database), an autonumber field set to
increment (as opposed to randomize) will start after the last used value.
This means that if you really care about it filling in the gaps, starting
with 21, then you need to delete any existing records with a value greater
than 21, and then perform the compact and repair operation. Thus, if you have
some record with, say 84 as the highest value, a compact and repair will
result in the next number being 85 (even if there are no records in-between
20 and 84). On the other hand, if you delete record # 84, so that you now
only have records # 1 through 20, and you do the compact and repair, you
should find that the next assigned number is 21.

Some additional reading for you:
See "The case for the Surrogate Key" here (article # 4):

http://www.access.qbuilt.com/html/articles.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

thank you so much!!!
you should come teach a course on access for a university in montreal sometime
great help
thanx
hfarouki

 

[Guest]

You're very welcome. Glad I could help.

you should come teach a course on access for a university in montreal sometime

Sure, if you can arrange for a nice stipend! <smile>


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________