SELECT statement syntax confusion

[noe1818 via AccessMonster.com]

I'm lost.

I would like an unbound text box to pull up a value from another field.
so,
SELECT [28d d/w]
FROM [Fasting]
WHERE [Client ID] (in the fasting table) = [Client ID] (from this table) AND
[A.S #] (fasting table) = [A.S.#] (from this table) AND [asDate] (fasting
table) = [asDate] (current table)

Client ID is text
A.S.# is number
asDate is date

What is the proper way to write this so I get my desired results? I don't
know the syntax for SELECT statements. I want to use it as the record source
for the unbound box
Any help would be appreciated.

 

[Conan Kelly]

noe1818,

Why not create this query in query design view? After you create it, switch
to SQL view and Access will display the SQL Syntax for you.

There are a few issues with your syntax:

1. What are the names of your 2 tables? In the FROM clause I only see
"[Fasting]" but in the WHERE clause you describe "(fasting table)",
"(current table)", and "(this table)". What is the name of "current/this"
table?

2. Everything in your WHERE clause should be part of your FROM clause.
When joining 2 tables, your from clause will be something like this: FROM
[Table1] INNER JOIN/LEFT JOIN/RIGHT JOIN [Table2] ON [Table1].[Field1] =
[Table2].[Field1] AND [Table1].[Field2] = [Table2].[Field2]. (*** NOTICE:
I listed the 3 different joins (left, right, & inner)...only select one of
them).

3. Specify which table each of the fields are from:
[TableName].[FieldName]

Here is an example:

SELECT [Table2].[Month], [Table2].[Company], [Table2].[Curr Month BV],
[Table2].[Amount Due], [Table2].[ST Bal], [Table2].[Correcting JE]
FROM [Table2] LEFT JOIN [Table1] ON ([Table2].[Curr Month BV] =
[Table1].[Weight]) AND ([Table2].[Company] = [Table1].[LoadID]);


HTH,

Conan

 

[Michel Walsh]

In a new query, bring the two tables.

Drag the ClientID field from one table over the ClientID field of the other
table, in the graphical portion.

Do the same for A.S# field.

Do the same for the asDate field.


Drag the required fields in the grid.

That should be it.



Hoping it may help,
Vanderghast, Access MVP