Yearly attendance formula

[O2BNSV]

Using v2008, can anyone recommend a formula to be placed in a cross-tab where I could calculate new, unique event participants to the same event on a year to year basis. My goal is to have a simple count of new participants as the output.

 

[lbass]

Create a SQL expression {%minyr}:

(
select min(`EventDate`)
from Table A
where A.`ParticipantID` = `Table`.`ParticipantID`
)

Add this as your column field in the crosstab->group options->print on change of year. Then use a distinctcount of {table.participantId} as your summary field.

-LB

 

[O2BNSV]

So this is the SQL expression I've created based on your input:

(
select min("START_DATE")
from table A
where A."participantID" = "Participants"."ID"
)

When I check the formula, I get the following error:

Error in compiling SQL Expression:
Database Connector Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'table'. [Database Vendor Code: 156]

Any thoughts?

 

[O2BNSV]

I think I figured out why this error might be received. Event Start date and Participant ID live in 2 different tables. I am unfamiliar with SQL. What changes need to be made and can anyone recommend a good book on SQL basics?

 

[lbass]

You need to substitute your table name and your actual field name for the participant ID (the fields should match on both sides of the "=", as in:

(
select min("START_DATE")
from "Participants" A
where A."participantID" = "Participants"."participantID"
)

-LB

 

[O2BNSV]

OK. I got the logical side of the equation to work and can save with no errors. However, again I am limited since the event start date and participant ID are in separate tables. I am guessing I need to include this in the select formula. Knowing basically nothing about SQL commands, would it be something like the following:

(
select
MIN(START_DATE)
from "Special_Event"

"ID"
from "Participants" Left Outer Join "Special_Event" On
"Participants"."EventID"="Special_Event"."ID" A

where a."ID"="Participants"."ID"

Just a stab. Please let me know what corrections need to be made. Thanks.

 

[lbass]

Try this in field explorer->SQL expression->new (not a command):

(
select MIN("START_DATE")
from "Special_Event" A, "Participants" B
where A."ID" = B."EventID" and
B."ID" = "Participants"."ID"
)

-LB