Ms-access – Ms Access SQL problem

ms access

enter image description here
in this table Customer is actually storing CUSID (i have pleased the caption customer and made it display the name to which the CUSID corresponds to)
now in the CUSnum field i want it to automatically pic the number CUSID of which was entered into the previous field (if he customer doesnt exist it should all to add the number of unregistered customer)

i came up with the following SQL
SELECT [Customer].[CUSContact] FROM Customer WHERE [Sale].[CUSID] = [Customer].[CUSID];

but for some reason it is not automatically catching the CUSID from the sale table bu asking for it to be manually inputted ! please help !

Best Answer

You need to make SQL join Sale to Customer. There are two ways to approach this:

APPROACH #1

In SQL mode, add the Sale table to the FROM clause

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSID];

APPROACH #2

Open the QBE (Query-By-Example) Grid and add the Sale table. The graphical representation of the query should draw a line from the CUSID in the Customer table to the CUSID in the Sale table.

Give it a Try !!!

UPDATE 2014-03-10 14:43 EDT

I just looked at the image again. Perhaps the query should be this instead:

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSnum];
Related Question