Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indicating More Than 1 Category per Contact Record (Access 2000)

    It has been a long time since I've had to the need to do this task. I tried searching for guidance on the best way of indicating more than 1 category per record but couldn't find it. So here I am seeking your help again.

    I have built a "marketing contacts" database and want to be able to indicate 1 OR MORE categories for each prospect/record, e.g., teacher, student, patient, Government, personal, etc. (Some records could each be included in four or five different categories.) I also want to be able to automatically add new categories to the categories list and then have the contact input "form" updated at the same time. I don't think listing each category in the contacts table design with a Yes/No field for each is the most efficient. Is there a better way to do this and still be user friendly for the person entering thousands of records? Any guidance you can give me will be much appreciated! Many thanks!...M.

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    What you describe is a many-to-many relationship. To implement it, you need three tables:

    - A prospects table.
    - A categories table.
    - An intermediate table with one record for each prospect-category combination.

    Data entry is through a form based on the prospects table and a subform based on the intermediate table. Take a look at the very simple demo attached to <post#=364,203>post 364,203</post#>. In the demo it's students and classes, in your database it would be prospects and categories.

    If you want the user to be able to add categories on the fly, you must use the On Not In List event of the categories combo box in the subform. You'll find several examples in this forum, for example in <post:=146,637>post 146,637</post:>.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Many thanks, Hans! You've come to my rescue once again! I appreciate the guidance and clear instructions and will get on it this afternoon. I'm sure it's the answer I'm looking for. Thanks again...Mary

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Hello again, Hans...I'm having trouble with this many-to-many relationship "subform" and can't figure out what I did wrong. When I try to delete a category from a current record, I get "Index or primary key cannot contain a null value". Also all of a sudden my Prospect ID autonumber in the main form is not working. I only have 5 test records in the database but have been pulling my hair out for hours trying to resolve the problem! Could you please take a look at the file and advise me what to do to fix it? Many thanks!

    Also, I have a question about running a parameter query to filter records by category for mailing labels but I don't want user to type in the category name (likelihood of misspelling, etc.), I want them to get a dropdown list of the categories to choose from when they go to run the query. I know I asked you about this a couple of years ago but can't remember what I need to do. If you prefer I can put this question in a new post. Please advise.

    Thanks again, Hans...Mary
    P.S. My file is 225k so I'm not able to attach it--perhaps I could send to you directly via Email?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Oops, Hans...I just sent this to myself! :-( Sorry, here goes...

    Hello again, Hans...I'm having trouble with this many-to-many relationship "subform" and can't figure out what I did wrong. When I try to delete a category from a current record, I get "Index or primary key cannot contain a null value". Also all of a sudden my Prospect ID autonumber in the main form is not working. I only have 5 test records in the database but have been pulling my hair out for hours trying to resolve the problem! Could you please take a look at the file and advise me what to do to fix it? Many thanks!

    Also, I have a question about running a parameter query to filter records by category for mailing labels but I don't want user to type in the category name (likelihood of misspelling, etc.), I want them to get a dropdown list of the categories to choose from when they go to run the query. I know I asked you about this a couple of years ago but can't remember what I need to do. If you prefer I can put this question in a new post. Please advise.

    Thanks again, Hans...Mary
    P.S. My file is 225k so I'm not able to attach it--perhaps I could send to you directly via Email?

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    It doesn't matter that you replied to yourself - like most Lounge "regulars", I check for new posts from time to time, not just for replies to me.

    Did you zip the database? See <post#=401925>post 401925</post#> for instructions on how to reduce and post a database.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Okay, I just downloaded a free zip software. Here is copy of the database I was talking about.

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    1) When you want to remove a category from a prospect, you shouldn't just clear the box, but click in it, then click the "Delete Record" button in the toolbar. Alternatively, click in the record selector to the left of the category, and press the Delete key.

    2) See the attached version. I created a form with a list box from which the user can select a category. The code that opens the report filters on this category. You can click the command button or double click an item in the list box.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Hans, you are brilliant as always! Thank you so much--I love the direct form for selecting a category for labels! I don't know why I didn't think about "deleting" record in the subform instead of trying to clear a category from the record, but I understand it now. What did you do to fix the ID autonumber? It now works! :-)

    Once again, thank you, thank you, thank you for coming to my rescue and for your quick reply and invaluable guidance! Sincerely, Mary

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    I didn't do anything specific about the AutoNumber, but I did do a Compact and Repair Database - this often solves minor problems.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Hello again, Hans...Could you advise me on something else with regard to this many-to-many relationship intermediate table. I tried putting a command button on the prospects form to delete a record (for the convenience of user who does not know Access). However, when it is clicked, I get "The record cannot be deleted or changed because a table "tblIntermediate" includes related records." Does establishing referential integrity in relationships have anything to do with this? Thanks for advising me!...Mary

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Yes, it has to do with referential integrity. If you select Tools | Relationships..., you'll see that the line joining tblCategories and tblIntermediate is thick, and that it has a 1 on the rblCategories side, and the infinity symbol on the tblIntermediate side. This indicates that referential integrity is enforced. Normally, this will prevent you from deleting a record in tblCategories as long as there are related records in tblIntermediate - they would be orphaned. But you can double click the line to view/edit the properties of the relationship. If you tick the check box "Cascade Delete Related Records" and click OK, you'll be asked whether you want to delete the related records from tblIntermediate when you delete a record from tblCategories.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    Many thanks, Hans...I've always been a bit confused about the referential integrity options (I guess I am afraid I'll lose data that I didn't want to lose, e.g. a particular category!). I trust doing the same thing you suggest to the tblProspects relationship with the intermediate table will work the same way? I want to be able to delete an individual prospect record along with that record's ID in the intermediate table (for related categories), but I don't want to lose the categories option as they are needed for all records. Thanks again, Hans. I appreciate all you've taught me!...Mary

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

    Re: Indicating More Than 1 Category per Contact Record (Access 2000)

    If you set Cascade Delete Related Records for the relationship between tblProspects and tblIntermediate, deleting a record in tblProspects will cause all related records in tblIntermediate to be deleted too, but it will not delete any records in tblCategories. Deleting a record in tblIntermediate will not delete any records in tblCategories or in tblProspects. The cascade only goes from the "one" side to the "many" side of the relationship, never from the "many" side to the "one" side.

Posting Permissions

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