Trouble with Select Query

[David Sanderson]

I'm new to this group and new to Access. I have a table with contacts
that I am trying to setup a Select Query on. Each contact in the table
has "ContactType" combo box. I have 10 different "ContactType" in the
list. I want to create a query to get the results of just one contact
type. The problem I have is when I set the criteria for this field in
the query, I get a wrong data type message. As an example, I am
setting this to "Client" for one the queries.

Also, I'm not sure if this is the right approach to what I am trying
to do. The reason, I need the query, is to setup different tables for
each of the contact types. Currently the contacts db is an All
Contacts table, which I still need. However, I have a form that needs
to select from this table, 3 different contact types, Client, Agency
and Employee. Is there another approach I should be taking to resolve
this?

I appreciate any and all help.

Best Regards.

 

[Scott McDaniel]

Not sure what you mean by the "results of just one contact type" ... if you
need to filter records by ContactType, then just build a query with the
appropriate fields and set the "criteria" to = whatever type you want. The
SQL would look something like this:

SELECT * FROM tblContacts WHERE lngContactType=2

The error you're getting usually means you're using a numeric value in a
text field (or something like that). Make sure that the criteria you're
using is appropriate for the datatype of the underlying field.

Storing all your contacts in the same table (and keeping them there) is the
right way to do it. You should have a field in this table which would
indicate the type of contact (this field would be a Numeric field and would
be linked to a separate lookup table). When you need to filter your table
for a particular type (or types) of contacts, you would build a query and
filter it appropriately:

SELECT * FROM tblContacts WHERE lngContactType=2 OR lngContactType=5

What you should not do is break your table into separate tables containing
particular "types" of contacts. This is what I call "spreadsheeting" ...
trying to use Access like Excel. Users should have no reason to look at your
tables directly ... all data access should be done through forms.

 

[David Sanderson]

Hi Scott,

Thank you very much for the response. I have a few follow on
questions. Please forgive me as I am new to this. Is SQL required to
build the Queries? Can I use the expression builder to do this? If I
use the SQL, is this done in the criteria field of the query? I'm
using Data Access Pages. Will this cause any problems? And, finally,
to put the information of several contact types in a single page/form,
will I be pulling from several queries?

Sorry for all the questions. I'm completely new to access and
databases in general.

Thanks again for your help.

Cheers!

David