Create a column based on a query

[Russellh]

I have 2 tables that I cannot link.
I need to take the server order # (153001 example) and convert it to 050000000000153001 which I can do.
Then I can use that number to pull the note that I need from the other table that I cannot link.

Query 1 I am pulling Service Order info from SOTABLE
in Query 1 I need to add a field that will show the 2nd note from NotesDetail table.
The fields for the NotesDetail table are NoteKey, Date, NoteItem.
In order to get the note I am after I would have to query the NotesDetail table for the following:
NoteKey = 050000000000153001
NoteItem = 000102

What is the syntax for creating a field in query 1 that will show that information?

Note: [query notesdetail where notekey="050000000000153001" and noteitem="000102"]

If it can't be done in a way that I can enter into a blank field (from design view) as I started in the previous line, how would I do it?

[June7]

Why don't you join the tables in query? You can use the field constructed with the conversion expression as one side of the JOIN clause.

Then apply appropriate filter criteria.

[Russellh]

I am not allowed to modify any of the tables. So I can't add the new field to it. I'm only able to create querries on the tables. I can create my own tables but then the copy wouldn't update itself live.

[June7]

I am not suggesting you modify tables.

I am suggesting joining tables in query. One side of the join will be calculated field and the other side will be natural field. Something like:

SELECT table1.*, table2.* FROM table1 INNER JOIN table1 ON "050000000000" & table1.fieldname AS SONUM = table2.ServiceNumber;

Won't be able to see that join in query designer view. Build it in SQL view.

Alternative is to create a query object with the calculated field, no joins.

Then use that query object in another query to join with table.

[Russellh]

Hey June,
I am too much of a novice to follow you I think. Here is a screenshot of what I am trying to do.

The last column on the right at the bottom recreates the CANOTEKEY field in the unlinked table.
I need to create another field that shows the CATEXT field with conditions.
The conditions are 1) CANOTEKEY has to be the same for both
2) CANOTEITEM has to equal 000102

If that explains it any better. I tried playing with joins. Since I am creating the CANOTEKEY field in table1 by adding text strings together I cannot find a way to use that to create a join to the canotekey in the unjoined table. For every canotekey there are several lines of notes. Each line is noted by 000101 (first line) and 000102 (2nd line) and so on. Basically I need the 2nd line. In other words in the unjoined table there are several records for the same canotekey and so I need the catext field where canotekey = canotekey (from my created field) and canoteitem = 000102.

I'm sure I am probably over complicating this but I can't seem to make it work.

[June7]

Queries can be used like tables in queries.

Using my second suggestion, remove the unlinked table from that query and save the query.

Now build another query that joins the first query to the 'unlinked' table.

NOTE: the & is preferred concatenation operator.

[Russellh]

Super thanks. Worked like a charm! I just needed read what you were telling me in a way I could grasp.
MANY THANKS again!!