Help! Access and Columns.

[martha_t21]

I'm working on an Access Database for my Collage, and the parameters
are a little...weird.

I need to be able to search via a form for a clolumn:

Then, I need to search that column for certain data, already existing.

Is that even possible? Or is there a way to do that, without too much
VB, which we have not covered (And won't in this class).

Hope there's an answer out there.

 

[Guest]

Hey Martha, if you are simply trying to search for a record, not a column, in
a table then this can be accomplished without using VBA.

One method is to create a query based on the table(s) that contains the data
you need to find. For the criteria for the column that contains the data you
are looking for, type the following: Like "*" & [YOUR STATEMENT HERE] & "*"

This criteria will display an input box in which you can type what you are
looking for. The "Like" statement acts as a wild card so the user does not
have to type exacly how the info appears in the database.

The next step is to create a form based on the query. As I have stated,
there are multiple methods to do what you accomplished but this is perhaps
the easiest.

 

[Rick B]

I would build a combo box that lists all the fields in the table and then an
unbound text box in which the user can type what they want to find, then a
search button.

Since this is obviously some kind of homework, I won't go into additional
details. I'd hate to take away from your learning experience. ;-)

 

[martha_t21]

Actually, it's not traditional homework not like you'd think, anyway.
Its suppsed to be a reward for having surpassed the criteria of the
class. The problem is, is that I have over 20 files (and the drop boxes
require less than that) to search.

The database is supposed to find out (in this hypothetical situation)
which students need which class (from a list of like, 50 +) classes. I
also need to be able to reference each student's class record - a
problem I've already found an answer for. The kicker is that the
parameters of the assignment dictate that the informaiton being used
must come from an Excel spreadsheet.

THew way I had to reorder the data, has each classroomn-class as a
column of iot's own, proceeded by a column of student names:


A101 B101 C 101
Joe A.
Joe B.
Joe C.


The classes for each student are then marked "Y" fdor taken, "N" for
Needed and "C" for current. WHat I need is for Access to let me query
"Which Class Are you Looking For?" and then lookt hrough that class'
column to find which students have or have not (or are currently in)
this particular class. It's a difficult assignment - and the profeessor
insists that we not come to him to solve our problems (it's not as
brutal as it sounds).

Though, the previous answers were somewhat helpful. Perhaps I ought to
have elaborated on the assignment a little more before I asked for
help?

Perhaps, if someone might point me in the direction I need to go? Pivot
charts seem pretty well out of the question. I don't know what do do
with this, and I really don't want to tell the professor "I give up".

 

[John Vinson]

Actually, it's not traditional homework not like you'd think, anyway.
Its suppsed to be a reward for having surpassed the criteria of the
class. The problem is, is that I have over 20 files (and the drop boxes
require less than that) to search.

The database is supposed to find out (in this hypothetical situation)
which students need which class (from a list of like, 50 +) classes. I
also need to be able to reference each student's class record - a
problem I've already found an answer for. The kicker is that the
parameters of the assignment dictate that the informaiton being used
must come from an Excel spreadsheet.

THew way I had to reorder the data, has each classroomn-class as a
column of iot's own, proceeded by a column of student names:


A101 B101 C 101
Joe A.
Joe B.
Joe C.

I'd suggest a "Normalizing Union Query" to migrate this spreadsheet
data into a proper Access table. Do a Google Groups search for the
term, it's answered here often enough.

You could then use an Unmatched Query on the tall-thin table that you
get, or just about any type of query - without the seriously flawed
design you now have (it's a perfectly fine spreadsheet design but
Access isn't a spreadsheet!)


John W. Vinson[MVP]

 

[martha_t21]

Yeah...that design really bit the dust -_-;. *Fortunately*, I think I
got another layot that'll work even better - it'll be a little work for
the data entry guru, but then again, as long as it works. After all -
they wanted a *working* database. ^_^. Thanks for your help, guys!