Update query in access using count from other tbl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update query in access using count from other tbl

Author

Topic

webby1396
Starting Member

3 Posts

Posted - 2006-05-26 : 06:54:17

Hi.
I have two tables.
Activity(ActivityID,Name,Price,NumberAttending)

Student(StudentID,MorningActivityID,AfternoonActivityID)

I want to update Activity.NumberAttending with the count of students going participating in that activity for both the morning and afternoon. ie i want the numberAttending field to be automatically updated for each activityID with the amount of students attending.

I keep getting errors in Access when trying to do it.

Also, is there a way of doing it so that all of them happen automatically and i dont have to pass a parameter into the query.

TIA

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 07:23:11

IS activity.activityid = student.studentid.....?

Thanks
KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-26 : 07:23:51

Otherwise give some sample data for both table..!

Thanks
KK

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-26 : 07:54:18

Hi all,

webby - you probably shouldn't store NumberAttending in your data. That information is already available from the other data, so there's no need to store it.

Instead, you can just write a query to get hold of it when you need (I'm guessing with the ActivityID mapping)...

--data
declare @Activity table (ActivityID int identity(1, 1), Name varchar(30), Price int)
insert @Activity
          select 'some activity', 100
union all select 'some other activity', 200
union all select 'very expensive activity', 20000

declare @Student table (StudentID int identity(1, 1), MorningActivityID int, AfternoonActivityID int)
insert @Student
          select 1, 2
union all select 1, 2
union all select 1, 3
union all select 2, 1
union all select 2, 1
union all select 1, 3

--calculation
select *,
    (select count(*) from @Student where MorningActivityID = a.ActivityID) as NumberAttendingInMorning,
    (select count(*) from @Student where AfternoonActivityID = a.ActivityID) as NumberAttendingInAfternoon,
    (select count(*) from @Student where a.ActivityID in (MorningActivityID, AfternoonActivityID)) as NumberAttending
from @Activity a

--results
ActivityID  Name                           Price       NumberAttendingInMorning NumberAttendingInAfternoon NumberAttending 
----------- ------------------------------ ----------- ------------------------ -------------------------- --------------- 
1           some activity                  100         4                        2                          6
2           some other activity            200         2                        2                          4
3           very expensive activity        20000       0                        2                          2

But then again, you've said you're using Access, so maybe you're in the wrong forum...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

webby1396
Starting Member

3 Posts

Posted - 2006-05-26 : 15:53:37

Oops silly me... wrong forums indeed. what a n00b. ill post it in the access one. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources