Results 1 to 2 of 2
  1. #1
    token_remedie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    13

    stuck on the best way to do this

    so I'm working with access's template of the library database, and so far so good, I've managed to import data into a new table to work with in the hopes of cleaning it and putting it in the main table called books. So the import table is called importtable, main table is books, and the history table is called transactions.

    What I'm trying to do is this:



    if books.[asset number] Inner Join importtable.[serial number]
    count < 1 check books.[location name] against importtable.[location name]
    if they're equal then delete the record from importtable.

    Else copy the qhole record from books to transactions delete the entry from books and append the entry from importtable to books.

    the hope is that basically if an asset has moved locations that will show up in transactions, and only the most recent location will be in the main table.
    I've kind of worked out the SQL for it, but it returns 0 entries, and asks if I want to update this table with 0 entries....so it's wrong somewhere, maybe I've been staring at it all too long. Ideally I'd like to have it in VBA but if I can just have a query and use docmd.runquery then that's fine too.

    Here's my dodgy code so far:

    Code:
    INSERT INTO Transactions ( [Asset Number], [Location Code], [Serial No] )
    SELECT Books.[Asset Number], Books.[Location Code], Count(Books.[Serial No]) AS [CountOfSerial No]
    FROM Books INNER JOIN importtable ON (Books.[Serial No] = importtable.[Serial No#]) AND (Books.[Asset Number] = importtable.[Asset Number])
    GROUP BY Books.[Asset Number], Books.[Location Code]
    HAVING (((Count(Books.[Asset Number]))>1));

  2. #2
    token_remedie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    13

    I actually think I did it

    Code:
    INSERT INTO Transactions ( CoCd, Class, [Asset Description], [Invent No], CostCentre, Plnt, Location, [Location Code], [Location Name], FundTyp, ProgSrc, SubClass, Vendor, Manufacturer, Cost, [W Start], Remarks, R2, Formula, [Asset Number] )
    SELECT importtable.CoCd, importtable.Class, importtable.[Asset Description], importtable.[Invent No], importtable.CostCentre, importtable.Plnt, importtable.Location, importtable.[Location Code], importtable.[Location Name], importtable.FundTyp, importtable.ProgSrc, importtable.SubClass, importtable.Vendor, importtable.Manufacturer, importtable.Cost, importtable.[W Start], importtable.Remarks, importtable.R2, importtable.Formula, importtable.[Asset Number]
    FROM Books LEFT JOIN importtable ON Books.[Asset Number] = importtable.[Asset Number] AND importtable.[serial No#] = books.[serial no]
    WHERE (((importtable.[Asset Number]) Is Not Null)AND books.[serial no] Is Not Null);

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

Similar Threads

  1. Can anyone help please... I am stuck
    By Casper2012 in forum Forms
    Replies: 7
    Last Post: 08-15-2011, 11:27 AM
  2. I am stuck already! Table Issue.
    By Kevo in forum Access
    Replies: 1
    Last Post: 06-19-2011, 07:17 PM
  3. Help I am stuck
    By Darkglasses in forum Database Design
    Replies: 10
    Last Post: 03-04-2011, 09:10 PM
  4. Stuck on Query
    By wes028 in forum Access
    Replies: 9
    Last Post: 01-14-2010, 08:33 AM
  5. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 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