Query to search all columns and rows in a table (1 Viewer)

JahJr

Andy
Local time
Today, 06:32
Joined
Dec 3, 2008
Messages
93
I need a query that will search all the rows and columns in a particular table. The Table has the following columns:
Sport1
Team1
Score1
OpScore1
Sport2
Team2
Score2
OpScore2
Sport3
Team3
Score3
OpScore3
Sport4
Team4
Score4
OpScore4

The sport and team data all come from the same table so sport1 and team1 might be the same as sport3 and team3 in the same row or in different rows. I need to be able to search the table for all the teams that dont have a score posted in the corresponding score column. The idea is to make it easy to update all the scores.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:32
Joined
Jan 20, 2009
Messages
12,849
Restructure you table. Needing to search in this way almost always indicates bad structure.
All your scores should be in the same field with foreign key fields to indicate team and sport.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:32
Joined
Jan 20, 2009
Messages
12,849
WHERE tablename.fieldname is Null OR tablename.fieldname2 is Null OR etc etc
 

JahJr

Andy
Local time
Today, 06:32
Joined
Dec 3, 2008
Messages
93
A little more complicated than that Im affraid. Depending on the event sometimes there is just 1 team per a row other times there could be as many as 4. Everywhere there is a team listed there should be a score. The search would have to be something like this. If there is a value present in sport1 and team1 then needs to look and see if there is a value present in score1 if score 1 is null then sport1 and team1 need to be returned to query or report. If Sport1 is null then the entire row will be null. if sport 2 is null then 3 and 4 will also be null. if sport3 is null then sport4 will also be null.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:32
Joined
Jan 20, 2009
Messages
12,849
I revert to my original answer. The structure needs work.

I read the other thread. John did not say his structure was the only way, just that it could do what you had asked for at the time. I don't think was he fully aware of this latest need which is not simple in that structure.
 

Users who are viewing this thread

Top Bottom