Problem with automatic SQL lookup

[Charles E Finkenbiner]

Hi All,

I have SQL lookup that does not do exactly what I want. I am new to
Access and SQL so I am doing some practice databases. I have a table
with 3 fields:

Table - Counties:
ID - Primary Key
Name
StateCode
CountryCode

ID is an AutoNumber and the other 3 are text. I am not using any forms
at the moment, just opening the database. I am trying to get
CountryCode filled automatically once I enter the StateCode. There is
another table called States which contains the CountryCode for each
state. I have tried using a lookup in field CountryCode, the SQL code
looks like this:

SELECT States.CountryCode FROM States WHERE
Counties.StateCode=States.Code ORDER BY States.Code;

I have 2 problems with my attempts. 1 - none of the CountryCode fields
gets filled in automatically, once I enter the StateCode like I thought
it would. 2 - the first time I manually enter a CountryCode or click on
the down arrow of the combobox I get a parameter dialog box. The dialog
asks me for Counties.StateCode but that value is already in the correct
field.

Once I enter the Counties.StateCode once I can manually enter
CountryCodes until I close the database. I can not enter an incorrect
CountryCode for a State, which is what I want. I tried changing the
WHERE statement order but that did not fix anything either.

How can I get it to automatically insert the CountryCode and how do I
stop the parameter dialog from popping up?


Thanks for any and all help,

Charles

 

[Jeff Boyce]

Charles

You don't!

If you already have a States table "which contains the CountryCode", why
redundantly store it in a second table?

As one of my fellow MVPs is wont to declaim, "use [Access] a relational
database relationally!".

(use a query to show which CountryCode goes with the StateCode)

Regards

Jeff Boyce
<Access MVP>

 

[John Vinson]

Hi All,

I have SQL lookup that does not do exactly what I want. I am new to
Access and SQL so I am doing some practice databases. I have a table
with 3 fields:

Table - Counties:
ID - Primary Key
Name
StateCode
CountryCode

ID is an AutoNumber and the other 3 are text. I am not using any forms
at the moment, just opening the database.

Well... that's the source of your problem.

Table datasheets are of VERY LIMITED UTILITY.

One thing that you cannot do in a table (though it's easy on a Form)
is just what you're asking - conditional combo boxes.

I would recommend several things:
- Never, ever use Lookup Fields (well, if you've authored books on
Access development, understand their limitations, and want to use
them, go ahead - my colleague John Viescas does and I won't argue with
HIM!)
- Don't use Table Datasheets for anything but debugging
- Don't store data redundantly. If StateCode uniquely identifies a
state, then there is NO reason for the Counties table to contain the
country code; you can link to the States table instead.

John W. Vinson[MVP]

 

[Charles E Finkenbiner]

Hi,

I agree with both of you, do not store data redundantly, in real life.
Since I am learning Access and SQL I am just trying to learn what I can
and can't do with different features of Access. I am pretty sure I
could do want I want in a form, with code, but I am trying to learn what
lookups can and can't do.


Thanks for your help,

Charles

 

[Charles E Finkenbiner]

Hi,

As I think about this I guess my question should have been different. I
am not trying to store the CountryCode in the Counties table. In fact,
I do not even want access to the CountryCode field. I wanted a field
that only displayed the CountryCode based on the StateCode but does not
allow data input. I think this is where a form comes in but I have not
gotten that far yet.

Hope I don't wear out my welcome with what might seem like stupid
questions to experts.


Thanks for all the help,

Charles

 

[John Vinson]

Hi,

As I think about this I guess my question should have been different. I
am not trying to store the CountryCode in the Counties table. In fact,
I do not even want access to the CountryCode field. I wanted a field
that only displayed the CountryCode based on the StateCode but does not
allow data input. I think this is where a form comes in but I have not
gotten that far yet.

Hope I don't wear out my welcome with what might seem like stupid
questions to experts.

Not at all! Ask away.

Just be prepared for the answer "well, don't DO that - use a Form
instead" or the like. Sometimes asking "how can I do this using xyz"
requires that the response be "well, don't DO it using xyz, use
something else instead!"


John W. Vinson[MVP]

 

[Charles E Finkenbiner]

Not at all! Ask away.

Just be prepared for the answer "well, don't DO that - use a Form
instead" or the like. Sometimes asking "how can I do this using xyz"
requires that the response be "well, don't DO it using xyz, use
something else instead!"


John W. Vinson[MVP]

Hi,

No problems, I would rather learn once, the correct way, than have to
unlearn a bunch of bad habits and then relearn the correct way again.
Does that make sense? If I don't ask question then I may learn
things the hard (bad) way.

I should give you a little history as to why I approached this problem
the way I did. I was following what Access had done on another table.
I had exported a lot of data from my MS-DOS relational database, in
text delimited format. I used the 'Get External Data - Import' feature
to import this data. I let Access use the table analyzer wizard and it
recommended that the table be split into 2 tables so I let Access do
it's thing.

The resulting table1 had a lookup to table 2 field in it, assigned a
long integer format. The lookup used a SQL statement to display data in
table1 from table2.

SELECT [ID] AS xyz_ID_xyz, [State] & ', ' & [County] AS
xyz_DispExpr_xyz, [State],[County] FROM [Table2] ORDER BY [State],[County]

This looked like what I wanted to do with CountryCode so I started
following that logic. I do not understand why Access did it this way
but I thought it knew more than me, at this point anyway.

I am sure I will be posting to many more Access groups in the next few days.


Thank you, and all, very much for the help,


Charles

 

[John Vinson]

No problems, I would rather learn once, the correct way, than have to
unlearn a bunch of bad habits and then relearn the correct way again.
Does that make sense? If I don't ask question then I may learn
things the hard (bad) way.

I should give you a little history as to why I approached this problem
the way I did. I was following what Access had done on another table.
I had exported a lot of data from my MS-DOS relational database, in
text delimited format. I used the 'Get External Data - Import' feature
to import this data. I let Access use the table analyzer wizard and it
recommended that the table be split into 2 tables so I let Access do
it's thing.

The resulting table1 had a lookup to table 2 field in it, assigned a
long integer format. The lookup used a SQL statement to display data in
table1 from table2.

SELECT [ID] AS xyz_ID_xyz, [State] & ', ' & [County] AS
xyz_DispExpr_xyz, [State],[County] FROM [Table2] ORDER BY [State],[County]

One of many reasons why I view the Access wizards with a certain
amount of distrust.

Somebody at Microsoft, a bunch of folks likely, thought that Lookup
fields would be a really neat idea when they were designing A2000.
Based on several years' experience in these newsgroups, I think they
made a mistake. The Lookup Field does one thing reasonably well -
makes it easier to use table datasheets for data entry; but it causes
vast confusion and other problems as well (by conceling the actual
contents of your State field from view, in this example).

This looked like what I wanted to do with CountryCode so I started
following that logic. I do not understand why Access did it this way
but I thought it knew more than me, at this point anyway.

Good logic... but based on a flawed premise (that Access wizards are
intelligent and do things right).

I am sure I will be posting to many more Access groups in the next few days.

We'll be glad to help.

John W. Vinson[MVP]