problem with subquery

[dharkangel]

Hi,

I've done this in the past but today, I can't get it to work. I'm trying to do an INSERT that grabs data from another table through a subquery SELECT statement. I am receiving a 'error in INSERT Statement' error:

'populate UsersTable
strSQL = "INSERT INTO UsersTable ([myName], [myEmail]) " & _
"(SELECT getAllUsers.[myName], getAllUsers.[myEmail] FROM getAllUsers) AS D " & _
"GROUP BY [myName];"
DoCmd.RunSQL strSQL

thank you,

 

[lespaul]

try:

strSQL = "INSERT INTO UsersTable ([myName], [myEmail]) " & _
"VALUES (SELECT getAllUsers.[myName], getAllUsers.[myEmail] FROM getAllUsers);"

since you're not using an aggregate function you don't need the GROUP BY and you were missing the VALUES.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dharkangel]

Thanks for responding, but I am still getting an error but a different one:

Syntax Error. In query expression 'SELECT getAllUsers.[myName]'

 

[lespaul]

so if you run:

SELECT [myName], [myEmail] FROM getAllUsers

does that work?

Are these the only two fields in UsersTable?

 

[gmmastros]

Try removing some of those parenthesis:

strSQL = "INSERT INTO UsersTable ([myName], [myEmail]) " & _
"[!]([/!]SELECT getAllUsers.[myName], getAllUsers.[myEmail] FROM getAllUsers[!])[/!]"

So that it becomes:

strSQL = "INSERT INTO UsersTable ([myName], [myEmail]) " & _
"SELECT getAllUsers.[myName], getAllUsers.[myEmail] FROM getAllUsers"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dharkangel]

Removing the parentheses worked! It doesn't make sense to me but at least its working. Thanks to both of you.

 

[Golom]

Your parens are in the wrong place and you need an aggregate function if you are going to GROUP BY myName.

strSQL = "INSERT INTO UsersTable ([myName], [myEmail]) " & _
         "SELECT [myName], MAX([myEmail]) As [EMail] " & _
         "FROM getAllUsers " & _
         "GROUP BY [myName];"

 

[gmmastros]

There are (generally) 2 methods for inserting records.

Insert Into Table(Field1, Field2) Values(1, 'A')

or

Insert Into Table(Field1, Field2)
Select Field1, Field2
From SomeOtherTable

You can use parenthesis for subqueries, like...

Insert Into Table(Id, Name)
Select [!]([/!][blue]Select Max(Id) + 1 From Table[/blue][!])[/!], 'George'

Does it make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lespaul]

Must be an Access quirk, I use parens in my INSERT queries all the time and it's not an issue.

 

[VisiSteve]

When I use a syntax identical to this in my database:

Insert Into Table(Id, Name)
Select (Select Max(Id) + 1 From Table), 'George'

I receive the following error:

Reserved error (-3025); there is no message for this error.

I've tried several combinations of parenthesis, using SELECT or VALUES as the keyword for the source of the values, etc. and I'm not able to resolve the issue.

Any ideas? I've searched the web for references to this error number without success.

My goal is to add a new record to a many-to-many relationship table after adding a record to the primary-object table. I've verified that the values I'm adding to the MTM table actually exist in the other tables, and in fact I've tested the above syntax with a brand-new table with no relationships defined at all, just to make sure I wasn't running into referential-integrity issues.

Thanks in advance...Steve

 

[lespaul]

Insert Into Table(Id, Name)
Select Max(Id) + 1, 'George' FROM Table

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[gmmastros]

I showed that syntax as an example. I know it works with SQL Server and just assumed it would also work with Access since it seems to be relatively straight forward (at least I thought so).

So... it seems that this syntax is no good. Sorry. You should be able to use something like this....

Insert Into Table(Id, Name)
Select Nz(Max(Id)) + 1, 'George'
From Table



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VisiSteve]

(Sorry if I'm hijacking the thread -- I think this is along the same lines though)

Thanks George, the above syntax does seem to work against a single table however I'm still running into issues in my "real" scenario:

I have 3 tables, Equipment, Measurement, and Equipment_Measurement. Equipment has fields ID, Name. Measurement has fields ID, Name. Equipment_Measurement has fields EquipmentID, MeasurementID.

Measurement has a bunch of records in it. Let's say for example sake it has a record ID = 5, Name="Voltage".

I need to add a new Equipment record and tie it to the Voltage record in the Measurement table.

I do a query "INSERT INTO Equipment DEFAULT VALUES". This adds the new Equipment record with default values (the ID field is AutoNumber, so it gets set appropriately).

I then need to add a record to the Equipment_Measurement table. This is what I've been trying:

INSERT INTO Equipment_Measurement (EquipmentID, MeasurementID)
VALUES (
(SELECT Max(ID) FROM Equipment),
(SELECT ID From Measurement WHERE Name="Voltage")
)

This results in the -3025 error I referred to earlier. The SQL seems correct to me but I must be missing something.

Thanks much...Steve

 

[PHV]

You may try something like this:
INSERT INTO Equipment_Measurement (EquipmentID, MeasurementID)
SELECT DMax('ID', 'Equipment'), ID FROM Measurement WHERE Name='Voltage'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

maybe:

INSERT INTO Equipment_Measurement (EquipmentID, MeasurementID)
(SELECT Max(Equipment.ID), (SELECT Max(Measurement.ID) FROM Measurement WHERE Measurement.[Name]="Voltage") FROM Equipment))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Leslie, I think you thought this ?
INSERT INTO Equipment_Measurement (EquipmentID, MeasurementID)
SELECT (SELECT Max(Equipment.ID) FROM Equipment), Measurement.ID FROM Measurement WHERE Measurement.[Name]="Voltage"
Or this ?
INSERT INTO Equipment_Measurement (EquipmentID, MeasurementID)
SELECT Max(Equipment.ID), (SELECT Measurement.ID FROM Measurement WHERE Measurement.[Name]="Voltage") FROM Equipment

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VisiSteve]

Thanks so much folks, the version the PHV supplied did the trick...it works like a champ now.

Note that I'm using @@Identity instead of the DMax() function to retrieve the newly-added record -- that seems to be working fine, but I think either would work in my situation.