Getting querie to list everyone who has a specific key

[access challenged]

I have a database set up listing first and lastname and then keys that people
have check out. Each key is in a different column but listed in the order
they were checked out . This means Joe may have checked out key 58 in column
1 and Sally may have checked out key 58 in row 8. If I want a list of
everyone who has key 58 how do I run the querie? I can get it to run with
just the column of names and picking only one key column to check but I would
like to be able to search all key columns. When I list all columns it seems
to think I only want the info for people have checked out key 58 in all
columns not any columns. HELP!

 

[KARL DEWEY]

First you need to change your database from spreadsheet to what Access is
intended, a relational database.
Table like this ---
Employee --
EmpID - autonumber - primary key
LName - text
FName -
etc -

KeyList --
KeyID - autonumber - primary key
Key Num - text
Location - text - what the key opens

KeyIssue --
IssueID - autonumber - primary key
KeyID - number - integer - foreign key
EmpID - number - integer - foreign key
IssueDate - DateTime
EstRtn - DateTime
Return - DateTime
Rmks - text

The Employee and KeyList tables are set as a one-to-many relationship to the
KeyIssue table on the primary to foreign keys.
Use form/subform Master/Child linked on EmpID to issue and receive returned
keys.

How is it that you can issue key 58 to two people at the same time?

 

[Jerry Whittle]

The root problem is that you have keys across in columns like a spreadsheet.
This is bad for a number of reason including, as you have found out, creating
a query to look across all the columns. Also Access can have a maximum of 255
columns in a table. What happens if you need to track more than 255 keys? An
when you do add another column, you need to also modifiy any forms, reports,
and queries based on this table.

Here's what you need as a minimum:

A Person table with information about the people who can check out a key.
Have an autonumber field as the primary key (no pun intended). Call it PerID.

A Keys table with information about the keys. If you already have a unique
KeyNumber, you could use that as the Primary Key (there I go again).

Next a KeyCheckout Table. This table would have an autonumber Primary Key
field (I can't help myself). It would also have PerID and KeyNumber foriegn
key fields that join to the other two tables. I'd also see a CheckOutDate and
CheckInDate fields plus a Notes field (Memo data type possibly) to add any
needed information.

It the KeyCheckout table you create a new record by pulling in the PerID
number for the person checking out the key and KeyNumber for the appropriate
key. It would also have the date and time checked out. When returned update
the CheckInDate field.

Now you can easily write a query to see who has or had Key 58.

I did notice one problem where you state that multiple people can have key
58. That could be a problem as keys are unique. Do mean something like who
has a key to Room 58 or Toolbox 58?

 

[John Spencer]

Bad design. IF you have to live with this then you need to put the criteria
under each column (field), but you must put the criteria for the key on
separate rows.

You could use a UNION query as a source query and then query that.

OR you could change your table structure to something along the lines of
Person
KeyNumber

AND have one row for each person and key they have out. Then your query is
really simple - if you are looking for whoever has key 58, enter it once and
get back a list of every person that has the key.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..