Select various columns from a database but grouping only by a few

Question

Hi forum, I have a form that allows the user to search a school database by various parameters such as first name, last name, school name, etc. This search is done in various columns of my main database including the principal, teacher, viceprincipal, etc. Important to say is that I have many rows for a single school depending on how many teachers there are.

In that form there is a subform that displays the results and I would like it to display only the school name. To this point it is showing many rows for a single school depending on how many coincidences the seach finds.

I don't want to change my search because I want the search to be done in all the files available. So, I tried including in the GROUP BY statement an aggregated expression such as min() or max() but it is not working either.

I would appreciate any thoughts on how to better approach this.

Thanks!

Answer 1

> This search is done in various columns of my main database including the principal, teacher, viceprincipal, etc. Important to say is that I have many rows for a single school depending on how many teachers there are.

That seems a contradiction. You cannot both have multiple COLUMNS and multiple ROWS, it should be the latter exclusively.

tblStaff
EmployeeID long int required PK
PositionID long int required PK

---

-Tom. Microsoft Access MVP

Answer 2

Hey Tom,

My database looks something like this:

Row   SchoolName   Principal     VPrincipal     Teacher

1        School ABC      John Doe      Jane Doe        Michael Smith

2        School ABC      John Doe      Mark Nelson    Sean Core

3        School ABC      John Doe      Mark Nelson    Louise Carr

Therefore, when I'm doing the search by say "Name= Nelson", then it searches in all columns: Principal, VPrincipal, Teacher in all rows 1-3. 

And the search is working fine, the output display rows 2 and 3. However, I want the output to display only "School ABC" .

Does this make sense?

Thanks!

Answer 3

If the data you have posted as examples are held in a single table, the root of your problem is that the table is badly designed, hence the redundant duplication of data.  Also, Principal, VPrincipal and Teacher are data (positions), so data is being encoded as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

What you really have here is a many-to-many relationship type between Schools and Positions entity types, so you should start with a table for each, in broad outline like this:

Schools
....SchoolsID  (PK)
....SchoolName

Positions
....PositionID (PK)
....Position

In each the primary key can be an autonumber for convenience.

The many-to-many relationship type between the above two tables might be named Personnel for instance.  A many-to-many relationship type is modelled by a table which resolves this into two one-to-many relationship types, so in this case the table would be like this:

Personnel
....PersonnelID  (PK)
....FirstName
....LastName
....SchoolID  (FK)
....PositionID  (FK)

This table would probably include other columns representing attributes of the relationship type, e.g. the date when the person was appointed etc.  Other tables in the database might model other entity types such as courses and the relationship type between courses and personnel, i.e. which courses are taught by which teachers.

One possible user interface would be a Schools form, within which is a Personnel subform, linked to the parent form on SchoolID.

When it comes to searching the database, a search by name for instance would be on the columns FirstName and LastName.  This would usually be by means of an unbound combo box which lists all personnel in the case of a search for a single person, or a multi-select list box in the case of a search for multiple people.  If you simply want to know the name of the  school (or schools in the case of a search for multiple people) then the search would return the relevant rows from the Schools table in which each school is represented by a single row.

You might like to take a look at Databasebasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates the basic principles on which a relational database is built.  In the section on 'Retrieving data from the database' a number of search techniques and interfaces are illustrated.

---

Ken Sheridan, Stafford, England