By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Insert Into

P: n/a
I'm fairly confident in my knowledge of SQL but I'm stumped with regards
with an Insert Into statement in Access 2003. I've got a 4 column table
that I've simplified about as much as I can, no primary key, no indexes(at
least I don't think I have any), allow duplicates, allow zero length fields,
the fields in the new table are of the same type of the fields in the old
table, etc. Maybe someone can give me an idea as to what I'm doing wrong.
MS Access I've noticed doesn't give me any error messages to tell me what
I'm doing wrong. The only way I can tell it's not working is by checking
the table and seeing it's still empty.
When I run the Select statement by itself, it's returns what I want but for
some reason, my table still remains empty. Any sugestions?

Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data
Units].Unit
FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data
Units].Owner
WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND (([Table
Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR ((([Data
Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004# Or
([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
(([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND
(([Data Units].Template)<>"PRPD"))
GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner, [Data
Units].Unit
ORDER BY [Data Units].Stype;
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
How are you running this query? By opening the query from database window?
From VBA code or macro? Post more details about your process.

--

Ken Snell
<MS ACCESS MVP>

"efgh" <ef**@neverland.com> wrote in message
news:so********************@nnrp1.uunet.ca...
I'm fairly confident in my knowledge of SQL but I'm stumped with regards
with an Insert Into statement in Access 2003. I've got a 4 column table
that I've simplified about as much as I can, no primary key, no indexes(at
least I don't think I have any), allow duplicates, allow zero length fields, the fields in the new table are of the same type of the fields in the old
table, etc. Maybe someone can give me an idea as to what I'm doing wrong.
MS Access I've noticed doesn't give me any error messages to tell me what
I'm doing wrong. The only way I can tell it's not working is by checking
the table and seeing it's still empty.
When I run the Select statement by itself, it's returns what I want but for some reason, my table still remains empty. Any sugestions?

Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data
Units].Unit
FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data
Units].Owner
WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND (([Table
Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR ((([Data Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004# Or
([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
(([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND
(([Data Units].Template)<>"PRPD"))
GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner, [Data Units].Unit
ORDER BY [Data Units].Stype;

Nov 13 '05 #2

P: n/a
I'm trying to run it from from the database window.

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:a6********************@comcast.com...
How are you running this query? By opening the query from database window?
From VBA code or macro? Post more details about your process.

--

Ken Snell
<MS ACCESS MVP>

"efgh" <ef**@neverland.com> wrote in message
news:so********************@nnrp1.uunet.ca...
I'm fairly confident in my knowledge of SQL but I'm stumped with regards
with an Insert Into statement in Access 2003. I've got a 4 column table
that I've simplified about as much as I can, no primary key, no
indexes(at
least I don't think I have any), allow duplicates, allow zero length

fields,
the fields in the new table are of the same type of the fields in the old
table, etc. Maybe someone can give me an idea as to what I'm doing
wrong.
MS Access I've noticed doesn't give me any error messages to tell me what
I'm doing wrong. The only way I can tell it's not working is by checking
the table and seeing it's still empty.
When I run the Select statement by itself, it's returns what I want but

for
some reason, my table still remains empty. Any sugestions?

Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data
Units].Unit
FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data
Units].Owner
WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND
(([Table
Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR

((([Data
Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004# Or
([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
(([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND
(([Data Units].Template)<>"PRPD"))
GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner,

[Data
Units].Unit
ORDER BY [Data Units].Stype;


Nov 13 '05 #3

P: n/a
I've tried inserting a row of values into the table, rather than using the
select statement and it worked, however, when I looked at the values in the
table, the column headers all got changed to EXPR1(Or something like that,
essentially, the column header got changed), EXPR2, etc..

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:a6********************@comcast.com...
How are you running this query? By opening the query from database window?
From VBA code or macro? Post more details about your process.

--

Ken Snell
<MS ACCESS MVP>

"efgh" <ef**@neverland.com> wrote in message
news:so********************@nnrp1.uunet.ca...
I'm fairly confident in my knowledge of SQL but I'm stumped with regards
with an Insert Into statement in Access 2003. I've got a 4 column table
that I've simplified about as much as I can, no primary key, no
indexes(at
least I don't think I have any), allow duplicates, allow zero length

fields,
the fields in the new table are of the same type of the fields in the old
table, etc. Maybe someone can give me an idea as to what I'm doing
wrong.
MS Access I've noticed doesn't give me any error messages to tell me what
I'm doing wrong. The only way I can tell it's not working is by checking
the table and seeing it's still empty.
When I run the Select statement by itself, it's returns what I want but

for
some reason, my table still remains empty. Any sugestions?

Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data
Units].Unit
FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data
Units].Owner
WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND
(([Table
Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR

((([Data
Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004# Or
([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
(([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND
(([Data Units].Template)<>"PRPD"))
GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner,

[Data
Units].Unit
ORDER BY [Data Units].Stype;


Nov 13 '05 #4

P: n/a
Based on what you've posted, I am not sure what may be wrong.

You say the SELECT portion of the query is working (leaving off the INSERT
INTO portion). With this info, I would assume that some type of integrity
(duplicate primary key, duplicate unique key values, etc.) is being violated
by your attempt to insert the new records.

Do you have SetWarnings turned off in your database, and thus ACCESS is not
telling you that there is an error?
--

Ken Snell
<MS ACCESS MVP>
"efgh" <ef**@neverland.com> wrote in message
news:Dl********************@nnrp1.uunet.ca...
I'm trying to run it from from the database window.

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:a6********************@comcast.com...
How are you running this query? By opening the query from database window? From VBA code or macro? Post more details about your process.

--

Ken Snell
<MS ACCESS MVP>

"efgh" <ef**@neverland.com> wrote in message
news:so********************@nnrp1.uunet.ca...
I'm fairly confident in my knowledge of SQL but I'm stumped with regards with an Insert Into statement in Access 2003. I've got a 4 column table that I've simplified about as much as I can, no primary key, no
indexes(at
least I don't think I have any), allow duplicates, allow zero length

fields,
the fields in the new table are of the same type of the fields in the old table, etc. Maybe someone can give me an idea as to what I'm doing
wrong.
MS Access I've noticed doesn't give me any error messages to tell me what I'm doing wrong. The only way I can tell it's not working is by checking the table and seeing it's still empty.
When I run the Select statement by itself, it's returns what I want but

for
some reason, my table still remains empty. Any sugestions?

Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data Units].Unit
FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data Units].Owner
WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND
(([Table
Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR

((([Data
Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004# Or
([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
(([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND (([Data Units].Template)<>"PRPD"))
GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner,

[Data
Units].Unit
ORDER BY [Data Units].Stype;



Nov 13 '05 #5

P: n/a
I figured out what I was doing wrong. I was right clicking on the title bar
of the SQL window and choosing Datasheet View. Since I was seeing the
results of the query, I thought it was working, only to find out it wasn't
when I looked at the table. I didn't realize that in order to actually run
the query, you have to close it or save it, then go to the Database Window
and "Open" it from there. Thanks for the help though.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:87********************@comcast.com...
Based on what you've posted, I am not sure what may be wrong.

You say the SELECT portion of the query is working (leaving off the INSERT
INTO portion). With this info, I would assume that some type of integrity
(duplicate primary key, duplicate unique key values, etc.) is being
violated
by your attempt to insert the new records.

Do you have SetWarnings turned off in your database, and thus ACCESS is
not
telling you that there is an error?
--

Ken Snell
<MS ACCESS MVP>
"efgh" <ef**@neverland.com> wrote in message
news:Dl********************@nnrp1.uunet.ca...
I'm trying to run it from from the database window.

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:a6********************@comcast.com...
> How are you running this query? By opening the query from database window? > From VBA code or macro? Post more details about your process.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "efgh" <ef**@neverland.com> wrote in message
> news:so********************@nnrp1.uunet.ca...
>> I'm fairly confident in my knowledge of SQL but I'm stumped with regards >> with an Insert Into statement in Access 2003. I've got a 4 column table >> that I've simplified about as much as I can, no primary key, no
>> indexes(at
>> least I don't think I have any), allow duplicates, allow zero length
> fields,
>> the fields in the new table are of the same type of the fields in the old >> table, etc. Maybe someone can give me an idea as to what I'm doing
>> wrong.
>> MS Access I've noticed doesn't give me any error messages to tell me what >> I'm doing wrong. The only way I can tell it's not working is by checking >> the table and seeing it's still empty.
>> When I run the Select statement by itself, it's returns what I want
>> but
> for
>> some reason, my table still remains empty. Any sugestions?
>>
>> Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
>> SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area, [Data >> Units].Unit
>> FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner = [Data >> Units].Owner
>> WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
>> Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND
>> (([Table
>> Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And ([Data
>> Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR
> ((([Data
>> Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004#
>> Or
>> ([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND
>> (([Data Units].Unit)>=8676780000 And ([Data Units].Unit)<=8676789999) AND >> (([Data Units].Template)<>"PRPD"))
>> GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner,
> [Data
>> Units].Unit
>> ORDER BY [Data Units].Stype;
>>
>>
>
>



Nov 13 '05 #6

P: n/a
You can run the action query from the design view...click on the red
exclamation point in the center of the toolbar.
--

Ken Snell
<MS ACCESS MVP>

"efgh" <ef**@neverland.com> wrote in message
news:Bc********************@nnrp1.uunet.ca...
I figured out what I was doing wrong. I was right clicking on the title bar of the SQL window and choosing Datasheet View. Since I was seeing the
results of the query, I thought it was working, only to find out it wasn't
when I looked at the table. I didn't realize that in order to actually run the query, you have to close it or save it, then go to the Database Window
and "Open" it from there. Thanks for the help though.
"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:87********************@comcast.com...
Based on what you've posted, I am not sure what may be wrong.

You say the SELECT portion of the query is working (leaving off the INSERT INTO portion). With this info, I would assume that some type of integrity (duplicate primary key, duplicate unique key values, etc.) is being
violated
by your attempt to insert the new records.

Do you have SetWarnings turned off in your database, and thus ACCESS is
not
telling you that there is an error?
--

Ken Snell
<MS ACCESS MVP>
"efgh" <ef**@neverland.com> wrote in message
news:Dl********************@nnrp1.uunet.ca...
I'm trying to run it from from the database window.

"Ken Snell" <kt***********@ncoomcastt.renaetl> wrote in message
news:a6********************@comcast.com...
> How are you running this query? By opening the query from database

window?
> From VBA code or macro? Post more details about your process.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "efgh" <ef**@neverland.com> wrote in message
> news:so********************@nnrp1.uunet.ca...
>> I'm fairly confident in my knowledge of SQL but I'm stumped with

regards
>> with an Insert Into statement in Access 2003. I've got a 4 column

table
>> that I've simplified about as much as I can, no primary key, no
>> indexes(at
>> least I don't think I have any), allow duplicates, allow zero length
> fields,
>> the fields in the new table are of the same type of the fields in the
old
>> table, etc. Maybe someone can give me an idea as to what I'm doing
>> wrong.
>> MS Access I've noticed doesn't give me any error messages to tell me

what
>> I'm doing wrong. The only way I can tell it's not working is by

checking
>> the table and seeing it's still empty.
>> When I run the Select statement by itself, it's returns what I want
>> but
> for
>> some reason, my table still remains empty. Any sugestions?
>>
>> Insert Into [Data Units TW Inuvik] (SType, Owner, Area, Unit)
>> SELECT [Data Units].Stype, [Data Units].Owner, [Table Owner].Area,

[Data
>> Units].Unit
>> FROM [Table Owner] INNER JOIN [Data Units] ON [Table Owner].Owner =

[Data
>> Units].Owner
>> WHERE ((([Data Units].InDate)<=#8/31/2004#) AND (([Data
>> Units].TmDate)>=#8/31/2004# Or ([Data Units].TmDate) Is Null) AND
>> (([Table
>> Owner].Area)="Inuvik") AND (([Data Units].Unit)>=8677770000 And
([Data >> Units].Unit)<=8677779999) AND (([Data Units].Template)<>"PRPD")) OR
> ((([Data
>> Units].InDate)<=#8/31/2004#) AND (([Data Units].TmDate)>=#8/31/2004#
>> Or
>> ([Data Units].TmDate) Is Null) AND (([Table Owner].Area)="Inuvik") AND >> (([Data Units].Unit)>=8676780000 And ([Data

Units].Unit)<=8676789999) AND
>> (([Data Units].Template)<>"PRPD"))
>> GROUP BY [Data Units].Stype, [Table Owner].Area, [Data Units].Owner,
> [Data
>> Units].Unit
>> ORDER BY [Data Units].Stype;
>>
>>
>
>



Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.