Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

relationship

Discussion in 'Information Technology' started by hfarouki, Jul 28, 2006.

Tags:
  1. hfarouki

    hfarouki
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Relationship with Creator Blogs Oct 17, 2015
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Arts/Society What are your thoughts on the interaction of male / female relationship of marriage? Language, Arts & Culture Oct 15, 2013
    Relationship advice Love & Marriage Sep 30, 2012
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011

  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

    > 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
     
  4. hfarouki

    hfarouki
    Expand Collapse
    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.



    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "hfarouki" wrote:
    >
    > > 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
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

    > 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.
    >
    >
    >
    > "Tom Wickerath" wrote:
    >
    > > 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
    > > __________________________________________
    > >
    > > "hfarouki" wrote:
    > >
    > > > 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
     
  6. hfarouki

    hfarouki
    Expand Collapse
    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

    "Tom Wickerath" wrote:

    > > 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
    > __________________________________________
    >
    > "hfarouki" wrote:
    >
    > > 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.
    > >
    > >
    > >
    > > "Tom Wickerath" wrote:
    > >
    > > > 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
    > > > __________________________________________
    > > >
    > > > "hfarouki" wrote:
    > > >
    > > > > 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
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

    > 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
     
  8. hfarouki

    hfarouki
    Expand Collapse
    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.?



    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "hfarouki" wrote:
    >
    > > 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
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

    > 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.?
     
  10. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

  11. hfarouki

    hfarouki
    Expand Collapse
    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


    "Tom Wickerath" wrote:

    > 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
    > __________________________________________
    >
    > "hfarouki" wrote:
    >
    > > 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.?
     
  12. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

    > 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 i decided to 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
     
  13. hfarouki

    hfarouki
    Expand Collapse
    Guest

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

    "Tom Wickerath" wrote:

    > > 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
    > __________________________________________
    >
    > "hfarouki" wrote:
    >
    > > 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 i decided to 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
     
  14. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    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
    __________________________________________

    "hfarouki" wrote:

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

Share This Page