Query help

[Guest]

My access database is for law enforcement vehicles. So I have a table with
the officers names in, table name is Officers. Then I have another table
(Vehicle table) which is my big table with all my other information. And I
have combo boxes in there to limit the data and to also to make the data
consistent. So when they enter the officer’s name in the Vehicle table they
just use a combo box - they get the list of officers and pick a name.

So now I am trying to query by let’s say an officer, but won’t pull up the
right information. And it won’t put the names in ascending order in the
query.

I also would like to group my districts (the town the officers are from).
So in the Vehicle table I have a combo box for the districts with a list of
the districts so they can pick which one they need. But when I try to query
by just one district (town) it won’t work.

So did I set up my whole database wrong? Do I need to add more tables?
Thanks for your help.

 

[Bob L.]

Not enough information to understand what's happening. Suggest you open
the Northwinds sample database and look at how they do queries and
tables etc.

Bob L.

 

[Guest]

The following is from my table in design view:

Field Name – Districts
Data Type – Text, but when you look at the properties for this field under
the Lookup Tab I have:

Display Control – Combo Box
Row Source Type – Value List
Row Source – then I have a list they can pick from (Example – Bismarck;
Dickinson; Fargo; etc)
Bound Column – 0
Column Count – 1
Column Heads – No
Column Widths – blank
List Rows – 8
List Width –Auto
Limit to List – Yes

Now I make a table and would like to group all of the Bismarck, Dickinson,
Fargo, etc.
But when I do that the results are 1, 2, 3, etc.

 

[Bob L.]

Kelly,
That's because the value is what is stored in that field. I find look
up values in tables to be confusing (though it may be the preferred
way). Instead, I suggest creating another table called tblDistricts
with an auto id field and a field named something like fldDistrictName.
Then populate the table with a few of your district names. Then go
to tools, relationships and add the 2 tables. Create a relationship
between the district ID field in your orignal table and the new autoid
field in this table. Now when you run a query and add these tables you
can include the name of the district in the fields you want shown in the
query results. You can then do groupbys and sorts on this field as well.

HTH, but as I said before I suggest you spend some time with Northwinds
sample database to better understand how all of this stuff works.