Hello! I've made some simple queries, but this query I need to do seems quite complicated and I'm unable to figure it out. Here's what I have:

I have a table, tblTopicTitle, that has a field: TopicTitle, that has as of now 5 records with titles in them. What I want is a query that brings up a separate record for each field in the query. In other words:

Record 1 of tblTopicTitle has the title "Introduction", and I would like the first field in the query to have that title. The second record of the tblTopicTitle table has the title "Instructional Branching", and I would like that title to be in the second field of the query. Also, I need the query field to have custom field names instead of "Exprs1000" that seems to be the default field name.

I know your first thought is "why are you doing it that way?" Because this information has to parsed this way so it can be read in Authorware, so I need a way to make this query with multiple fields from a table with multiple records. Thanks in advance to anyone who can point me in the right direction!

Carlos

Not really sure but maybe a CrossTab query is what your looking for. Good luck.

Here's a link that can help you with that:

web page

I looked at the link, but quite frankly, I don't know what to do with the information given on that site! However, I found a work-around that might make things easier. On the form, I have this code attached to the Save command button I put on the form:

Dim strSQL As String

strSQL = "UPDATE TopicTitle_old SET TopicTitle1 = '(SELECT TopicTitle FROM tblTopicTitle WHERE stg = 1)' "

DoCmd.RunSQL strSQL

So basically, I want to update the data in the table TopicTitle_old with the data from the tblTopicTitle table that the form uses. Eventually, I want to extrapolate this so each field (up to 18 of them) in the TopicTitle_old will be updated with up 18 records in the tblTopicTitle table.

When I run the above code, the SQL statement in the parenthesis is plastered in the TopicTitle1 field of the TopicTitle_old table. This is kind of what I want, except I don't want the literal sql statement in the TopicTitle1 field, I want what is IN (or added, or deleted, or changed) the TopicTitle field in the tblTopicTitle table. How can I make the "SET TopicTitle1 = ..." statement read the information in the second sql statement in the parenthesis and put that data in the appropriate field rather than putting the sql statement itself in the field. If I take out the apostrophies, I get an "Operation must use an updateable query" pop-up message. I don't know what that means, precisely; or at least I can't use that message to figure out how to fix it.

Carlos

I don't think you can do it the way you are doing it. You can though, break it up into two steps.

Dim strSQL As String
Dim strTitle As String

strTitle = DLookup("TopicTitle", "tblTopicTitle", "stg = 1")
strSQL = "UPDATE TopicTitle_old SET TopicTitle1 = " & strTitle
DoCmd.RunSQL strSQL

Oh, so close! Ok, it works, but what happens is when I press the Save command button, an "Enter Parameter Value" pop-up appears and asks for a parameter value. It will show the value of the TopicTitle WHERE stg = 1, but I have to enter the same value in the pop-up in order for it to appear in the TopicTitle_old table.

Is there a way to side step the pop-up and just make the change(s) in the TopicTitle_old table automatically?

Carlos

I made a boo-boo I think. Try this:

strSQL = "UPDATE TopicTitle_old SET TopicTitle1 = '" & strTitle & "'"

I have surrounded strTitle with a single quote inside double quotes.

Yes!! You have saved me uncountable hours of banging my head against a wall trying to figure this out, thank you so much!!

Carlos

You're welcome, Carlos. I am glad I could finally help.