Link CSV file created query append query to append data

Discussion in 'Microsoft Access Queries' started by Guest, Jul 1, 2005.

  1. Guest

    Guest Guest

    I have linked a csv file as a table. I generated an append query then I
    generated a Make-Table query. I then get a message that says "The existing
    table will be deleted before you run the query. This generates all the
    information every time I run the query. My problem is the linked file does
    not have 2 crucial fields that I need to be able to sort with and control the
    output. So the csv file has info like grower number, cherry sizes, variety,
    date, pool etc. I need to sort the data by a Run order field that I can
    number 1,2,3, etc and then be able to use a data field yes/no to check off
    what has been packed so it does not come back when I run the query again. any
    ideas?
    here is the SQL view
    SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
    [RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
    [RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
    [RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
    [RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
    [RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
    tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
    FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
    Query].LotNum
    WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));
     
    Guest, Jul 1, 2005
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    Are you relying on the csv file being presented to you in any particular
    order? If so, you may want to add an incrementing surrogate key (or row id)
    to the csv file before you import it. If this is what you want, post back.

    Alternatively there may be a field in the CSV file that can be used to
    determine order.

    Database tables by definition, cannot be relied upon to store data in any
    order. Queries (in particular the ORDER BY clause) determine the ordering of
    data presented to the end user (you). So you will need to establish before
    you start using the data, precisely what order you want, and how it could be
    derived from data that could potentialy be stored in random order.

    Actually on re-reading, it could be that the make-table action query is
    blowing away useful information you've added after the import. Why not
    append new data to the existing table, but uniquely identify the new data
    with a metadata field like the date/time of import or the file name (if it is
    unique) coupled with your existing method for unquely identifying each row of
    data. So currently you may have:
    Fld1 Fld2...Fldn in your CSV file and your query might look like:
    INSERT INTO tblABC(F1, F2,...Fn)
    SELECT Fld1, Fld2,...Fldn
    FROM tblCSV
    this might change to:
    INSERT INTO tblABC(F1, F2,...Fn, FImportDateTime)
    SELECT Fld1, Fld2,...Fldn, Now() as ImportDateTime
    FROM tblCSV



    "esparzaone" wrote:

    > I have linked a csv file as a table. I generated an append query then I
    > generated a Make-Table query. I then get a message that says "The existing
    > table will be deleted before you run the query. This generates all the
    > information every time I run the query. My problem is the linked file does
    > not have 2 crucial fields that I need to be able to sort with and control the
    > output. So the csv file has info like grower number, cherry sizes, variety,
    > date, pool etc. I need to sort the data by a Run order field that I can
    > number 1,2,3, etc and then be able to use a data field yes/no to check off
    > what has been packed so it does not come back when I run the query again. any
    > ideas?
    > here is the SQL view
    > SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
    > [RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
    > [RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
    > [RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
    > [RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
    > [RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
    > tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
    > FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
    > Query].LotNum
    > WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));
    >
     
    Guest, Jul 1, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    First of all thank you for your response. I need to accomplish 2 things. 1-
    Be able to use a column (run order) to sort by number meaning I want a column
    where I can assign a row with any given number (1,4,7,2,3,6,5,etc) then sort
    numerically to come up with my run order in numeric sequence. Then I need to
    use the "package" column to checkmark the fruit already run so that
    information does not appear in my query. Now with my previous question I
    can't accomplish this with the make table query.

    "GPO" wrote:

    > Are you relying on the csv file being presented to you in any particular
    > order? If so, you may want to add an incrementing surrogate key (or row id)
    > to the csv file before you import it. If this is what you want, post back.
    >
    > Alternatively there may be a field in the CSV file that can be used to
    > determine order.
    >
    > Database tables by definition, cannot be relied upon to store data in any
    > order. Queries (in particular the ORDER BY clause) determine the ordering of
    > data presented to the end user (you). So you will need to establish before
    > you start using the data, precisely what order you want, and how it could be
    > derived from data that could potentialy be stored in random order.
    >
    > Actually on re-reading, it could be that the make-table action query is
    > blowing away useful information you've added after the import. Why not
    > append new data to the existing table, but uniquely identify the new data
    > with a metadata field like the date/time of import or the file name (if it is
    > unique) coupled with your existing method for unquely identifying each row of
    > data. So currently you may have:
    > Fld1 Fld2...Fldn in your CSV file and your query might look like:
    > INSERT INTO tblABC(F1, F2,...Fn)
    > SELECT Fld1, Fld2,...Fldn
    > FROM tblCSV
    > this might change to:
    > INSERT INTO tblABC(F1, F2,...Fn, FImportDateTime)
    > SELECT Fld1, Fld2,...Fldn, Now() as ImportDateTime
    > FROM tblCSV
    >
    >
    >
    > "esparzaone" wrote:
    >
    > > I have linked a csv file as a table. I generated an append query then I
    > > generated a Make-Table query. I then get a message that says "The existing
    > > table will be deleted before you run the query. This generates all the
    > > information every time I run the query. My problem is the linked file does
    > > not have 2 crucial fields that I need to be able to sort with and control the
    > > output. So the csv file has info like grower number, cherry sizes, variety,
    > > date, pool etc. I need to sort the data by a Run order field that I can
    > > number 1,2,3, etc and then be able to use a data field yes/no to check off
    > > what has been packed so it does not come back when I run the query again. any
    > > ideas?
    > > here is the SQL view
    > > SELECT tblLotList.LotNum, [RSM-Recv Query].TicketNum, [RSM-Recv Query].Bins,
    > > [RSM-Recv Query].RecvDate, [RSM-Recv Query].CullP, [RSM-Recv Query].BrineP,
    > > [RSM-Recv Query].P080, [RSM-Recv Query].P085, [RSM-Recv Query].P090,
    > > [RSM-Recv Query].P095, [RSM-Recv Query].P100, [RSM-Recv Query].P105,
    > > [RSM-Recv Query].P110, [RSM-Recv Query].P115, [RSM-Recv Query].P120,
    > > [RSM-Recv Query].P130, [RSM-Recv Query].PoolNum, [RSM-Recv Query].Variety,
    > > tblLotList.Packed, tblLotList.[Run Order] INTO [Append Data]
    > > FROM tblLotList INNER JOIN [RSM-Recv Query] ON tblLotList.LotNum = [RSM-Recv
    > > Query].LotNum
    > > WHERE ((([RSM-Recv Query].Variety)=[Enter Variety]));
    > >
     
    Guest, Jul 5, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bryan
    Replies:
    1
    Views:
    161
    [MVP] S. Clark
    Nov 17, 2003
  2. Vance

    Append Query... Can't Create a Unique Number to Append

    Vance, Dec 10, 2003, in forum: Microsoft Access Queries
    Replies:
    8
    Views:
    386
    Michel Walsh
    Dec 17, 2003
  3. Greg Clements

    can't append records in append query

    Greg Clements, Jul 2, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    317
    Ted Allen
    Jul 2, 2004
  4. Mandi

    Append query - only want to append new records

    Mandi, Feb 24, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    545
    Mandi
    Feb 24, 2005
  5. dee

    Append to Append Query

    dee, Jan 25, 2008, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    135
Loading...

Share This Page