Help with nested query

[kosmokramer]

INSERT INTO orderTable (transaction, movieTitle, movieQuantity )
VALUES (' & (Select Max(transaction) FROM transactionTable) & ', 'Braveheart', 3);

I am not really sure how you are supposed to nest queries like this, so I am just guessing. The transaction table just holds the transaction numbers and the userName. There is a one to many relationship between the orderTable and the transaction table (one transaction number, but many orders....er...items would be better probably). The number transaction is an autonumber variable

 

[Quehay]

Syntax is a bit different when you've got a SELECT as the source:

[tt]
INSERT INTO orderTable (transaction, movieTitle, movieQuantity )
SELECT Max(transaction),'Braveheart', 3
FROM transactionTable;

[/tt]

If transaction is numeric you don't need to delimit with '

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[kosmokramer]

Thanks! That seems kind of odd that you would put the From part after the rest of the listing for the values to insert.

Oh...one more thing. When you are in Access, you know how there is the menu that pops up at the very beginning where you can choose to create a table in design view, create a table using a wizard, etc? Alright, on the left hand side there is a place for queries. I have just used it so far to test my queries before putting them on webpages. Can you use this in the same way as in MS SQL Server to create stored procedures?

 

[kosmokramer]

Humm...thought I had it, but it is not working now. Well, it is probably something with the session variables.

for loopCounter = 2 to Session("movieCount")+1

strSQL3 = "INSERT INTO orderTable ([transaction], movieTitle, movieQuantity) SELECT Max(transaction),'"_
		& Session.Contents(loopCounter) & "'," & Session.Contents(loopCounter+Session.Contents("movieCount")) & " FROM transactionTable;"

next

Alright, what I am doing here is saving the movie titles and movie quantities as well as the total number of movies in session variables until the person is ready to check out. The session variables all print out the correct results when I just Response.Write them, so I know they have the correct values, but I'm getting a syntax error in the Insert Into statment

 

[Quehay]

TRY: Debug.print your strSQL variable when it gets a complete statement and stop code then try running the SQL (use clipboard to lift & drop) as a new QRY in the Access query GUI. It's easier to check syntax this way.

Only text/VARCHAR fields need to be wrapped in single quotes. *CHR(39) is cleaner to me or use a constant, e.g. "const SINGLE_QUOTE as string = "'"

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[kosmokramer]

Got it working!! All I had done wrong was to not include the database connections in the loop, so I was using a single connection to try to make several inserts.

By the way, thanks for the advice on the single quotes, but all of the single quotes were done correctly. The variable Session.Contents(loopCounter) is actually the name of the movie, not a number. loopCounter just serves as an index on the Session.Contents array, and at that index, it contains the name of a movie.