Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2011
    Posts
    5

    Unanswered: Dummy needs help

    I have two tables in a database. One is a contact list, the other is donation records.
    Editing and appending records is not a problem, but, the purpose in using access instead of excel was to eliminate redundancy and manipulate data more easily.
    Since typing out each name 12 times a year is rather redundant, I want to eliminate that by getting the name from the contacts table and appending that record key, along with the new donation info, in the donations table.
    I've tried doing an append query, but that wants to append the entire 1000 names. I want to append 1 record, not 1000.
    I want to search for 1 name in the contacts table, and have it insert the associated key record, and allow me to enter the new donation data, appending it to the donation table.
    Surely this is a common thing to do, but I cannot find the answer...
    Edit to add: I am using Access 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't store the name in the donation table
    a smarter call is to store the id of the donor in the donation table

    define a relationship between the donations and donor tables
    when you design your form for donations insert a combo or listbox that is bound to the donorid in the donations table, and uses the donors table as its recordsource. using the combo/list wizard should help you set that up


    when you need to retrieve the donor name use a join query

    select my,comma,separated,list,of,columns from mydonationstable
    join mydonorstable on mydonorstable.donorid = mydonationstable.donorid
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2011
    Posts
    5
    Okay, that worked. Thanks!
    Now I have another issue.
    Instead of appending Donations, it's overwriting previous records with the same name, so that I can only have 1 record for each name in the table.
    How can I make the form always append?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread please, that way round someone else following on can search the titles for possible resolutions.

    fwiw
    I don't think you should append data. a donation is a donation from a specific person / donor. by making each donation an individual contribution associated to the donor via the donor ID
    the donation ID could be, day the accounting reference.

    why would you want to store each donation separately?

    well ferinstance, what happens if the user wants to know who has made the most donations, not necessarily the biggest donations
    what happens if you want to analyse the donations by month or some other arbitary classification.
    by storing each donation separately you can slice and dice the data as required.

    my guess is that you are making the donor ID part of the primary key.. it shouldn't be, its merely a foreign key to the table donors. what makes a donation unique?

    I suspect you are going to benefit from getting to grips with the concept of normalisation, a key requirement for developing databases.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2011
    Posts
    5
    What you are describing is exactly what I'm doing, or trying to. Each donation is an individual record, with assoc. name, date, check#, amount, etc., and I do intend to make reports, with charts etc. showing monthly totals, etc.
    To do that, I need to add records to the table each time a donation comes in. Perhaps I'm not using the right word?
    I need to add new records, not overwrite them. How do I do that?
    In my doner table, I have the doner ID, f&l name, address, etc. All personal info.
    In the donation table, I have the doner ID, so that it is associated with the right person, and the check#,date, amount, etc. Each donation is a separate record within the donation table.
    I just need to be able to properly add records to the table. This is not a difficult concept. I just can't seem to make it do what I want it to do.
    I can add records to the table in datasheet view, but I need to be able to do it through the form, so that office help can enter data without messing around with the structures.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can you take a screen dump of the table defintions and post them here

    sorry I read append as appending data to an existing record.

    is this something as simple as you are editing the record, not creating a new record. when you add a new record how do you go about doing this. is there 'stuff' fromt he previous record still displayed? are you typing into a blank box
    if you look at the foot of a form there are usually some buttons and a text box. the text box indicates what record number this is (in itself a meaningless number as it displays the position of this record in this dataset. the buttons usually are record navigation ones. the button marked with an asterisk * usually is the one to add a new record, or scroll to the end of the recordset till you see blank controls.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2011
    Posts
    5
    Contacts tbl Donations tbl FundID tbl
    ID AutoNumber - ID Number Fund Text
    First Name Text \ ChkDate Date/Time -Code Number
    Last Name Text \ Check# Text / Description Memo
    Address Text \ Amount Currency /
    City Text \ Codes Number---/
    St Text - Doner Number
    Zip Number
    HomePhone Text
    WorkPhone Text Relationships
    CellPhone Text Contacts ID to Donations Doner
    Email Hyperlink Donations Codes to FundID Code
    Fax Text
    Website Hyperlink
    Other Memo

    When you have a table open, and you are in Datasheet view, at the left of the screen is a star beside the 'new record'. To add a new record, you simply type in the blank boxes on that line.
    In Forms, in Form View, there is a yellow star at the bottom of the page you can click to enter a new record.
    That's what I'm trying to with the Donation Form, basically, except I want to search the name from Contacts, put the ID from that name into the new record in Donations, and then type in the rest of the new record.
    My whole point was removing the redundant entry of names, something Access supposedly enables one to do. So surely this can be done.......
    I could just go write my own database program in Dbase or Foxplus or something, but Access was supposed to make all this easy.
    Edit: Sorry, the formatting of this screen didn't work.

  8. #8
    Join Date
    Jan 2011
    Posts
    5
    Ha! I figured it out. I needed to do a subform. Now it works perfectly.

Posting Permissions

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