• Update query (Access 2000)

    Author
    Topic
    #360289

    Hi

    I cannot get my brain round a problem.

    I have two tables, one for people, one for ticket numbers (for a random raffle/lottery). Each person will have multiple tickets.

    I have a field in my people table which shows the date on which a person makes his last payment. If someone enters a value in this field, I want to change a Yes/No field called “Valid” in the Ticket field to show “no”. I have created a form (with a subform showing ticket numbers & Valid field) to do so, Set up a Leave event for the date field, but only manage to edit the first of the ticket nos, not all of them.

    I really can’t think how to resolve this.

    Can anyone help please.

    Colin

    Viewing 1 reply thread
    Author
    Replies
    • #542267

      You’re missing a table. You need a “join” table that holds a record with the keyID from the people table and a keyID (or ticket number) from the Tickets table. That allows you to create a person-ticket link, and that would be where you put any valid/invalid information, etc. If the tickets belong completely to an individual, you could make the ticket ID a unique field in the join table. If people can share tickets, then your key would be the combination of the person KeyID and the ticket.

      I don’t understand the payment business, so I can’t tell you where that field belongs. Are the people making payments on tickets?

    • #542299

      I’m assuming the ticket table has both the PersonID and a TicketID in it? That is, it shows which tickets have been assigned to which people?

      Anyway, what you are trying to do you shouldn’t do!!! The Ticket record should not contain any information related to the Person (other than the PersonID). This is basic Rules of Normalization stuff. You do a query joining the 2 tables, and then select those Tickets for which there is a LastPaymentDate for that Person.

      • #542772

        Thanks

        I was duplicating data, and looking for an easy solution.

        I’ve done it properly now.

        Ta

        Colin

    Viewing 1 reply thread
    Reply To: Update query (Access 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: