Refering to a group of fields!

  • Thread starter Thread starter Majeed
  • Start date Start date
M

Majeed

I have a Table in Access database wich contains 11 similarly named field
like SC1 , SC2 ,SC3....SC11. I do a lot oq SQL queries referening to this
group of fileds and typing all of them repeatedly is cubmersome. Is there
any means of naming this special group to save me typing them all.

For example

SELECT * FROM TMrecords
WHERE (((TMrecords.SC1)=[SCORER])) OR (((TMrecords.SC2)=[SCORER])) OR
(((TMrecords.SC3)=[SCORER])) OR
(((TMrecords.SC4)=[SCORER])).................OR
(((TMrecords.SC11)=[SCORER]));

To something like
SELECT * FROM TMrecords
WHERE (((TMrecords.SC1GroupSet=[SCORER])) ;


Thanks.
 
There's no such shorthand. The fact that you have such a repeating group in
your table is a sign that the database hasn't been properly normalized.

What you should have rather than 11 fields like that is 11 rows in a table,
with each row containing a number from 1 to 11 and the value that would be
in the corresponding SCn field.

You may want to check out the following:

Fundamentals of Relational Database Design
http://support.microsoft.com/?id=129519
Understanding Relational Database Design
http://support.microsoft.com/?id=234208
Database Design Principles
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSystemObject.htm
Understanding Relational Database Design (downloadable Word doc)
http://support.microsoft.com/?id=164172
Fundamentals of Relational Database Design
http://www.microsoft.com/technet/Access/technote/ac101.asp
 
Thanks Douglas.

I will refer to the articles that you have posted, but just as a background,
these fields I have in my table , are names of players in a soccer team. I
have thought about optimizing that , but I really could not find a method
where I can have all the names of the soccer team with 13 or even 16 players
( including substitute players) in one field and yet do any kind of easy
manipulation of data when refered to those players. data such as number of
games each players has played, tptal number of goals and so forth.

Any insight in optimization is welcome.

Thanks

Majeed


Douglas J. Steele said:
There's no such shorthand. The fact that you have such a repeating group in
your table is a sign that the database hasn't been properly normalized.

What you should have rather than 11 fields like that is 11 rows in a table,
with each row containing a number from 1 to 11 and the value that would be
in the corresponding SCn field.

You may want to check out the following:

Fundamentals of Relational Database Design
http://support.microsoft.com/?id=129519
Understanding Relational Database Design
http://support.microsoft.com/?id=234208
Database Design Principles
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSystemObject.htm
Understanding Relational Database Design (downloadable Word doc)
http://support.microsoft.com/?id=164172
Fundamentals of Relational Database Design
http://www.microsoft.com/technet/Access/technote/ac101.asp


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Majeed said:
I have a Table in Access database wich contains 11 similarly named field
like SC1 , SC2 ,SC3....SC11. I do a lot oq SQL queries referening to this
group of fileds and typing all of them repeatedly is cubmersome. Is there
any means of naming this special group to save me typing them all.

For example

SELECT * FROM TMrecords
WHERE (((TMrecords.SC1)=[SCORER])) OR (((TMrecords.SC2)=[SCORER])) OR
(((TMrecords.SC3)=[SCORER])) OR
(((TMrecords.SC4)=[SCORER])).................OR
(((TMrecords.SC11)=[SCORER]));

To something like
SELECT * FROM TMrecords
WHERE (((TMrecords.SC1GroupSet=[SCORER])) ;


Thanks.
Click to expand...
Click to expand...
 
Majeed,

How about the following:

TblTeam
TeamID
TeamName

TblTeamPlayer
TeamPlayerID
TeamID
FirstName
LastName
PlayerType 'Regular or Substitute
etc

TblGame
GameID
GameDate

TblGamePlayer
GamePlayerID
GameID
TeamPlayerID
Goals

You can get the number of games each players has played , total number of goals
scored by each player and in fact the final score of any game from
TblGamePlayer.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Majeed said:
Thanks Douglas.

I will refer to the articles that you have posted, but just as a background,
these fields I have in my table , are names of players in a soccer team. I
have thought about optimizing that , but I really could not find a method
where I can have all the names of the soccer team with 13 or even 16 players
( including substitute players) in one field and yet do any kind of easy
manipulation of data when refered to those players. data such as number of
games each players has played, tptal number of goals and so forth.

Any insight in optimization is welcome.

Thanks

Majeed


Douglas J. Steele said:
There's no such shorthand. The fact that you have such a repeating group in
your table is a sign that the database hasn't been properly normalized.

What you should have rather than 11 fields like that is 11 rows in a table,
with each row containing a number from 1 to 11 and the value that would be
in the corresponding SCn field.

You may want to check out the following:

Fundamentals of Relational Database Design
http://support.microsoft.com/?id=129519
Understanding Relational Database Design
http://support.microsoft.com/?id=234208
Database Design Principles
Click to expand...
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSystemOb
ject.htm
Understanding Relational Database Design (downloadable Word doc)
http://support.microsoft.com/?id=164172
Fundamentals of Relational Database Design
http://www.microsoft.com/technet/Access/technote/ac101.asp


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Majeed said:
I have a Table in Access database wich contains 11 similarly named field
like SC1 , SC2 ,SC3....SC11. I do a lot oq SQL queries referening to this
group of fileds and typing all of them repeatedly is cubmersome. Is there
any means of naming this special group to save me typing them all.

For example

SELECT * FROM TMrecords
WHERE (((TMrecords.SC1)=[SCORER])) OR (((TMrecords.SC2)=[SCORER])) OR
(((TMrecords.SC3)=[SCORER])) OR
(((TMrecords.SC4)=[SCORER])).................OR
(((TMrecords.SC11)=[SCORER]));

To something like
SELECT * FROM TMrecords
WHERE (((TMrecords.SC1GroupSet=[SCORER])) ;


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Many thanks.
Very interseting concept. I shall re-structure my original database and
sample the tables that you have posted. My initial impression is that there
could be more work involved in the set-up of the database with 4 tables
instead of one , and the fact that I have to re-write the existing codes in
the queries and forms.

Thanks , I shall get back to you.
 
Looks like you've been given a good start on what you should be doing.

Yes, it's probably going to be more work to code, and you'll probably have
to rewrite most of what you've already done, but the additional flexibility
and simplification of queries will more than make up for that extra effort.

For what it's worth, your model will probably have to be more complicated
than what was suggested to you. For instance, you may need to be able to
handle players transferring from one team to another during the season. You
may want to keep track of when each goal was scored, rather than total goals
per player per game. You may want to keep track of red and yellow cards. You
may even want to keep track of when substitutions were done, and which
player replaced which other player.
 
-----Original Message-----
I have a Table in Access database wich contains 11 similarly named field
like SC1 , SC2 ,SC3....SC11. I do a lot oq SQL queries referening to this
group of fileds and typing all of them repeatedly is cubmersome. Is there
any means of naming this special group to save me typing them all.

For example

SELECT * FROM TMrecords
WHERE (((TMrecords.SC1)=[SCORER])) OR (((TMrecords.SC2)= [SCORER])) OR
(((TMrecords.SC3)=[SCORER])) OR
(((TMrecords.SC4)=[SCORER])).................OR
(((TMrecords.SC11)=[SCORER]));

To something like
SELECT * FROM TMrecords
WHERE (((TMrecords.SC1GroupSet=[SCORER])) ;


Thanks.
Click to expand...
Hi Majeed,
have you considered creating a function that returns a
string containing these fields. In this function create a
loop for the required number of fields and append to a
string.

for example
dim strWhere as string
strWhere=GetFields()

' include strWhere in sql

public function GetFields() as string
dim strFields as string
dim intLoop as integer

for intLoop = 1 to 11
strFields=strFields & "(((TMrecords.SC" & _
intLoop & ")=[SCORER]))"
doevents
next intLoop

GetFields=strFields
end function

Luck
Jonathan
 
Back
Top