Searching value within specific field (URGENT Help needed)

[BHarries]

I need some urgent help. Basically I made a search form for a databse which specified what value to search in each field (as there wasn't many fields), but now I've got a database with 200+ fields. The fields can be grouped into certain headings (i.e. competence, discipline e.t.c). I'm trying to create a dropdown box named "Competence" that will include the names of certain fields and then somehow link a text box with it (Called "Rating") so that I can click on the combobox and choose a field and then enter a rating in the rating box so that when I run a query it will search for all the names of employees with that rating under the specified field name. It seems simple but I just cannot do it. URGENT help please.



Regards

[ranman256]

The comb box will have the list of fields. (And delimiter)
the text box will allow user to enter the search word.
A button will open the query.

Note: the combo box will need 2 fields: 1 to show the field name, 2 to have the delimiter.
strings will have a single quote.
numbers will be null.
dates will have #.


The form is continuous form that shows all records.
the button click will filter via code....

sub btnFind_click()
vDelim =cboBox.column(1)
Me.filter ="[" & cboBox & "]=" & vDelim & txtBox & vDelim
me.filterOn = true

end sub

[BHarries]

Thank you. Where does the code you've given me go sorry?

[Bulzie]

What does your employee table look like? Also explain this more please "The fields can be grouped into certain headings (i.e. competence, discipline e.t.c)."?

[BHarries]

What does your employee table look like? Also explain this more please "The fields can be grouped into certain headings (i.e. competence, discipline e.t.c)."?

It's a simple table. List of employees then in each column theres a competence, and there are 200+ columns. They can be grouped into headings because they fall under certain topics, so I want a combo box with the name of that topic then the combo box to have a list of all the columns that fall under that topic. This way I can seperate everything up. I want to be able to pick a column from the combo box then enter a rating so that the search looks for the rating in that specific column and shows a list of employees that have that rating.

[Bulzie]

So the table is like:

Name, Competence1, Competence2, ...
John Doe, Friendly, Smart, ...

[BHarries]

So the table is like:

Name, Competence1, Competence2, ...
John Doe, Friendly, Smart, ...

Yeah but all the comtences are rated from [0-5] so every column has the same spread of values [0-5], thats the complex bit, I can't just search a column because it has an independant value to the rest of them, they're all the same.

[ranman256]

Code goes in a FIND button click event.

[Bulzie]

So your input table is like
Name, Friendly, Smart, Timely ...
John Doe, 3, 2, 4 ...
Mary Jane, 5, 3, 4

And you want to select a competency such as Friendly and a Rating such as 5 and it would pull back Mary and anyone else that had a 5 for Friendly?
...

[BHarries]

So your input table is like
Name, Friendly, Smart, Timely ...
John Doe, 3, 2, 4 ...
Mary Jane, 5, 3, 4

And you want to select a competency such as Friendly and a Rating such as 5 and it would pull back Mary and anyone else that had a 5 for Friendly?
...

Yeah exactly that, but I'll have more than one competence in one combo box (so the search form isn't huge), so say I had a combobox for friendly, smart and timely.

[Bulzie]

Any way you can change the structure to be 2 tables say Employee and Competency?
Employee would have EmpID, EmpName
Competency would have CompetencyID, EmpID, Competency, Rating
1, 1001, Friendly, 3
2, 1006, Smart, 4
3, 1001, Smart, 2
etc,

You would link the tables on EmpID. This way you just have to search 2 fields (Competency and Rating) to find your records instead of having to deal with 200+ fields.

[orange]

I've got a database with 200+ fields.

Tell us more. Tables involved. Purpose of the database......

Sounds like a database design problem, but we know little of what you are trying to accomplish.

Perhaps:

Employee--->EmpHasCompetencyWithLevel<----Competency

[ssanfu]

I also think you have a table design error - it is designed like a spreadsheet. Spreadsheets are short and wide - databases are tall and narrow.


Consider

tblEmployees
--------------
EmpID_PK (Autonumber)
EmpFName (Text)
EmpLName (Text)


tblCompetency
--------------
CompetencyID (Autonumber)
EmpID_FK (Long) - link to tblEmployees
CompOptionID_FK (Long) - link to tblCompOption
Rating (Integer)


tblCompOption
------------------
CompOptionID_PK
CompDesc (Text)
CompTopic (Text)

[BHarries]

Any way you can change the structure to be 2 tables say Employee and Competency?
Employee would have EmpID, EmpName
Competency would have CompetencyID, EmpID, Competency, Rating
1, 1001, Friendly, 3
2, 1006, Smart, 4
3, 1001, Smart, 2

etc,

You would link the tables on EmpID. This way you just have to search 2 fields (Competency and Rating) to find your records instead of having to deal with 200+ fields.

I'm getting this databse from an excel spreadsheet, I'm importing it into access so how would I make 2 tables and link them please?

[orange]

Import to a temp table in Access, then use queries(and/or vba) to move data to properly designed/normalized tables than support your requirements.
Get you database designed to meet your requirements-- that is critical.