process of updating in access

[karlreiner]

What would be the best way to update something in the tables? In my case, the volunteers will log their time entry in one form. The system then needs to update their status to active if they have logged in within the past 6 months.

I have two Tables:

Daily Log
Volunteer List

1 form that logs user service times.

Example: I have 3 volunteers.

A - Active last logged Nov 1
B - Active last logged May 26
C - Inactive Last logged Febuary 12

Volunteer C then logges a time for Nov 27. I need the system to update her status to active again since she logged in. And updates the status of Volunteer B to inactive since she has not logged in for the past 6 months.

Is this possible in Access?

 

[Galaxiom]

I wouldn't store the active status if it is based solely on the login data.
When you want to find volunteers with an active status just do the date test against their last login.

Otherwise try this (air code):

Make a temporary table to get the last login of each volunteer.

SELECT [Volunteer List].[VolunteerID], Max([Daily Log].[logindate]) AS LastLogin INTO [Temptable]
FROM [Volunteer List] INNER JOIN [Daily Log] ON [Volunteer List].[VolunteerID]=[Daily Log].[VolunteerID]
GROUP BY [Volunteer List].[VolunteerID];

Then an Update query.

UPDATE [Volunteer List] INNER JOIN [Temptable] ON [Volunteer List].[VolunteerID]=[TempTable].[VolunteerID]
SET [status]=Iif(Date()-[Lastlogin]<180, True, False);

There is probably a better way but you have to watch out for the query not being updateable when you don't use a temp table with grouping.

You will be able to cobble something from the parts anyway.

BTW Recommend you drop spaces from names. Saves using the [] around everything.

 

[smig]

why do you ask the same question twice

http://www.access-programmers.co.uk/forums/showthread.php?t=184076