Look-up Query Issue ??

[BettyWho]

Hi

I apologise I am new to this forum., I'm not sure if this is the best place for this question and if it isn't I am sorry. But I am stumped It has been far too long since I did any access work and I cannot figure out a query issue. I suspect its got to do with the look up fields but I'm hoping someone can shed some light for me on how to fix this it is driving me nuts:banghead: and I know the answer is right in front of me but I'm missing it!

So I have two tables. The first is a list of schools. The second table references the school name but adds data such as personal and HR there is a look-up field referencing the first table in a comb box. I have created a query (so far so good) But when I run the query using [Which school] & "*", I enter "A" to return all results starting with "A", but the query runs returning no results. However is I use "1" (which obviously references all schools listed under A it returns all results for "1" showing them in text format. What have I done wrong?!?! Please help. Thanks in advance.

 

[Isskint]

Welcome BettyWho

Ok your post is a little muddly but let me try to help. first a couple of questions.

1) Is the lookup field referencing the first table in a combo box. Is this lookup in the table or on a form? Using lookups in a table is never a good idea. The best way to do this is create a separate table for the lookup items and use this tables Primary Key (PK) values in your main table (known as a Foreign Key or FK).

2) Regarding the data stored in the second table. If you will have more than 1 record for a single school then fine, but if there is only going to be 1 record per school, then you only need 1 table.


The issue you are describing suggests you are looking at a numeric field but trying to criteria it with Text data.

In general terms you would have this set up; (for multiple same school records in the second table)

tableSchools
schoolID AutoNumber PK
schoolName Text
other fields

tableSchoolDetails
schooldetailsID AutoNumber PK
schoolID Number (FK)
other fields

Create a relationship between the tableSchools PK and the tableSchoolDetails FK.
With this done, in a query when you type in a text value ("A") as a criteria for school, the query will return the relevant information.

 

[Pat Hartman]

You are running into one of the problems with table level lookups. It is impossible to tell when you are working with the "id" value which is usually numeric and when you are working with the "lookup" value which is usually text.

Best advice is get rid of the lookup on the table. Use lookups only on forms.

The crutch you will loose is if you open the table that had the lookup now, you will only see the "id" field, you will actually need to create a query with a join to the lookup table to see the "text" value in the same recordset.

Table level lookups are a crutch for total novices. As soon as you progress to queries and code, they are nothing but trouble.