Append Query

[phil009]

Hi everybody, I have a question about writing an append query. I currently have a table with data from 96,97,98,99,00,01,02,03,and 04. My company is sent a new excel sheet every week with the new data (in this case 2004) as well as the rest of the year. An example would be this is week 23 so when they send us the information it is not just week 23 but weeks 1 to 23 that we receive. I then try to run an append query to simply write over the top of my table data but instead of changing it the query just adds it on to the bottom of the worksheet. This is my first append query that I have ever tried so I am sure that I am doing something wrong that is very basic. If anybody knows what is going wrong it would be much appreciated.

Thanks a lot,
Phil

 

[phil009]

I don't know if this will help but my SQL looks something like below. I didn't mention it before but I am running my append query off of another query that searches the data we are sent for only "2004" because sometimes we are accidently sent 2003 data mixed in with 2004. If anybody sees the reason for my problem the help would be much appreciated.

Phil

INSERT INTO [first] ( Railroad, Railroad2, [Year], Year2, Commodity, Commodity2, [W 1], [W 2], [W 3], [W 4], [W 5], [W 6], [W 7], [W 8], [W 9], [W 10], [W 11], [W 12], [W 13], [W 14], [W 15], [W 16], [W 17], [W 18], [W 19], [W 20], [W 21], [W 22], [W 23], [W 24], [W 25], [W 26], [W 27], [W 28], [W 29], [W 30], [W 31], [W 32], [W 33], [W 34], [W 35], [W 36], [W 37], [W 38], [W 39], [W 40], [W 41], [W 42], [W 43], [W 44], [W 45], [W 46], [W 47], [W 48], [W 49], [W 50], [W 51], [W 52] )
SELECT Query1.RRCODE, Query1.RRCODE, Query1.YEAR, Query1.YEAR, Query1.TYPECODE, Query1.TYPECODE, Query1.[Week 1], Query1.[Week 2], Query1.[Week 3], Query1.[Week 4], Query1.[Week 5], Query1.[Week 6], Query1.[Week 7], Query1.[Week 8], Query1.[Week 9], Query1.[Week 10], Query1.[Week 11], Query1.Week12, Query1.[Week 13], Query1.[Week 14], Query1.[Week 15], Query1.Week16, Query1.Week17, Query1.[Week 18], Query1.[Week 19], Query1.[Week 20], Query1.[Week 21], Query1.[Week 22], Query1.[Week 23], Query1.[Week 24], Query1.[Week 25], Query1.[Week 26], Query1.[Week 27], Query1.[Week 28], Query1.[Week 29], Query1.[Week 30], Query1.[Week 31], Query1.[Week 32], Query1.[Week 33], Query1.[Week 34], Query1.[Week 35], Query1.[Week 36], Query1.[Week 37], Query1.[Week 38], Query1.[Week 39], Query1.[Week 40], Query1.[Week 41], Query1.[Week 42], Query1.[Week 43], Query1.[Week 44], Query1.[Week 45], Query1.[Week 46], Query1.[Week 47], Query1.[Week 48], Query1.[Week 49], Query1.[Week 50], Query1.[Week 51], Query1.[Week 52]
FROM Query1, [first]
ORDER BY Query1.RRCODE, Query1.YEAR, Query1.TYPECODE;

 

[OhioSteve]

First, let me give you a short answer to your initial question. You seem to want to have a record for each week. Then you want to update that record over time. You are doing an "append" query to complete this task. That is not the correct type of query for this task. In order to update existing records, you need to do an "update" query, not an "append" query.

Second, let me give my overall impression of your code. You have many fields with names like "x1", "x2" and "x3". Also your query looks extremely long and complex. Finally, your field names imply that you might be storing the results of calculations in tables. These things make me suspect that you have systemic problems with your database. You might want to read more about relational databases, consult with a colleague, or ask us more about how to setup your database.

 

[phil009]

Hi guys, someone in my office helped me solve the problem using an update query. Thanks for the help though.

Phil

 

[lespaul]

That still doesn't resolve your data issues though. You should still look into relational database design theory:

'The Fundamentals of Relational Database Design'

HTH

leslie