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

 

[Guest]

Hi Eric,

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

You don't have a field named "name", which is a good thing, since name is
considered a reserved word in Access. Try this instead:

SELECT firstName & " " & lastName AS CustName, province
FROM provinces
INNER JOIN contacts
ON provinces.idProvince=contacts.idProvince;

or

SELECT lastName & ", " & firstName AS CustName, province
FROM provinces
INNER JOIN contacts
ON provinces.idProvince=contacts.idProvince;


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

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

 

[Guest]

As John Vinson pointed out in his reply to the same question you multiposted
to the New Users group, there is no field named province. I didn't catch that
the first time.

Really, it's not necessary to multipost such simple questions. Post to one
group and then be patient for an answer. If you don't receive an answer
within one or two days, then it's considered okay to repost, however, you
should indicate that it is a repost.

Corrections shown below to the SQL statements I originally indicated.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi Eric,

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

You don't have a field named "name", which is a good thing, since name is
considered a reserved word in Access. Try this instead:

SELECT firstName & " " & lastName AS CustName, provinceName
FROM provinces
INNER JOIN contacts
ON provinces.idProvince=contacts.idProvince;

or

SELECT lastName & ", " & firstName AS CustName, provinceName
FROM provinces
INNER JOIN contacts
ON provinces.idProvince=contacts.idProvince;


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

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