Results 1 to 15 of 15
  1. #1
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33

    I have a problem with add new record to database.

    Currently, I have two tables, one is all record of products which I called "N", and other is my watch list which I called "watchlist". I can update all records in "watchlist" from "N". The problem is I cannot update the new products that does not exist in "watchlist". What are ideas to do this function?


    Thank you.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use a left join between your N table and watchlist table. this will display all records in N regardless of whether there is a record in watchlist

    To see which are new records (i.e. in N but not in watchlist) then add a criteria for any of the fields (doesn't matter which one) in watchlist which is null

  3. #3
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Ajax View Post
    use a left join between your N table and watchlist table. this will display all records in N regardless of whether there is a record in watchlist

    To see which are new records (i.e. in N but not in watchlist) then add a criteria for any of the fields (doesn't matter which one) in watchlist which is null
    I think I need to use SQL to work it on. But what the relationship of them? Because the new records are not exist in watchlist, the relationship will be empty?
    from watchlist left join N on watchlist.ID = N.ID;?

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    yes sql and it is left join from N to watchlist - not the other way round. As to relationship it would be the ID, whatever that may be - I presme the same as when you wrote ' I can update all records in "watchlist" from "N".'

    You haven't explained your problem very well so I may have misunderstood what you want. Best thing to do is try it, and if not what you want, try rephrasing your question

  5. #5
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Ajax View Post
    yes sql and it is left join from N to watchlist - not the other way round. As to relationship it would be the ID, whatever that may be - I presme the same as when you wrote ' I can update all records in "watchlist" from "N".'

    You haven't explained your problem very well so I may have misunderstood what you want. Best thing to do is try it, and if not what you want, try rephrasing your question
    you're right. I will try it.
    "I can update all records in watchlist form n" that means my watchlist table have already the records that I want now. However, the N may have the new records later on. If I update the old one, the new records will not exist in watchlist. (I use the Update function and sql as inner join to two tables).
    By the way, is there a function that can replace the repeat record? I use the left join from two tables. It works, but it adds the same one and continues.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry that is an even more unclear explanation

    And no idea what you mean by a repeat record?

    Might be an idea if you post a screenshot of your table relationships and refer to that in your explanation

  7. #7
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Click image for larger version. 

Name:	A1.png 
Views:	18 
Size:	19.2 KB 
ID:	23550
    at first, I have two tables. A1 as all record.
    Click image for larger version. 

Name:	W.png 
Views:	18 
Size:	13.6 KB 
ID:	23551
    Watlistest only has one record.

    I use the sql as
    Code:
    INSERT INTO Watchlistest ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT  Jan 2013_Sales $], FROM A1 LEFT JOIN Watchlistest ON A1.NDC = Watchlistest.NDC;
    Click image for larger version. 

Name:	N.png 
Views:	18 
Size:	26.9 KB 
ID:	23552
    Then I get the result in Watlistest, but the first and second are the same. How could I replace it?

  8. #8
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    much clearer!

    to just add new records you would use

    Code:
    INSERT INTO Watchlistest ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT  Jan 2013_Sales $], 
    FROM A1 LEFT JOIN Watchlistest ON A1.NDC = Watchlistest.NDC
    WHERE Watchlistest.NDC is Null
    To amend existing records you would use

    CODE]INSERT INTO Watchlistest ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT Jan 2013_Sales $],
    FROM A1 INNER JOIN Watchlistest ON A1.NDC = Watchlistest.NDC[/CODE]

  9. #9
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Thank you, I got it! I will try more to see if there are the problems.

  10. #10
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry, was in a hurry - the second query should be an update query, hope you spotted that!

  11. #11
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Ajax View Post
    sorry, was in a hurry - the second query should be an update query, hope you spotted that!
    Ok, if I want to reverse it, such as A1 has only one, and Watchlistest have two records, why I use right join that does not work?

  12. #12
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know without seeing your code

  13. #13
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Ajax View Post
    don't know without seeing your code
    INSERT INTO Watchlistest ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT Jan 2013_Sales $]
    FROM Watchlistest RIGHT JOIN A1 ON A1.NDC=Watchlistest.NDC
    WHERE Watchlistest.NDC Is Null;
    just changed left join to right join

  14. #14
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    change to

    INSERT INTO A1 ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT Jan 2013_Sales $]
    FROM Watchlistest RIGHT JOIN A1 ON A1.NDC=Watchlistest.NDC
    WHERE A1.NDC Is Null;

  15. #15
    llgtjb001 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Sep 2015
    Posts
    33
    Quote Originally Posted by Ajax View Post
    change to

    INSERT INTO A1 ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT Jan 2013_Sales $]
    FROM Watchlistest RIGHT JOIN A1 ON A1.NDC=Watchlistest.NDC
    WHERE A1.NDC Is Null;
    INSERT INTO A1 ( [Brand/Generic], [Product Sum], Strength, Manufacturer, NDC, [MAT Jan 2013_Sales $]
    FROM Watchlistest RIGHT JOIN A1 ON A1.NDC=Watchlistest.NDC
    WHERE A1.NDC Is Null;

    why does not work?

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

Similar Threads

  1. New Record Problem
    By The Professor in forum Forms
    Replies: 6
    Last Post: 08-27-2014, 04:15 PM
  2. Replies: 2
    Last Post: 11-15-2013, 09:58 AM
  3. Goto Record Problem
    By The Professor in forum Forms
    Replies: 6
    Last Post: 02-15-2013, 04:39 PM
  4. Record Locking Problem
    By Grendall5383 in forum Access
    Replies: 9
    Last Post: 12-05-2011, 09:45 AM
  5. Problem deleting record
    By Vikki in forum Access
    Replies: 8
    Last Post: 02-26-2010, 11:07 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