Results 1 to 10 of 10
  1. #1
    IsmQ is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    5

    Archive - Delete Query

    Hello,



    I recently tried to create a query that would take data from a linked table and append it to a structure copy of the initial table. The query pulls the data, and it appears on the query tab, but does not archive the data into the table that the query is appending into. Has anyone encountered this problem and if so what steps can be taken to create a query that effectively updates my archive table?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum

    Suggest you post the query SQL used and describe what does happen instead
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    IsmQ is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    5
    Thanks,
    I have the following as the append query:
    INSERT INTO [Daily Room Log Archive] ( [Report Date] )
    SELECT [Daily Room Log].*, [Daily Room Log].[Report Date]
    FROM [Daily Room Log]
    WHERE ((([Daily Room Log].[Report Date])<Date()-60));


    I have the following as the delete query:
    DELETE [Daily Room Log].*, [Daily Room Log].[Report Date]
    FROM [Daily Room Log]
    WHERE ((([Daily Room Log].[Report Date])<Date()-60));

    It seems like the query runs correctly because the query tabs have the correct data after I run them. Ignoring the delete query for now, the expectation is that this data is appended to the Daily Room Log Archive when running the append query, but it does not.

    Does this kind of describe the issue properly?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry what does this mean?
    It seems like the query runs correctly because the query tabs have the correct data after I run them
    As you only are importing one field, your query is incorrect. Try this:
    Code:
    INSERT INTO [Daily Room Log Archive] ( [Report Date] )
    SELECT [Daily Room Log].[Report Date]
    FROM [Daily Room Log]
    WHERE ((([Daily Room Log].[Report Date])<Date()-60));
    If it still fails, check the following:
    Do the source and destination have the same datatype (date/time)?
    Is 'Report Date' the PK field? If so, perhaps those dates already exist in the destination table
    If not, perhaps another field is required but not being populated
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    IsmQ is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    5
    Gotcha, on the query datasheet view, not the query tabs, the correct data that I want pulled shows up, it just does not append to the other datasheet. I double checked, currently, the destination is a structure copy of the source with matching fields and everything.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK, so does it work if you modify the query as suggested? Or do you want to populate all fields.

    The question still remains about why you are making a COPY of your table data
    Also I still have no idea what you mean by 'query tabs'
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    IsmQ is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    5
    I just phrased it wrong, I meant the datasheet view of the query when I said query tab. But aside from that, I am trying to create an archive from a SharePoint linked table to a new archive table. The archive table I want populated was created by making a structure copy of linked table, providing me with a table that has the correct fields with correct datatypes, but no data. So the issue I still have is that the data pulled from the linked table will show up on the datasheet view of the append query after I have ran it, but it does not populate the archive sheet.
    If I am not mistaking, my query should retrieve all the data fields up to a certain date based on the "Report Date" field, which, again, I see on the query's datasheet view after running the query.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I know nothing about Sharepoint but I doubt that's the issue

    I still see no reason to make a copy of the table but lets move on ....

    My amended query should populate the Report Date field ONLY for all records where the report date is at least 60 days in the past

    Questions from earlier posts:
    So do you just want to populate one field or all of them?

    Is 'Report Date' the PK field? If so, perhaps those dates already exist in the destination table

    If not, perhaps another field is required but not being populated

    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by IsmQ View Post
    INSERT INTO [Daily Room Log Archive] ( [Report Date] )
    You are inserting into [Daily Room Log Archive] table field [Report Date] something. And this something is multi-field result of following query. Error 1 - the number of fields you list for target table must equal with number of fields returned by query.
    Quote Originally Posted by IsmQ View Post
    SELECT [Daily Room Log].*, [Daily Room Log].[Report Date]
    Your query returns all fields from table [Daily Room Log], and additionaly a field [Report Date] from same table second time. Error 2 - you can't have same field with same name twice. And why you need this anyway?
    Quote Originally Posted by IsmQ View Post
    FROM [Daily Room Log]
    WHERE ((([Daily Room Log].[Report Date])<Date()-60));
    Now some uncertainity. Does the table [Daily Room Log Archive] have an autonumeric PK? When yes, then does table [Daily Room Log] have an autonumeric PK too? This will not work, as you can not insert values into autonumeric field. When this is the case, you have to change PK in archive table to Long Integer.

    So the query must be something like
    Code:
    INSERT INTO [Daily Room Log Archive]
    SELECT [Daily Room Log].*
    FROM [Daily Room Log]
    WHERE 
    ((([Daily Room Log].[Report Date])<Date()-60)) AND
    ([Daily Room Log].[PrimaryKey] NOT IN (SELECT [PrimaryKey] FROM [Daily Room Log Archive]));
    The 2nd condition prevents entering same record into archive twice.
    Last edited by ArviLaanemets; 06-14-2018 at 12:51 PM.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Arvil
    You have assumed the OP wants all the fields which may be correct but as he/she hasn't answered my question, we don't know
    If you are correct, something like this should also work

    Code:
    INSERT INTO [Daily Room Log Archive]SELECT [Daily Room Log].*
    FROM [Daily Room Log Archive] RIGHT JOIN [Daily Room Log] ON [Daily Room Log Archive].[PrimaryKey] = [Daily Room Log].[PrimaryKey]
    WHERE 
    ((([Daily Room Log].[Report Date])<Date()-60)) AND
    ([Daily Room Log Archive].[PrimaryKey] Is Null));
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  2. Replies: 7
    Last Post: 09-16-2017, 04:01 PM
  3. Archive and Delete Queries
    By Canadiangal in forum Access
    Replies: 3
    Last Post: 04-20-2013, 02:34 PM
  4. Replies: 2
    Last Post: 01-24-2012, 02:16 PM
  5. Delete button with archive Table
    By tlyons in forum Forms
    Replies: 4
    Last Post: 07-08-2010, 12:18 PM

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