Link CSV file created query append query to append data

G

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]));
 
G

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 said:
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]));
Click to expand...
 
G

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 said:
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 said:
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]));
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A query based on a query and a Link table - Problems connecting 3
populate table through query or something??? 1
Can't data entry when form is based on a avg. query? 4

Top