Calculated Field based on Another Record in same Query

[catluvr]

I can't quite figure this one out. I help run a Girl Scout Camp and we have units assigned to Morning Flag Ceremony, Afternoon Flag Ceremony, and Flag Training (to learn HOW to do the ceremonies).

Each of these is a different activity with a different Activity ID.

A report is run for the person that does the training and it looks like:

Thursday
Unit 12 Morning Flag Ceremony 8:45 am
Unit 15 Flag Training 9:30 am
Unit 18 Flag Training 9:30 am
Unit 18 Afternoon Flag Ceremony 2:45 pm

Friday
Unit 15 Morning Flag Ceremony 8:45 am
Unit 20 Flag Training 9:30 am
Unit 24 Flag Training 9:30 am
Unit 20 Afternoon Flag Ceremony 2:45 pm

Saturday
Unit 24 Morning Flag Ceremony 8:45 am
etc.
.
.

Anyway, the person who does the training would like there to be a designator after "Flag Training" to indicate whether the unit is doing a morning or afternoon flag ceremony.

I know I could add an activity - "Flag Training - AM" and change the other to "Flag Training - PM," but I also have reports by activity and these wouldn't be the same for that report.

I was hoping that I could make a calculated field in the query to concatenate the "AM" or "PM" onto the "Flag Training" activity.

My query consists of UnitID, ActID, time, and day. The result essentially looks the same as the report:

Unit 12 Thursday Morning Flag Ceremony 8:45 am
Unit 15 Thursday Flag Training 9:30 am
Unit 18 Thursday Flag Training 9:30 am
Unit 18 Thursday Afternoon Flag Ceremony 2:45 pm
Unit 15 Wednesday Morning Flag Ceremony 8:45 am
Unit 20 Wednesday Flag Training 9:30 am
Unit 20 Wednesday Afternoon Flag Ceremony 2:45 pm
Unit 24 Wednesday Flag Training 9:30 am

etc ...

Is there a way to have a field that uses the IIF statement to return "Flag Training - AM" or "Flag Training - PM"?

Yes, I realize that she could deduce the which unit needs which training from the report ... but, I now see this as a puzzle that needs a solution.



Thanks!


Susie


Kansas

[pbaldy]

How about

IIf(TimeFieldName < #12:00:00PM#, "AM", "PM")

Concatenate that with the activity.

[catluvr]

IIf(TimeFieldName < #12:00:00PM#, "AM", "PM")

Concatenate that with the activity.

I can do that. I'm trying to figure out how to look at which flag ceremony and particular unit is doing and concatenating something to the "Flag Training." The issue is that the two things I'm looking at are different records.

Unit 12 Thursday Morning Flag Ceremony 8:45 am
Unit 15 Thursday Flag Training 9:30 am <-- I would like to have the activity name concatenated based on
Unit 18 Thursday Flag Training 9:30 am
Unit 18 Thursday Afternoon Flag Ceremony 2:45 pm
Unit 15 Wednesday Morning Flag Ceremony 8:45 am <-- this record
Unit 20 Wednesday Flag Training 9:30 am
Unit 20 Wednesday Afternoon Flag Ceremony 2:45 pm
Unit 24 Wednesday Flag Training 9:30 am

So, if Unit 15 is doing a Morning Flag Ceremony, then Unit 15's flag training would say "Flag Training - AM" in another field.
If Unit 18 is doing an Afternoon Flag Ceremony, Unit 18's flag training would say "Flag Training - PM" in another field.


The fact that I'm trying to base it on a different record is what's tripping me up. (I can do it based on a different field of the same record.)

Thank you!


Susie
Kansas

[Gicu]

Susie, I think you want to use the AM\PM flag derived from the first activity by date for the unit; you would need to create a totals query in which you group by unit and get the Min(ActivityDate) (your date field from the table). Now in yet another query you join the table to this query on both unit and date (ActivityDate to MinOfActivityDate) and you get your flag (AM vs PM). and in the reports and any other queries that need that you simply join now the original to the latest query by unit.

Cheers,

[June7]

I am confused. Your sample output already shows am/pm.

Can use Domain Aggregate functions to pull info from other record(s). Explore DLookup and DCount.

Or nested subquery.

[pbaldy]

Sorry, I didn't pick up on the "other record" requirement. Hopefully the other's ideas get you sorted out.

[ArviLaanemets]

The query below (on fly) must give you all info you need to compose the wanted output. (To get the output for specific day, you have to add an additional WHERE clause.)

SELECT yt1.UnitID, yt1.Day, yt1.Time AS CeremonyTime, yt2.Time AS TrainingTime
FROM YourTable yt1
LEFT JOIN YourTable yt2 ON yt2.UnitID = yt1.UnitID AND yt2.Day = yt1.Day
WHERE yt1.ActionID = ActionIDForFlagCeremony AND yt2.ActionID = ActionIDForFlagTraining

[catluvr]

Thank you all!

While I didn't use Gicu's idea of needing a separate totals query, that made me think of using a separate query to pull just the flag ceremonies (not the training). Then, in the query I already had, I used June7's DLookUp idea and added a calculated field that looked up the activity in the ceremony only query based on the UnitID.

From there, it was easy to concatenate an "AM" or "PM" onto the "Flag Training" activity based on the result of the DLookUp.

I have the report the flag ceremony trainer wanted!

YAY!

Thanks!

[Gicu]