Edgar-
Did you download and read "Problem With Lookups.doc" in the Files /
Articles_Excerpts folder on the group web page?
If you don't want artificial keys, then don't use AutoNumber, but sometimes you
can't avoid that. Your City field is a good example because city names aren't
unique. If a user picks "Franklin" - does he or she want "Franklin, AL" or
"Franklin, CT" or one of the 28 other US cities with that name? So your city
lookup table will probably look like:
CityID AutoNumber
CityName Text
StateName Text
If you try to get rid of the AutoNumber, you would need to make both CityName
and StateName the Primary Key to ensure uniqueness. But a Combo Box can be
bound to only one field, so you would have to jump through hoops to allow the
user to pick the correct combination and then set two fields in the related
table. AutoNumber solves that by returning one unique number.
If you use AutoNumber, you must remember to always include the lookup table in
any query you build. The number 592 means nothing as a foreign key value for
City, but when linked to the lookup table, you can include the city and state
fields from that table to make it easy to sort on search on the actual name.
HTH...
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
rivera_vazquez_edgar
Sent: Saturday, June 04, 2011 12:17 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Problem with a query, Help!
Hello Paul, and thanks for your reply.
I tried changing the "ACTIVO" criteria for the record autonumber for that value
(2) and it worked. The thing is that such field seams to be bound to the first
colum (the autonumber). Is there a way to change this so it looks up the actual
value? I want to do this because I have the same problem with the 'City' field.
In criteria I put [Enter City] and in the pop up i have to put the number of the
first colum in the tblCity instead of the actual city name. The problem with
this is that I would have to know the corresponding number of a given city
everytime I try to run this query, and that doesn't sound too fun. What can I
do?
Edgar
--- In MS_Access_Professionals@yahoogroups.com, "Paul" <pbaldy72@...> wrote:
>
> Two possibilities come to mind. Most likely is that the Status field is
numeric, and is expecting 1 (or the appropriate number) instead of "ACTIVO".
The other possibility is that a field in the join has incompatible data types,
like IdCli is a number in one table and text in the other.
>
> Paul
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "rivera_vazquez_edgar"
<xtintores@> wrote:
> >
> > Hello again...
> > So I've made all my tables, ralationships and forms and now I'm off to
making the querys for my reports. I tried to make my first query and when I try
to run it get a message that says "Data type mismatch in criteria expression".
> >
> > The SQL is:
> >
> > SELECT tblClients.Status, tblClients.Cuenta, tblClients.Company,
tblClients.Tel1, tblClients.Tel2, tblClients.Tel3, tblContacts.Title,
tblContacts.FirstName, tblContacts.MiddleName, tblContacts.LastName1,
tblContacts.LastName2, tblContacts.Nickname, tblContacts.JobTitle,
tblContacts.Tel4, tblContacts.Tel5, tblAddress.AddrType, tblAddress.Address,
tblAddress.City, tblAddress.Tel6, tblAddress.Tel7
> > FROM (((tblClients INNER JOIN tblContacts ON tblClients.IdCli =
tblContacts.IdCli) INNER JOIN tblAddress ON tblClients.IdCli = tblAddress.IdCli)
INNER JOIN tblClientInventory ON tblClients.IdCli = tblClientInventory.IdCli)
INNER JOIN tblServices ON tblClientInventory.IdInven = tblServices.IdInven
> > WHERE (((tblClients.Status)="ACTIVO"));
> >
> > Can someone rescue me out of this one?
> > Thanks a lot, Edgar
> >
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar