SQL Make Table with twist

[Doctor]

I have a table called tblChurchTeams with the following structure

ChurchTeamID, ChurchID, EventID, NumberOfTeams
1, 1, 1, 2
2, 1, 2, 4
3, 1, 3, 2
4, 1, 4, 2
etc...

I would like to make another table from this table but I would like one
record in the new table for each team that was entered in the previous table
in the NumberOfTeams field.

MakeTable Desired Structure:
TeamID, ChurchTeamID, Team Number
1, 1, 1
2, 1, 2
3, 2, 1
4, 2, 2
5, 2, 3
6, 2, 4
etc...

For instance, in the first table it says how many teams the church has for a
particular event. I want the second table to split the number of teams they
have into one record for each team.

Perhaps there is a simpler way to accomplish this; I am open to suggestions.
Any help and advice would be greatly appreciated.

 

[tina]

well, yes, you could do it in VBA with some looping code. but i wonder if we
should take a look at your tables' structure first. can you post those, as
(example)

TableName
FirstField (indicate primary key field where appropriate)
SecondField
ThirdField (indicate foreign key field where appropriate)
FourthField
FifthField

and tell us what the purpose of the database is - what data you're trying to
store/manipulate.

hth

 

[Doctor]

Tina, sure:

tblChurchTeams
ChurchTeamID (Pkey)
ChurchID (Fkey)
EventID (Fkey)
NumberOfTeams

The second table does not yet exist, but in my previous post I suggested
this structure:
tblIndividualTeams
IndividualTeamID (Pkey)
ChurchTeamID (Fkey)
TeamNumber

What I am trying to accomplish is a church will say how many teams they are
planning to enter in each event for competition as noted in the first table.
What I would like to do is take the number of teams that they entered in
NumberOfTeams and create a record for each team in a new table so that I can
then assign lane numbers, etc. to individual teams.
For example if a church says that they have three teams entering in a relay,
I want to create three individual records for those teams in another table
automatically when called. In the second table the team number would come
from the number entered in NumberOfTeams. If they entered 3 in the first
table, then in the second table on the TeamNumber field, I would like to have
1 for the first team, 2 for the second team, and 3 for the third team. Make
sense?

Thanks again.

 

[Dale Fye]

Doctor,

Create another table (tbl_Numbers) with a single field (lngNumber). Put 9
records in it (the numbers 0 to 9).

Now, create a query:
Select CT.ChurchTeamId,
CT.ChurchID,
CT.EventID,
lngNumber as ChurchTeam#
FROM tblChurchTeams as CT, tblNumbers a N
WHERE N.lngNumber > 0
AND N.lngNumber < CT.NumberOfTeams

I use tblNumbers for a wide variety of reasons (mostly date issues), but
this is a perfect use of this. You can expand this list beyond 9 numbers by
creating another query (qryNumbers) which can be expanded as large as you
want it. This is a simple example to expand the list to 0-99:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tblNumbers as Tens, tblNumbers as Ones

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Doctor]

Thanks, Dale! This will work great!

Doctor,

Create another table (tbl_Numbers) with a single field (lngNumber). Put 9
records in it (the numbers 0 to 9).

Now, create a query:
Select CT.ChurchTeamId,
CT.ChurchID,
CT.EventID,
lngNumber as ChurchTeam#
FROM tblChurchTeams as CT, tblNumbers a N
WHERE N.lngNumber > 0
AND N.lngNumber < CT.NumberOfTeams

I use tblNumbers for a wide variety of reasons (mostly date issues), but
this is a perfect use of this. You can expand this list beyond 9 numbers by
creating another query (qryNumbers) which can be expanded as large as you
want it. This is a simple example to expand the list to 0-99:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tblNumbers as Tens, tblNumbers as Ones

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

That last line should read:

AND N.lngNumber <= CT.NumberOfTeams

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.