query error

[compsum]

I have two tables in Access 2007 which have a 1 to 1 relationship. When I
try to run a simple query I get the following error message:
"The wizard is unable to open your query in Datasheet view, possibly because
another user has a source table open in exclusive mode. Your query will be
opened in design view".
Also, if I click the plus sign next to a record in a table I get the
following error:
"Type mismatch in expression".

 

[compsum]

I saved the database in Acess 2003 format... same problem when running Access
2003 in Vista. I have a dual boot system with XP... ran the database in
Access 2003... same error. When I clicked on the Query to open it I got a
'Type Mismatch in expression error'.

 

[John W. Vinson]

I saved the database in Acess 2003 format... same problem when running Access
2003 in Vista. I have a dual boot system with XP... ran the database in
Access 2003... same error. When I clicked on the Query to open it I got a
'Type Mismatch in expression error'.

Then there's a problem with the query.

Care to post the SQL so someone might have a chance to see what the problem
might be? Indicate the datatype of each field in the JOIN clause (hint: they
should match, the error indicates that they don't).

John W. Vinson [MVP]

 

[compsum]

Thanks John... this is my first use of Access.. Two tables one is called
Clients and has 13 fields... the first is ClientID (autonumber -primary) and
the rest are text (first, last, spouse, company,
Emailaddress,homephone,address, address2,city,state,zip,cellphone
The other table is called compay and has 17 fields... they are last,company
(primary),cophone,cofax,coaddress1,coaddress2,city,state,zip,host,hostaccount,hostpassword,secret,comments
(all are text),dateposted(date/time),nopages(number),sitecost(currency) The
relationship is between ClientID in Client and customer in company.
Also, when I click on the plus sign in front of any record in either table I
get an error message (An expression is typed incorrectly or is too complex to
be evaluated....)

 

[Douglas J. Steele]

As John suggested, posting the SQL of your query that's raising the error
will help us help you.

If you're not familiar with SQL, open the query in Design view, then select
View | SQL View from the menu. You'll see the actual SQL that Access has
generated for your query. Copy that, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[compsum]

sorry typo... the other table is called company... fields are last, company
(primary)....

 

[compsum]

sorry... typo The other table is called 'company' and has 17 fields... the
relationship is to 'company' in the company table.

 

[compsum]

Thanks... the SQL is: SELECT Clients.First, Clients.Last, Company.Company,
Company.DatePosted, Company.SiteCost
FROM Clients INNER JOIN Company ON Clients.[ClientID] = Company.[Company];

This is one that worked: SELECT Clients.Last, Clients.Company
FROM Clients;

Also, something strange happened that eventually cleared itself. When I
went into the Company table the 'last' and 'company' names had changed to the
clientID from the Client table. After shutting down Access, the names
reappeared??

 

[compsum]

Douglas, I notice that the Query which didn't work is listed under 'Unrelated
Objects' in the column on the left.

 

[John Spencer]

Part of the problem is that ClientID is a NUMBER field and you are trying to
use that in a join to Company (which you say is a TEXT field). The two
field types must be the same to "play well together".

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[compsum]

ok... I'm gaining on it... I established a new relationship between 'company'
in 'company' and 'last' in 'client'. The SQL is SELECT Clients.First,
Clients.Last, Company.SiteCost, Company.Company
FROM Company INNER JOIN Clients ON Company.[Company] = Clients.[Last];
It takes me to datasheet view with the field names shown... but no data?
You have been very helpful!

 

[John W. Vinson]

ok... I'm gaining on it... I established a new relationship between 'company'
in 'company' and 'last' in 'client'. The SQL is SELECT Clients.First,
Clients.Last, Company.SiteCost, Company.Company
FROM Company INNER JOIN Clients ON Company.[Company] = Clients.[Last];
It takes me to datasheet view with the field names shown... but no data?
You have been very helpful!

Just because Jane Winchester works for Acme Corp. doesn't mean that you can
link "Acme" to "Winchester" and find that relationship!

You're missing the point about how joins work. Two tables are joined by
linking the Primary Key value (which might be of any datatype, but is often a
number) of the "one" side table to a Foreign Key value of the same datatype
AND THE SAME VALUE, in the Many side table.

If each Company has multiple Clients, then you need a field in the client
table which unambiguously identifies which Company they work for. Typically
one would use an Autonumber or Number CompanyID as the primary key of
Companies (since names aren't necessarily unique - how many hairdressers are
named "Curl Up and Dye"?); in the Clients table you would have a Number...
Long Integer field CompanyID.

You might want to take a look at some of the database design tutorials in the
links below, especially the Database Design 101 links on Jeff's page.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[compsum]

Thanks to all who helped... I made great progress as a result. The latest
SQL is SELECT COMPANY.Company, CLIENTS.First, CLIENTS.Last,
CLIENTS.EmailAddress
FROM CLIENTS INNER JOIN COMPANY ON CLIENTS.[ClientID] = COMPANY.[ClientID]
ORDER BY COMPANY.Company;
The only problem is that when I try to do a sort... it doesn't sort. The
company list is a list of company names.

ok... I'm gaining on it... I established a new relationship between 'company'
in 'company' and 'last' in 'client'. The SQL is SELECT Clients.First,
Clients.Last, Company.SiteCost, Company.Company
FROM Company INNER JOIN Clients ON Company.[Company] = Clients.[Last];
It takes me to datasheet view with the field names shown... but no data?
You have been very helpful!

Just because Jane Winchester works for Acme Corp. doesn't mean that you can
link "Acme" to "Winchester" and find that relationship!

You're missing the point about how joins work. Two tables are joined by
linking the Primary Key value (which might be of any datatype, but is often a
number) of the "one" side table to a Foreign Key value of the same datatype
AND THE SAME VALUE, in the Many side table.

If each Company has multiple Clients, then you need a field in the client
table which unambiguously identifies which Company they work for. Typically
one would use an Autonumber or Number CompanyID as the primary key of
Companies (since names aren't necessarily unique - how many hairdressers are
named "Curl Up and Dye"?); in the Clients table you would have a Number...
Long Integer field CompanyID.

You might want to take a look at some of the database design tutorials in the
links below, especially the Database Design 101 links on Jeff's page.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[compsum]

What I now notice is that I can sort on every field except the first in the
query which is company??

Thanks to all who helped... I made great progress as a result. The latest
SQL is SELECT COMPANY.Company, CLIENTS.First, CLIENTS.Last,
CLIENTS.EmailAddress
FROM CLIENTS INNER JOIN COMPANY ON CLIENTS.[ClientID] = COMPANY.[ClientID]
ORDER BY COMPANY.Company;
The only problem is that when I try to do a sort... it doesn't sort. The
company list is a list of company names.

ok... I'm gaining on it... I established a new relationship between 'company'
in 'company' and 'last' in 'client'. The SQL is SELECT Clients.First,
Clients.Last, Company.SiteCost, Company.Company
FROM Company INNER JOIN Clients ON Company.[Company] = Clients.[Last];
It takes me to datasheet view with the field names shown... but no data?
You have been very helpful!

Just because Jane Winchester works for Acme Corp. doesn't mean that you can
link "Acme" to "Winchester" and find that relationship!

You're missing the point about how joins work. Two tables are joined by
linking the Primary Key value (which might be of any datatype, but is often a
number) of the "one" side table to a Foreign Key value of the same datatype
AND THE SAME VALUE, in the Many side table.

If each Company has multiple Clients, then you need a field in the client
table which unambiguously identifies which Company they work for. Typically
one would use an Autonumber or Number CompanyID as the primary key of
Companies (since names aren't necessarily unique - how many hairdressers are
named "Curl Up and Dye"?); in the Clients table you would have a Number...
Long Integer field CompanyID.

You might want to take a look at some of the database design tutorials in the
links below, especially the Database Design 101 links on Jeff's page.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[John W. Vinson]

What I now notice is that I can sort on every field except the first in the
query which is company??

This is another of the problems with the Lookup Field.

You're sorting on the concealed numeric CompanyID. What you see is NOT what
you get, when you have lookup fields!

If you *join* the company table to the client table, you will be able to sort
on the actual company name field, which is in the Company table.

John W. Vinson [MVP]

 

[compsum]

John, I seem to be going from bad to worse. I deleted the numberic
CustomerID and created a new query. When I set up the query it asks for a
paramater on Customer? I've tought Word, Excel, PowerPoint and have never
had the issues I'm having with Access. The SQL is SELECT COMPANY.Company,
CLIENTS.First, CLIENTS.Last, COMPANY.NoPages, COMPANY.SiteCost,
COMPANY.DatePosted
FROM CLIENTS INNER JOIN COMPANY ON CLIENTS.[ClientID] = COMPANY.[ClientID];
Sorry to be such a pest.

 

[compsum]

John, maybe this will help. I'm a computer tech with over 300 clients so I
made a table with all their personal info. I've developed websites for
around 25 of them and created a table with the Company and website
information. I'm trying to link the two tables with a Query where I can see
selected Company info as well as some selected personal info.

 

[John W. Vinson]

John, I seem to be going from bad to worse. I deleted the numberic
CustomerID and created a new query.

:-{(

Why on earth would you delete the CustomerID!? I *certainly* did not suggest
that.

I hope you have a backup. If you don't then there is probably no link between
the two tables, and you'll have to reenter all the data.

Please let's start at the beginning: properly normalized tables. Post your
current table descriptions in the format

Tablename
Fieldname <Primary Key>
Fieldname
Fieldname

and indicate what (if any) relationships you have defined. It would also help
if you could describe the Entities (real-life persons, things, or events) that
each table represents, and how those entities are related in real life (e.g.
Each Company can have one or more Customers, each Customer belongs to one and
only one Company).

John W. Vinson [MVP]