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 Vinson]

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.

There is no field named Province in either table, so it assumes you're
asking for a parameter.

Replace [name] with [firstName] and [province] with [provinceName] to
see the firstName field from Contacts and the provinceName field from
provinces. You are talking to a very fast but very stupid computer -
not to some entity with intelligence to figure out what *you* mean by
the term "province"!

John W. Vinson[MVP]