Results 1 to 12 of 12

Thread: Query (97)

  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (97)

    Hi,
    I have two tables in a database Table A and Table B. Both tables contain the same fields REF and Hours but mostly different records. What id like to do is transfer the Hours from TableA to TableB where the REF numbers are common in both tables.

    Many thanks for the help,
    Rob.

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Hi,

    Create a new query in design view. Add table A to the design. On the 'Query' menu, select Append Query, then tell it to append records to Table B.

    Select the fields in Table A and match them to the corresponding fields in table B.

    In the criteria line for the REF field, use the builder (Right click the criteria line, choose'Build...') to select the REF field in Table B.

    Run the query.

    HTH

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

    Re: Query (97)

    Is there a unique index on REF? If so, try this:
    - Create a query based on Table A and Table B.
    - Join the tables on REF, i.e. drag a line from REF in Table A to REF in Table B.
    - Add the Hours field from Table A to the query grid.
    - Change the query into an update query using the Query menu.
    - In the Update To line, enter <!t>[Table B]<!/t>.[Hours] (replace Table B by the actual name of the table.)
    - If you want to update ALL records from Table A that have a corresponding record in Table B, select Query | Run now.
    - If you only want to update records from Table A that now have a blank value for Hours, enter Is Null, then select Query | Run.
    As usual, it is wise to create a backup copy before trying this.

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Hi Hans,
    I constructed the query like you said but nothing populates the "hours" field. 480 is the amount of records that share the same REF number in Tables A and B. When i run the update query it says "you are about to update 480 rows" at this point the query seems to have been successful. However when i go into table B nothing is in the "hours" field. Would you have any ideas about this??

    Many thanks,
    Rob.

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Hi Colm,
    I did what you said and when i go to run the query it asks me to enter the ref number of "Table B" in a parameter value box. When i click ok it says "you are about to append 0 rows" the hours field in table B remains blank. Have you any ideas as to whats wrong,
    Cheers,
    Rob.

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

    Re: Query (97)

    On looking back, I see that I got it wrong way around: the query I described attempts to update Table A instead of Table B. Sorry about that. Here is the (hopefully) correct description (modifications in bold):

    - Create a query based on Table A and Table B.
    - Join the tables on REF, i.e. drag a line from REF in Table A to REF in Table B.
    - Add the Hours field from Table B to the query grid.
    - Change the query into an update query using the Query menu.
    - In the Update To line, enter <!t>[Table A]<!/t>.[Hours] (replace Table A by the actual name of the table.)
    - If you want to update ALL records from Table B that have a corresponding record in Table A, select Query | Run now.
    - If you only want to update records from Table B that now have a blank value for Hours, enter Is Null in the Criteria line, then select Query | Run.

  7. #7
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Hi Hans,
    I altered the query but im still getting no data in the relevant field. The real names of the tables are A340600 and A330MPDUSE. Id like the hours in A330MPDUSE in the "Factored MPD MHRS" field to transfer across to the "Factored MPD MHRS" field in the A340600 given a common MPD REF. Here is a screen shot of the way my query looks. Again when i run the query it says it will "update 480 rows" but when i check the A340600 table everything is still blank. What format should the "Factored MPD MHRS" be in i have it set to number with a field size of double perhaps this is wrong??
    Cheers,
    Rob.
    Attached Images Attached Images

  8. #8
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Hi Rob,

    Just to be clear what you want to achieve - let's say table A has 5 records and table B has 10. All the REF entries in table A exist in table B. After running this query, do you want a) 15 records in table B (ie a combination of the two tables, where the REF field is equal in both tables) or [img]/forums/images/smilies/cool.gif[/img] 10 records in table B, where some records have new information updated from entries in table A?

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

    Re: Query (97)

    I don't think the field type is causing the problem. Try the following:

    - Open the query in design view.
    - Temporarily add the "Factored MPD MHRS" field from "A330-MPDUSE" to the query grid.
    - Enter a dummy value such as 1 in the Update To line.
    - Switch to datasheet view (using the View button on the toolbar or the View menu.

    Do you see the correct values in the second column? If you do, I don't understand why the update wouldn't work. If you don't, either the query design is incorrect, or the "Factored MPD MHRS" field doesn't contain values in the records of "A330-MPDUSE" with corresponding records in "A340600".

    Don't save the modified query, and don't try to execute it!

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    Thanks Hans that works fine now,
    Cheers,
    Rob.

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

    Re: Query (97)

    I'm glad it works. So what was the problem?

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (97)

    It turns out none of the matching MPD REFs had associated "Factored MPD MHRS" with them. I ran an update query for another hours field and the hours successfully added themselves in with the associated "MPD REF"

Posting Permissions

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