trying to understand a basic query procedure

[Eric Lin]

I'm getting started with SQL and MS Access, and I'm using an example and
very simple database made of 2 tables.

table contacts:
with these columns: idContact, firstName, lastName, idProvince

table provinces:
with these columns: idProvince, provinceName
l

When I use this SELECT statement:
SELECT * FROM provinces INNER JOIN contacts ON
provinces.idProvince=contacts.idProvince;

I get the expected result, a table formed by all the columns in the 2 tables
and the rows in wich the value of idProvince is the same in the two tables.

But if I don't want all the columns from the two original tables in my
resulting table, I would modify the query, replacing the wildcard (*) by
e.g. "name, province", like this one:

SELECT name, province FROM provinces INNER JOIN contacts ON
provinces.idProvince=contacts.idProvince;

But I don't get the result I was expecting. When I execute the query, the
DBMS (MS Access) ask for a value for province. I wonder why it doesn't just
culminate in a table like the resulting table in the previous SELECT
statement, but with fewer columns.

I would appreciate if someone could give me an explanation or suggest me
some link or site in which I could learn more about my errors.

Thanks
Eric

 

[John Spencer]

IF you want all the fields in Provinces then the query is

SELECT name, provinces.*
FROM provinces INNER JOIN contacts
ON provinces.idProvince=contacts.idProvince

The SELECT clause must have fieldnames in it. Province is not a field name
nor is it a table name.
The asterisk (*) means ALL fields. If you want to limit that to a table,
then you must also specify the tablename. And if two fields have the same
name in different tables, you must specify the tablename. So if you had

SELECT idProvince, provinceName, idContact
FROM provinces INNER JOIN contacts ON
provinces.idProvince=contacts.idProvince

Access would complain, since it would not know which idProvince to return -
the one in contacts table or the one in the Provinces table

 

[Guest]

Ok, thank you very much for your early support.
As you notice, I was using an incorrect field name. I correct it and it
works fine.
Thanks a lot again