Append Query - adding data to fields in a table 1

[rj51cxa]

I have a table with three fields - CompetitorNumber, EventRef and Score. For each competitor there are a number of event references, against which a score is added from a form.

There are currently 200 competitors and I need to add five extra events. To do this long hand means making 1000 key strokes and I was wondering if it is possible to write a query that adds the following information:

CompetitorNumber EventRef
1 30
1 31
1 32
1 33
1 34
2 30
etc for competitors 1 - 200

Any advice would save me a lot of time.
Thanks a lot
John

 

[Remou]

I guess you have a competitors table?

How about:

[tt]INSERT INTO EventCompetitor Table (CompetitorNumber,EventRef)
SELECT CompetitorNumber, 25 As EventRef
FROM CompetitorTable[/tt]

You could also run the SQL in VBA for a selection of EventRefs.

http://lessthandot.com

 

[rj51cxa]

Hi Remou,

The table in question is TblScores. The fields I mentioned are in that table which is not linked to any other table. However, I do have another table which has a list of competitor numbers.

I think what you are saying is that I add one event reference at a time. Would that be correct?

Best Regards
John

 

[Remou]

The above example adds all competitors with EventRef 25. You could also keep a numbers table, or Events table and use that like so:

[tt]INSERT INTO EventCompetitor Table (CompetitorNumber,EventRef)
SELECT c.CompetitorNumber, e.EventRef
FROM CompetitorTable c, EventTable e
WHERE e.EventRef Between 25 And 30[/tt]

This would insert a record for each competitor and each EventRef 25 - 30, inclusive.

http://lessthandot.com

 

[rj51cxa]

Thanks Remou,

That worked a treat. I did the job in five minutes instead of five hours!

Your help is much appreciated, as always.

Best Regards
John