Format registration data (1 Viewer)

[JemCain]

I run activities for children during the school holidays and log their personal details in the following table:

Whenever they attend an activity, I log their attendance in the following table:

where ActivityNumber is 1 for Monday's activity, 2 for Tuesday's activity etc. (details about the activites- e.g. time and location- are held in a separate table). Children generally attend more than one activity, as in the Datasheet View below:

At the end of the holiday, I have to produce a report to the funders in the following format (note the data is just an exmaple and not a real child):

Can anoyone tell me how I can build a query that will take the data in my tables and format it so I can export it straight into the Smartsheet above? At the moment I enter it by hand but it's quite time consuming and risks me making mistakes.

Thanks very much.

 

[CJ_London]

To get the daata looks like you need to use a crosstab query

group by names, gender, medical conditions would be your row headers
group by date would be your column header
the (first) existence of a matching record in the attendance table would be the value

whether this then needs to be exported as an excel or .csv file for importing to Smartsheet, or whether Smartsheet can link to the query, I wouldn't like to say as I have no experience of smartsheet

 

[JemCain]

To get the daata looks like you need to use a crosstab query

group by names, gender, medical conditions would be your row headers
group by date would be your column header
the (first) existence of a matching record in the attendance table would be the value

whether this then needs to be exported as an excel or .csv file for importing to Smartsheet, or whether Smartsheet can link to the query, I wouldn't like to say as I have no experience of smartsheet

 

[JemCain]

Thanks CJ. I always wondered what a Crosstab query did. Bugt I'm not sure I've understood how to assign the value. I've used ChildNumber as that's the linked field between the two main tables, but what I really need is a 1 if they attended, rather than their child number. Is this possible.

To get the data into Smartsheet, I just export into Excel and cut and paste.

Thanks again.

 

[CJ_London]

your destination form is showing checkboxes so modify childNumber to childNumber is not null

This will return a boolean value of -1 for true and 0 for false ( a checkbox is just how this is presented)

Some apps use 1 for true and 0 for false. If this is so in your case then can use abs(childNumber is not null) instead

Also - you have not followed my suggestion

the (first) existence of a matching record in the attendance table would be the value

change the table to the tblChildAttendance

The other thing you may need to do is change the link between childrendata and attendance data to a left join

and you might want to put some criteria on activitydate to limit the number of days to be returned

 

[JemCain]

Thank you. That works great, excpet I've used Count instead of First in the Value field.