Results 1 to 4 of 4
  1. #1
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62

    Cool Select Records to Copy from Table 1 to Table 2

    I have two tables that are initially joined with a nested left join that takes every record from Table 1 and every record related by the [Job #] field from Table 2 and appends them to Table 3 where the [Job #] does not yet exist in Table 3.

    After this initial commit, I want the user to be able to open up a form that allows them to move additional records from Table 2 into Table 3 (ones that aren't in Table 1). I'm wondering what the best way to do this would be.

    If I could display a list of all the records in Table 2, and the user could just put a check next to them then click "commit", that would be the ideal situation.

    I could go into table 2 and add a column with a Yes/No, and then write a query that will append every record from Table 2 to Table 3 that has a "yes" in that field, but I don't know how I can let the user do this through a form and not the table itself. Plus, I don't want to make permanent changes to the source table; I just want to select them in the form interface, but leave them "unmarked" in table 2.



    I hope this was a clear enough explanation.

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    I hope this was a clear enough explanation.
    Not really. From your description it sounds like you intentionally want to scatter all kinds of duplicate records across multiple tables within your application. Trying to understand why you would want to do this. Maybe I'm missing something?

  3. #3
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Bear with me on this wild ride:

    The current process in this application is to import a Forecast table, which contains information about jobs that are likely to be run in the next month. "Jobs" are a certain quantity [Qty] of a part [Part #] and are uniquely identified by their [Job #]. Additionally, a report is exported from Epicor's Vista containing a list of "Open Jobs", which are Jobs that have been received by the company and are therefore available to begin work on.

    These two tables are compared. All of the forecast jobs are added to the WorkingTable, and if they have a match by Job # in the OpenJobs table, the additional information provided by the Vista report for that Job # is added to the record as well.

    The Forecast and OpenJobs tables are dynamic; the Open Jobs report will be run every few days to check if new jobs have been opened, and these new jobs are appended to the table. Then, the application will check again to see if these new jobs match with anything from the forecast, and then append them to WorkingTable. People can add jobs to the Forecast label with a form, and again the same check is run and the records appended.

    The purpose of the WorkingTable is for people to track information about the jobs as they are being processed throughout the month. So they fill out fields that track the dates of certain markers throughout the process, such as off-machine date and shipped date.

    At the end of the month, the OpenJobs and Forecast tables are flushed, while the WorkingTable remains as an ongoing archive of jobs.

    It's a big dirty mess, but it is built to patch together the strange archaic processes and odd requirements I am working with.
    Last edited by BDibz; 04-18-2018 at 11:51 AM. Reason: typo

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    OK that makes more sense. My first inclination would have been to add the boolean (Yes/No) field to the second table like you mentioned in your first post, but you said you don't want to go that route and I'm wondering why. Is it because when you "Flush" this table at the end of the month you're actually deleting the entire table then re-importing updated data into a new table with the same name?

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

Similar Threads

  1. Replies: 83
    Last Post: 11-29-2016, 09:38 AM
  2. Getting table records to copy to another table
    By sarahc25 in forum Programming
    Replies: 11
    Last Post: 01-02-2016, 03:33 PM
  3. copy records within the same table
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 01-02-2012, 10:33 PM
  4. Replies: 3
    Last Post: 10-24-2011, 11:42 AM
  5. Replies: 2
    Last Post: 04-02-2010, 07:42 AM

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