The query cannot be completed.

[macri]

Hello all,

I have an excel file linked in access and a table called main I've made a query to append the excel data (Table1) to the Main table as so:

INSERT INTO Main ( [Mth/Day], [DS/NS], Shift, Line, [Product Code], [Product Description], [Mat'l], Pcs, Quantity, [Total Run], [Std lbs/hr], [Actual Lbs/hr], [RD / QA Samples], [Scrap Code 1], [Scrap Lbs 1], [Scrap Code 2], [Scrap Lbs 2], [Scrap Code 3], [Scrap Lbs 3], Comments, [Total Scrap], [Scrap %] )
SELECT Table1.[Mth/day], Table1.[DS/NS], Table1.Shift, Table1.Line, Table1.[Product Code], Table1.[Product Description], Table1.[Mat'l], Table1.Pcs, Table1.Quantity, Table1.[Total Run], Table1.[Std lbs/hr], Table1.[Actual Lbs/hr], Table1.[RD / QA Samples], Table1.[Scrap Code 1], Table1.[Scrap Lbs 1], Table1.[Scrap Code 2], Table1.[Scrap Lbs 2], Table1.[Scrap Code 3], Table1.[Scrap Lbs 3], Table1.Comments, Table1.[Total Scrap], Table1.[Scrap %]
FROM Main, Table1;

Their are currently 10711 rows of data that i need to append. If I import it directly via excel I get Type Conversion Failure errors due to blank entries. Unfortunately they let injured production workers perform data entry thus null values and blanks - improper data galore. What I'm wondering is if I can logically do this in segments perhaps using the Primary Key. One thing I did do is Import the data and made sure the primary key autonumber count was the same as the number of rows in excel and I added the primary key field into the excel file to allow the query to work properly. This is the only idea I have come up with to allow this to work but this would mean multiple linked excel files and multiple query adaptations. Anyone care to take a crack at her?

EDIT: new idea is their a way to reference the primary key in an sql statement like so...

WHERE [Primary Key] >0 <500 ??? then i would just have to change the values each time i append the table!? /le confusions

 

[GinaWhipp]

Ummm, Type Conversion Failure is usually not due to NULL values but trying to import TEXT into a NUMERIC field. How about setting up a tblTemp with all the fields as TEXT and see if the import works. If it does then I would write a routine to bring it into the tblTemp and fix the issues prior to importing to the Live table.

 

[macri]

WHERE ((([Table1]![ID])>0 And ([Table1]![ID])>500));

added this where clause i'm guessing something is broken with it because all the primary keys get turned into a 1 after it runs

 

[macri]

I was under the impression that ms access analyzes the first row of data (not titles) but the first row of data and changes the data type accordingly which i've tried doctoring with that microsoft work around on their support page where you make a fake row of data types for access to create the appropriate datatype for each field. which still gave me the same errors. I'm thinking I may have to just link the old data via excel and start them on a fresh database where the update form won't allow blank entries thus saving the data from the high school dropouts that work here!? unless of course someone can get this WHERE clause to work out alright.

 

[GinaWhipp]

Maybe this will help expalin it better...

http://support.microsoft.com/kb/109376

My point was that that error has nothing to do with NULL values. Have you tried to set up the table as I suggested and see what happens?

I'm not sure what that WHERE clause is suppose to do as >0 and >500 makes no sense. Did you mean >0 and <500? Well you would need to use the BETWEEN operator as well.

 

[macri]

yeah it won't reformat dates properly they come out as just 401xx numbers for some reason and i still get the same errors. also i caught that error with the <>'s but it's still not working, could you show me an example of the between operator or link me to some decent documentation on it? the syntax is quite a bit different from mysql which is what i'm learning in school. picked up a dba position for the summer regretting it, corporate is down in talahasse florida i think so they all have bill gates' finger well you know where...

 

[GinaWhipp]

Okay, well I'll just IGNORE that last line. Information on he Between() operator...

http://office.microsoft.com/en-us/access-help/between-and-operator-HP001032212.aspx

You mean you get the same errors formatting all the fields as TEXT and appending it to the table?

 

[macri]

i've been noobing around with this thing over the weekend here and there, i have everything else in my db finished out nicely all reports and what not this is really the last step and to be honest i'm milking it out until august 8th when my contract is up lol but i'm going to say i have a bracket or an operator in the wrong spot and i was wondering if anyone would be so kind as to unscramble my statement and show me good syntax pleaaaas <3?

INSERT INTO Main ( ID, [Mth/Day], [DS/NS], Shift, Line, [Product Code], [Product Description], [Mat'l], Pcs, Quantity, [Total Run], [Std lbs/hr], [Actual Lbs/hr], [RD / QA Samples], [Scrap Code 1], [Scrap Lbs 1], [Scrap Code 2], [Scrap Lbs 2], [Scrap Code 3], [Scrap Lbs 3], Comments, [Total Scrap], [Scrap %] )
SELECT IIf(Table1.ID Between 1 And 500) AS Expr1, Table1.[Mth/day], Table1.[DS/NS], Table1.Shift, Table1.Line, Table1.[Product Code], Table1.[Product Description], Table1.[Mat'l], Table1.Pcs, Table1.Quantity, Table1.[Total Run], Table1.[Std lbs/hr], Table1.[Actual Lbs/hr], Table1.[RD / QA Samples], Table1.[Scrap Code 1], Table1.[Scrap Lbs 1], Table1.[Scrap Code 2], Table1.[Scrap Lbs 2], Table1.[Scrap Code 3], Table1.[Scrap Lbs 3], Table1.Comments, Table1.[Total Scrap], Table1.[Scrap %]
FROM Main, Table1;

 

[GinaWhipp]

Try...

INSERT INTO Main ( ID, [Mth/Day], [DS/NS], Shift, Line, [Product Code], [Product Description], [Mat'l], Pcs, Quantity, [Total Run], [Std lbs/hr], [Actual Lbs/hr], [RD / QA Samples], [Scrap Code 1], [Scrap Lbs 1], [Scrap Code 2], [Scrap Lbs 2], [Scrap Code 3], [Scrap Lbs 3], Comments, [Total Scrap], [Scrap %] )
SELECT Table1.[Mth/day], Table1.[DS/NS], Table1.Shift, Table1.Line, Table1.[Product Code], Table1.[Product Description], Table1.[Mat'l], Table1.Pcs, Table1.Quantity, Table1.[Total Run], Table1.[Std lbs/hr], Table1.[Actual Lbs/hr], Table1.[RD / QA Samples], Table1.[Scrap Code 1], Table1.[Scrap Lbs 1], Table1.[Scrap Code 2], Table1.[Scrap Lbs 2], Table1.[Scrap Code 3], Table1.[Scrap Lbs 3], Table1.Comments, Table1.[Total Scrap], Table1.[Scrap %]
FROM Table1
HAVING (((Table1.ID) Between 1 And 500));

 

[macri]

Gina,

IT WORKED, Thanks so much for all of your help you've been wonderful if you have any insight for a young up and coming DBA/Web Dev/Server Admin I'd love to hear anything you would be willing to share! Also if you're this clever with sql and the likes what made you stay loyal to mr. gates and his collection of faulty products that run on his poorly put together operating system? I'm no elitest but i knew right away the moment i compiled my first slackware build at 15 that windows is going to die one day.

 

[GinaWhipp]

Another issue bits the dust!

Make no mistake my loyalty is to myself BUT without Mr. Gates and his desire to develop applications to make our life easier were would we be today? As for the products? I can't imagine writing THAT much code and not having bugs. I have written some pretty complicated databases that have taken MONTHS to finish and then tested and they STILL had bugs. So, I'll just give them the same benefit of doubt my Clients give me when a bug presents itself.

The only thing I have to pass on is... HAPPY CODING! AND do not criticize the soap because the world is dirty!

 

[macri]

Another issue bits the dust!

Make no mistake my loyalty is to myself BUT without Mr. Gates and his desire to develop applications to make our life easier were would we be today? As for the products? I can't imagine writing THAT much code and not having bugs. I have written some pretty complicated databases that have taken MONTHS to finish and then tested and they STILL had bugs. So, I'll just give them the same benefit of doubt my Clients give me when a bug presents itself.

The only thing I have to pass on is... HAPPY CODING! AND do not criticize the soap because the world is dirty!

i probably wouldn't be so critical if i didn't have to pay for the soap, open source developed free soap cleans dirt better in my experience. i mean you have the worlds dirtiest people coming together to unite and make a soap that can clean everything and can be further customized depending on your own personal soap knowledge. imagine a soap that you made that cleans only the spots that are dirty and can be shared with other dirty people facing the same dirt issues as yourself they could then add onto your already great soap recipe and your soap could become something amazing. with 6 billion dirty dirty people who knows what we could accomplish!

 

[GinaWhipp]

Hmmm, guess I don't mind paying for the soap when I can get all kinds of free suggestions and samples and code to make my soap better than anybody elses!

 

[macri]

LOLOL gina i could talk in soap based metaphores about software all day with you but i'm pretty sure i'd get banned from these forums, thanks for the humor and all the help.


CHEERS

 

[GinaWhipp]

No problem!