Results 1 to 7 of 7
  1. #1
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58

    Query to Update 1 Table using data from another

    I have 2 Tables, table 1 has the last sequence No. The other has all the product data, I'm trying to find products that are greater than the Last Sequence No with some filtering criteria and then add those records to another Product table.



    I then will use the highest sequence No from the updated Product table to update the Last Sequence Table.

    The result was an empty query. I have attached a picture of the query,
    Attached Thumbnails Attached Thumbnails LastSequence.PNG  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Need data structure and sample data. Build tables in post (GoAdvanced button to get advanced post editor) or attach db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the purpose of ptmseq and LastSequenceNo? Perhaps you could describe your requirement in plain English and post some expected values and associated logic. Are you familiar with autonumbers?

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This sounds a bit convoluted in my mind.
    You can always look up the last sequence number (either in a query or with a DLookup or DMax depending on the data type) so storing it to then look it up in a separate table seems an unnecessary step, that might also get missed if the process isn't closely controlled. you will often read in database forums about NOT storing things that can be calculated, this is because the calculation will always be correct, a stored result may not be.

    Perhaps you could post up some dummy data from the tables to better see the best way to get to the end result?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Hi Everyone,
    I think I went on a tangent to what I was trying to achieve. The problem could be solved I thought with a match? query.
    I have a link to a sql database and table called ptmfile. It has about 90 odd fields. I'm interested in only 4.

    PTMFILE (Linked Table) The file has 17,000 records

    1. Part No
    2. Description
    3. Index Code (From which I sort the required parts for my access database, > "200" And <> "060" And <> "061" And <> "150" And <> "155" And <> "180". This work OK
    4. SeqNo

    trfPart Table

    1.Part No
    2. Description
    3. Index Code (From which I filter the required parts for my access database)
    4. SeqNo

    I want to add to the Access table tblPart with all products which are not in the tblPart but have been added to the PTMFILE since the last update (With the filtering applied) from the PTMFile Table.

    I tried several match type queries but could not get the result I wanted.

    Cheers
    Bill

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is linking unique identifier - IndexCode? If IndexCode is set to not allow duplicates, then just run an INSERT SELECT action. Only new IndexCode records will save to tblPart. Review https://stackoverflow.com/questions/...g-in-ms-access
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Thanks June7, that is exactly what was happening.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2018, 07:08 AM
  2. Replies: 1
    Last Post: 04-27-2016, 04:06 PM
  3. Replies: 1
    Last Post: 05-11-2014, 10:04 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 2
    Last Post: 01-31-2011, 08:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums