How do I make this work in a Access field that is in the table [Job - Scan]?

SELECT * INTO [TEMP] 
FROM [Job - Scan];

SELECT [Job - Streams].[Stream Description]
FROM [Job - Streams] INNER JOIN [TEMP]
ON [TEMP].[Docket Number]=[Job - Streams].[Docket Number]
WHERE [TEMP].[Docket Number]=[Job - Streams].[Docket Number];

DROP TABLE [TEMP];

---

Request for Question Clarification by willie-ga on 23 May 2003 04:53 PDT

I'm a bit unsure as to what you're trying to do

Have you tried

INSERT INTO [TEMP]
    SELECT * FROM [Job - Scan]

as your first statement....that could be where the problem is....you
haven;t put anything into TEMP

willie-ga

---

Request for Question Clarification by willie-ga on 23 May 2003 05:03 PDT

Please ignore that last remark. Sorry, I'm not used to the "Select
into" usage.
I'm not going to try to answer the question, but have you tried it
without the
"WHERE [TEMP].[Docket Number]=[Job - Streams].[Docket Number]; "
line....that looks redundant to me.

willie-ga

---

Clarification of Question by amskid-ga on 23 May 2003 05:15 PDT

"WHERE [TEMP].[Docket Number]=[Job - Streams].[Docket Number]; "

The problem is that I cannot use data from column 1 (Docket Number) as
part of my query in column 2 on the same record. I first try to make a
copy of the current table I am on so that this would work, but the
same type of error occurs. I cannot do anything if I am currently in
that table/record.

---

Request for Question Clarification by thinkout-ga on 23 May 2003 09:48 PDT

Greetings amskid-ga,

If you will give me a detailed description of what you are trying to
do, I can post the SQL statement that will allow you to do it and
answer your guestion.  Give me as much detail as you can.  Give me the
detail in english rather than SQL.  Tell me how many tables are
involved under what conditions you want to insert or update.  I can
use your SQL to come up with these answers, but if I have an english
description also, it will help smooth out any communication issues.

Thanks!

Thinkout

---

Clarification of Question by amskid-ga on 23 May 2003 10:26 PDT

One table called "Job - Streams" that has two columns called "Docket
Number" and "Stream Description" and some other columns not of
importance. Another table called "Job - Scan" with the same two
columns within it. The function of "Job - Streams" is to identify all
types of work being processed under one "Docket Number". The function
of "Job - Scan" is to identify which "Stream Description" are to be
scanned using whatever settings are chosen using the remaining columns
(DPI, File type, colourspace, etc).

When I am entering an entry into the "Job - Scan" table, I first enter
the "Docket Number" and then the "Stream Description". I want the
"Stream Description" to be a list of choices based on the values in
"Job - Streams". For example :
Table "Job - Streams"
Docket Number  |  Stream Description
1234           |  Paper
1234           |  Film
3352           |  Photo
3352           |  Aperture Cards

If I pick "1234" under "Docket Number" in the "Job - Scan" table, I
want to have a query that results with "Paper" and "Film" for the
"Stream Description" column.

---

Request for Question Clarification by thinkout-ga on 23 May 2003 11:30 PDT

Greetings amskid-ga,

I have read your clarification three times to make sure that I am
understanding it properly and what it sounds like you are looking for
is a screen that allows you to enter data into the "job - scan" table.
 This is different that the initial SQL query that you were asking
about.  A few questions:

What version of Access are you using?

Are you indeed simply looking for a screen that:

1. Has a drop-down list of values to enter into the "job -
scan".stream - description field, so that when you enter a new value
into the table you  don't have to type in the same description again
and again?

2. Has a look up field so that if you type in the Docket Number it
automatically fills in the stream - description?

If this is really what you are looking for, it is a different solution
than a simple insert SQL statement and would require much more time to
mock up.  Is this what you are really looking for?

Thanks,

Thinkout