Results 1 to 4 of 4
  1. #1
    Rickdel is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2

    Newbie Question, how can a re-use a table and query each week but populate with new import data?

    Hi Everyone,

    I tried to search for an answer, but maybe my syntax is wrong....

    I have created a table structure and data from a weekly Xls import. I have also created multiple queries of this table.

    I would like to re-use or copy the table structure and query structure each week but importing new data and renaming


    it with the new work week file name.

    It does not seem obvious to me how to do this in Access 2016?

    Thanks in Advance Rick

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your table should have a field that identifies the batch (week) of data - by date or whatever. Then each week when you append your records you would also insert that identifier data. I would not
    a) over-write this table every week as a new table (a make table query)
    b) rename this table every week regardless of how the data is input

    I would run either an append query as I mentioned, or if I only wanted data pertaining to a particular period, I'd run a delete query on this table before running my append. This all presumes you're using a query rather than some other type of data import. You didn't say how you were inputting the data although I suppose you might have an import specification, according to what you wrote.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Rickdel is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2
    Hi,

    The data is pulled out of Oracle to an XLS file, then imported to Access each week. So the table structure is something I want to keep and re-use each week and not have to create a new table of data over and over again each week. So in my mind, import data from XLS, hit F9 or something and then the table structure strips out the data I don't want and I end up with the correct data table with the new weekly data. It has about 4400 records and goes out the about column BF.

    I have 4 queries that need to run and spit out reports based on the data in that table above each week. So your append query sounds like it may work. I really do not need to save each table of data or query data each week once the reports are made. I just want to re-use those structures each time.

    Thanks in Advance, I'll give it a try tomorrow.

    Cheers! Rick

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why not just query the Oracle db from Access? It is an Oracle db?
    OK, if you have to put it in Excel first for some reason, that's one thing, but you didn't clarify how the data gets into Access. You have to remember that we can see nothing from here, and only know what you report, subject to understanding that. So are you importing (i.e. you are using the Get Data tool to import) or are you querying, or could you just link to the spreadsheet? You didn't say if the sheet data is over-written each time, a new sheet is created, or a sheet is appended to. It's all relevant to whatever suggestion that anyone here might be able to proffer.

    I'm pretty literal, so this means nothing to me
    the table structure strips out the data I don't want
    AFAIC, a table cannot do this at all. Tables only store data, they don't perform any actions worth mentioning.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-13-2019, 04:18 PM
  2. Replies: 1
    Last Post: 01-04-2018, 05:36 PM
  3. Replies: 7
    Last Post: 08-10-2017, 12:53 AM
  4. Import Data Every Week
    By adnancanada in forum Import/Export Data
    Replies: 2
    Last Post: 10-31-2015, 07:00 AM
  5. Replies: 2
    Last Post: 05-01-2014, 06:51 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