Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Append query headache (2K and XP)

    I have an EMPTY table named tbl_Catalog. The table stores information about items donated for a fund-raiser. The people who are collecting the donated items have published a list on a web site. I copied the list into an Excel spreadsheet and then imported that into my DB as a new table, tbl_CatalogImport. This new table has just one field--ItemName--that I want to append to my tbl_Catalog. That field is common to both tables, and they are identical in every way, as far as I can tell. Both fields are "text," both have a field size of 255. There's no special formatting, no input mask, no...nothin'! The target table has a index (primary key), but the source table does not.

    When I try to run an append query to move the data in this one field from one table to the other--vai an append query--I get this error message:

    "...Access set 0 field(s) to null due to a type conversion failure, and it didn't add [any of my] records to the table due to key violations, 0 records due to lock violations, and 0 record(s) due to validation rule violations."

    OK, I give up--what am I tripping over? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> FWIW, I'll toss in some more information: The target table, as I mentioned earlier, is empty, but it does have relationships to two other tables.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append query headache (2K and XP)

    What is the primary key (if any) of the target table tbl_Catalog? If it is not an AutoNumber field, then adding only the ItemName field will leave the primary key empty, and that is not allowed.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Append query headache (2K and XP)

    Hello again, Hans! The primary key of the target table is, indeed, an AutoNumber field, so I suppose that's not the problem. Nonetheless, I've fixed the problem...although I must admit I do not fully understand why I had a problem.

    I went to the Relationships sheet and deleted the relationships that I'd built between the target table (tbl_Catalog) and two other tables. One table stores information about donors, and the other stores categories for the donations. (For example, "Furniture," "Be a Bookworm," "Civil War"....) Now that I've populated that one field, I suppose I can restore the relationships and move on.

    Thanks for the quick response! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Append query headache (2K and XP)

    It is likely that when you restore the relationships you will get a data integrity error. Any chance the imported data contains two identical records where the relationships in the other table are set up such that there can't be any duplicates? (or something similar) Check the indexes and primary keys on the other tables to see if duplicates are or are not allowed.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Append query headache (2K and XP)

    You are correct, sir! When I tried to reestablish the relationships, I got the error you foretold. This brings up a couple of new questions about this append query stuff, and I need to get my head straight on this matter because I'm sure I'll need to append more data as this project proceeds.

    First let me mention that, when the new data appeared in the heretofore-empty table, the AutoNumber field started with #106!! Guess I'd had some dummy data (from last year) in that table before and deleted it before I appended the real stuff.

    The relationships are among three tables: In the middle (so to speak) stands tbl_Catalog--a listing of items donated by businesses and individuals. On the right (I'm just looking at the Relationships window) I have tbl_Donors, with data on the sources of the items. On the left, I have tbl_Categories.

    "Categories" is nothing more than a lookup table with about 30 records. Each record (at this moment) has two fields: CatID (AutoNumber, Indexed (no duplicates)); and Category (names for each of the 30 categories). I'm reading between the lines of your reply, and I surmise that maybe I don't need a primary key field for this table at all...? A primary key field is (by definition?) indexed/no duplicates, right? So is this part of the problem with the append operation?

    The Donors table also has a primary key field, but it has many other fields. I've got the usual stuff: DonorLName, DonorFName, DonorTitle (Dr., Mr., Ms.). I've also borrowed a little from the Outlook model and added separate fields for DonorBusName, DonorBusAddr, DonorBusZIP...you get the picture. I've done this because some donors are businesses, and others are private individuals. (As a side issue, I'll ask whether you think this is a good idea or just plain overkill.)

    So...in the two related fields (Donors and Categories), yes, they are indexed/no duplicates. Is this the problem? Don't I need a primary index for Donors, at least? If having a field that is indexed/no duplicates gets in the way of appending, how does one ever append any data?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append query headache (2K and XP)

    Lucas,

    Your setup sounds OK. You should have primary keys in the Donors and Categories tables.
    The Catalog table should have at least fields corresponding to these two primary keys, and other fields as needed.
    You probably *don't* want a unique key (no duplicates) on the combination of those two fields - if you do, one donor will be able to contribute only one item in each category. You can use the AutoNumber field as primary key. If the table is empty, and you want the AutoNumber field to start with 1 again, compact the database.

    If you have a unique key on the Donor and Category ID's in the Catalog table, first remove the relationships in the Relations window, then remove the key form the table, then create the relationships anew.

    If you still have problems, compare the imported Excel table carefully with the Donors and Categories tables. Do the Donor and Category ID's correspond exactly to the values in the other tables?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Append query headache (2K and XP)

    Hans,

    Sorry, but I'm not tracking on some of what you wrote. "You should have primary keys in the Donors and Categories tables." But then you say, "You probably *don't* want a unique key (no duplicates) on the combination of those two fields." That sounds contradictory. If I use the AutoNumber field as the primary key in a table, I can't set it for "No duplicates" without removing that primary key field...right?

    The append operation involved ONLY the Catalog table, and I appended just one field: ItemName. I did not append any information about categories and donors. (We'll have to "connect those dots" later.) The original information was in this form: (example) "Handmade wicker chair donated by Fred Flintstone." I want to have separate tables for Categories, Items and Donors, so in the Excel spreadsheet I laid out three fields: Categories, ItemName, and Donors. Next I manually cut and pasted the donor information into the Donor field. (No doubt there's an easier way to parse this stuff, but I had only 97 records--it seemed faster to do it the old-fashioned way than to tinker around with parsing, given my skill level.) Next I imported the spreadsheet into a new table in the DB, but I imported only the ItemName field. Then I appended the ItemName data from the new table into the Catalog table that I'd already built.

    The donor field in the Excel spreadsheet still has "Fred Flintstone" or "Dr. and Mrs. Barney Rubble." My Access Donors table has a field to pick up that information via the Donors table's primary key (AutoNumber). The donors table has separate fields for title, first and last names, etc. I've also added address information, although I haven't seen that information in any written form yet. I hope the organization has that information, because I also want to be able to generate "Thank You" letters via mail-merge or automation.

    My objective is to create a data-entry form for the volunteers to input information about new donations. I want to make it easy to use: The volunteer will see fields for the item name, donor data, estimated retail value, minimum bid. The item number will increment automatically. Of course, any information they key into this form will automatically update on the corresponding tables (donors and the catalog).

    Thank you for your help!

  8. #8
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query headache (2K and XP)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    <hr>The append operation involved ONLY the Catalog table, and I appended just one field: ItemName. I did not append any information about categories and donors. (We'll have to "connect those dots" later.) <hr>
    Do you have Referential Integrity set up between the tables?
    If so it won't allow you to enter a new donation without the associated category and donor being filled in.

    Just a thought. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Append query headache (2K and XP)

    Lucas,

    If I understand you correctly, the following general rules should apply.

    1. Donor Table: You can have many donors, whether businesses or individuals. However, a unique donor can only exist once in the Donors table. Each donor can donate many items. You are using the Category as part of the definition of a category that a donor is donating.
    Thus, the relationship from Donor to Catalog is one to many.

    Your append (Updating data in the Catalog table) probably failed due to one of the following:
    a. You have the relationship of categories set such that a category can only be used once in the Catalog table.
    b. You tried to append a category name in the Catalog table that does not exist in the Category table.

    Take a look at your data to ensure that all categories are defined in the Category table and that you the Categories index is not set to unique.

    What happens if the same donor donates the same item at a later date.... something else to review.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Append query headache (2K and XP)

    Hm-m-m...as a matter o' fact, I think I did. So Access looked at the stuff coming in and saw it didn't have all its identification in order (donor, category) and turned it away. Is that pretty much it? So if I had included those other two fields, I might have carried out the append without all those problems...?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append query headache (2K and XP)

    Lucas,

    My remark "You probably *don't* want a unique key (no duplicates) on the combination of those two fields " was about the Catalog table. I think SteveH and GaryPSwanson have already given you a push in the right direction - Access expects a donor and category for each item in the Catalog table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •