Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005

    Unanswered: Table Design Problem (I think!)

    I have 2 tables

    The customer table contains details relating to the customer.
    The comments table contains comment information relating to the customer in the customer table.
    They are linked in a one-to-many relationship (one customer has many comments)
    However, in the comments table I have a field "Publish" which is type Yes/No. For each customer I only want to ever have one Yes but as many no's as required.

    The reason for this is....Comments are entered for the customer on a daily basis, however at the end of every month 1 comment for each customer has to go onto a managers report.

    I would really appreciate any advice on this.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so add you column to the comments table, probably as a boolean value., when you select that column as the one to be reporterd run a an update query agains the commments column to clear any other comments for that company currently set for publication

    update DTComments set ReportFlag=False where reportflag=true and customerid=blah and commentid<><currentcommnetid>

    when you do your report, just in case some sneaky so and so has flagged more than one comment consider using a select top 1 & group by CompanyID.

    after the report has run (and has been succesfully printed or distributed) clear the reportflag. As an alternative you might want to include a datereported as part of the commnet column, that way round if the printer fowls up the report you can reg-gen it. You could also report that comment as as an annual review showing each months whinge (sorry comment) from the sales team.

  3. #3
    Join Date
    Jun 2005
    Thanks for you help. I'll give it a go!

Posting Permissions

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