Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Update query

  1. #1
    Join Date
    Aug 2003
    Posts
    31

    Unanswered: Update query

    Hi all, im a very basic user of access and i have designed a simple database and im stuck, hopefully someone will be able to help me.
    I will try and put as much detail as possible, but if you need anything else then please ask.

    What the database should do is a house rings me up and tells me that they need a teacher to come in. I then ring different teachers and then when one of them can confirm a date i update my database to show them going into each house. There is a cut off date of 30 days from when the request is made.

    I have 3 tables in a database, 1 called house name, 1 called main, and 1 called teacher.

    The house name table has the contact details for the house such as name, phone number, address.

    The teacher table has the details on a teacher, such as name, phone number, address etc.

    The main table has the fields, House, Members in house's name, last date teacher needed by, name of teacher, actually date, comments.

    So in the main table it might look something like this


    House Members Last date Teacher Date Comment
    A Matt 30/10/03
    A John 30/10/03
    A Tim 30/10/03
    B Ed 19/10/03
    B Vic 19/10/03
    C Nina 25/10/03
    C Tony 25/10/03
    C John 25/10/03

    So what i want to do is create a query with the fields, teacher, date and house in it.

    Then when i choose say House A, input teacher as Mr Smith, and the date as 11/10/03 i want it to update every "house A" with the details.

    Im not sure how to do this, whether i have to make another table, or if its an update query. Im not sure how to use SQL, but hopefully its something really easy.

    Thanks in advance

    Matt

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I would use an update query run by a button in a form with drop down menues. Fill in the form press the button and you're done... hopefully.

    Let me know if it is enough...
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Aug 2003
    Posts
    31

    Reply

    Hi Marco,

    I can create a query with those three in it, then i made it into an update query but when i went to run it, it updated 0 records, i didnt get a chance to input any data.

    This might sounds weird, do you think you could tell me how to do it (simply if poss)

    Cheers mate,

    Matt

  4. #4
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    If you post the db I might see what the problem is.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  5. #5
    Join Date
    Aug 2003
    Posts
    31

    DB

    I cant really post the db as it has confidential information in it.

    Do you think you could give me a few pointers on how to do it.

    Thanks so much mate

    Matt

  6. #6
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I'll try.

    When you create an update query you have to give two kind of information:

    1) what you want to update,
    2) what is that you want to put instead of (1)

    So... example:

    I want all records that contain "Whatever" to be changed in "Whoever".

    If you look at an update query you will understand what I mean. When an update query gives a result like yours could be because can't find any records with the filter you specified.

    How you do what I write?

    Well it is quite easy. Open a new update query (I assume that you are able to do it). Choose a table and for the moment choose a field.

    Now the difficult part:

    update to must be (as I understand) your combo box control in the form. Put the path of your form in there (e.g. Forms![YourForm].[YourComboBox].

    Next put Criterias. Let's say House A. This info is taken from the first Combo box (if i'm not mistaken).

    In the end you will have 3 fields (House Members Last date). The first will have as "update to" as first combo box and "criteria" as first combo box (HOUSE). The second and third just "Update to" as second and third combo box (MEMBERS & LAST DATE).

    I hope I made it clear. It should work perfectly.

    Cheers
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  7. #7
    Join Date
    Aug 2003
    Posts
    31

    Thumbs up Thanks

    Thanks for that Marco,

    I will try doing it at home tonight.

    If i cant get it working i will post you the database, but i will just delete all the phone numbers and email addresses.

    Thanks for your help mate.

    Matt

  8. #8
    Join Date
    Aug 2003
    Posts
    31

    Thumbs up I tried

    Hi Marco,

    I tried doing what you said but when i had typed in all the info you told me and went out of design view i wasnt able to input any data.

    If i attach the database do you think you could try and help me out, i have deleted the email and phone number fields, when you open it you will understand why.

    I will go over the problem again, but this time i will give the real examples.

    There will be lots of prisoners who are at each prison up to about 20, then when a judge has been booked to go and see the prisoner i want a form so that i only have to type the data in once and it will update every prisoners (at that prison) records.

    Thanks so much for your help mate

    Matt
    Last edited by mattygg; 09-17-03 at 17:20.

  9. #9
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Try and see if this is what you wanted.
    Attached Files Attached Files
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  10. #10
    Join Date
    Aug 2003
    Posts
    31

    Thumbs up Thanks mate

    Thanks mate you are a big help

    I have a couple of questions.

    How do you add in the field date?

    Also when i have to type in the prison/judge can i make them scroll boxes, as the prison name and judge name are being taken from another table.

    Thanks for your help.

    Matt

  11. #11
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    If I understand...

    First question:

    add a text box to the form and name it date. Then add the final date field in the update query. Change SQL to:

    UPDATE Main SET Main.Judge = [Forms]![ChooseJudge].[judge]
    WHERE (((Main.Prison)=[forms]![ChooseJudge].[prison]) AND ((Main.[Final Date])=[forms]![ChooseJudge].[date]));


    Second:

    You can change the combo box to scroll box by selecting combo, going into format, change into...

    Tell me if it's what you wanted.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  12. #12
    Join Date
    Aug 2003
    Posts
    31

    Thumbs up Thanks

    Thanks Marco,

    Its working exactly how i wanted it too.

    If i have any more problems i know where to come.

    Cheers mate,

    Matt

  13. #13
    Join Date
    Aug 2003
    Posts
    31

    I need your help again Marco

    Hi Marco,

    Everything that you have done so far is great, but i have another query.

    Remember on that form you did for me with the update query in it.

    Is it possible to make another button next to the update one which will input the data already in the query, into a mail merge in word, and then automatically email it to the address from another table.

    So the data in the query says Judge, prison and date.

    And the email address is in the prison table.

    so in the mail merge i will need all 4 fields.

    Cheers again mate

    Matt

  14. #14
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    What you can do is use the e-mail macro (sendobject) and connect it to a new form that contains fields you want to send. For instance you can make a button that opens a form related to the one you talk about, selecting only the fields you are interested in. Put an onload event that trigger the macro and your done. Obviously what will happen is that your mail program will open up a new message mask, so to really send the mail you will have to push the send button on that mask.

    Other way round (which I found wonderful) is the use of CDONTs. If the db is on a server 2000, you can see this link:

    http://www.mvps.org/access/modules/mdl0019.htm

    Cheers!
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  15. #15
    Join Date
    Aug 2003
    Posts
    31

    Hi Marco

    Hi again Marco,

    I need your help again.

    I didnt understand the CDONT thing that you showed me before, but that isnt too important in my database so i will leave it for now.

    What im looking to do now is produce a report which says these things.

    Prison No current cases Judge Date of visit Final date

    A 10 Mr X 10/6/03 09/07/03

    What my problem is with this is that it needs to do 2 things.

    Firstly i need a list of all the prisons, then i need it to count how many cases there are for each prison. So prison A might have 3 cases, prison B might have 2 cases. Then put this into a table.

    Secondly when it has final date for each prison i need it to compare all the dates for Prison A, and only type in the earliest one. I have no idea how to do this.

    Thanks so much for your help mate.

    Mat

Posting Permissions

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