Hi Al,

Not sure if anyone else may have requested this before, but - being the anal type I am - I thought it would be great to be able to see the times (sessions) I used board X, or sail y. This is separate from seeing the number of times board x or sail y have been used.

Any thoughts?

Cheers,
Martin

Ummmm.... It's been a while since I looked at the toy usage database. I'll have to take a look and see if that's possible.

Al.

Not today though as the bike is calling and it's still windy out there!

Al.

Hi Martin,
I've had a quick look at the database structure and as far as I can see it should be fairly easy to do. When I get time, I'll write a little script that lists all of the entries in which a particular piece of kit has been used.

What I can then do is make the name of a piece of kit (both in the diary entries and the toybox) into a link that takes you to a page that lists these sessions. You should therefore be able to click on 'JP Wave' board and see a list of all sessions where this board was used, or click on '4.2m North Zeta' and see all sessions where this sail was used. I'll list the date, location, activity and rating of course as with other lists of diary entries.

It could be a long list for some of my kit though - my current winter wetsuit has seen almost 300 uses - time for a new one I think.

I take it that's what you'd like to see?

I'll get onto it when I can - half term next week though so I may be on Dad duties a little more than usual.

Al.

Hi Al,

Yep - you got it in a nutshell. Maybe the output of the intial query should be a set of paginated overview pages - like the results of a Google search - then allowing the user to click to view each entry.

I created this when I built my MS-Access version of a windsurfing log about 12 or 13 years ago; I built a form which - using VBA in the background - built the appropriate query. I'll try to dig out the queries used later. Not sure how useful they'll be to you, but I suspect your toys/sessions tables are pretty similar to what I would have.

It's amazing how much I've forgotten

Attached is Entity-Relationship diagram for my db.

My db was never designed for multiple users, so misses any form of user_id - which you must have in your db.

Out of interest, I spent a lot of time building a date dimension table, to facilitate queries like - "show me all sessions from winter 2008"; or "show me all sessions on Wednesdays in summer between 1998 and 2002", etc. Pretty anal, or pretty useful, depending upon your view! Trouble is, I can no longer figure out how I populated the table in the first place: I must have failed to save the query, but it must use some VBA time/date functions. I'll try to figure it out later.

quote:

---

Originally posted by MartinFAttached is Entity-Relationship diagram for my db.

---

Maybe just .png's I can't post?

I'll email them.

Don't worry Martin, the query should be very simple. I have separate diary entry, toy and toy usage tables all indexed with the same id's so it's just a matter of querying the toy usage table and getting the diary entry id's from that then generating a list of diary entries using those id's. It's not difficult to do.

I also already have scripts that generate paginated views of diary entries such as this page showing your recent entries - http://tinyurl.com/42qwd54 (or not quite so recent in your case - tch tch tch!!).

I can reuse that script with a slightly different query so it shouldn't be a huge job (famous last words). I just need to find a bit of time to sit down and do it - with a little extra contingency time in case I break anything!

Al.

Hi Martin,

I think I'm getting there.

1. When viewing your list of toys (http://www.forces-of-nature.co.uk/dbdriven/home/toys.php?action=viewmytoys), there should now be a 'View sessions used' link under the details of each toy. Clicking on this will take you through to (surprise surprise) a list of all the sessions in which that particualr toy has been used.

2. When viewing an individual diary entry. I have updated the list of toys on the right hand side so that they are now links. This link also takes you to the same list of sessions that the piece of equipment has been used in.

Be gentle with it while it's new and untested, but let me know if you notice any problems as well. Hope it works as you wanted it to.

Al.

Al, that's fantastic. Works perfectly thus far. Many thanks.

Also makes me realise how many sessions I never recorded, and how little use my kit has had

That is the problem with tracking all of this stuff and one reason why I never added the cost criteria into the toyboxes / diaires. I'm sure the cost per session analysis would be pretty depressing. Especially for those people who need to travel to the beach.

It also made me realise how long ago it was I wrote all of this stuff and how long the FON 'session diaries' have been active!

Al.

I know what you mean.

When I took a look at the MS-Access application I'd written, I couldn't even remember how to open the VBA environment to look at the code. Once I'd figured that out, it took a long time to get my head around the application structure. Part-time developers, heh?

Anyhow, you deserve to give yourself a big pat on the back for all the work you've put into the forums & diaries over the years. I'm sure I'm not the only one to appreciate it.

here here

Al, on the back of your recent mod to view sessions by kit, could I be cheeky & propose another enhancement: This would be to view sessions by location?

Perhaps the easiest way to go about this would be from the users' "View Sessions" page, by making the location a hyperlink to a corresponding query.

Cheers,
Martin

No problem Martin.... I'll see what I can do, but I probably won't have time to look at it for a couple of weeks... I will get around to it, just give me a gentle reminder at the beginning of August if I haven't done it by then.

Al.

What a star!

Cheers,
Martin

Actually, thinking about it, my last suggestion about using the Location in one's list of session entries as the [only] mechanism for filtering by location is not all that practical...

Suppose you only sailed at a place once or twice, and you don't know when that was, and you have a lot of session entries. Then you'd be scanning through all your pages to find the one entry you want to filter by - which kind of defeats the object.

I know it would be more work, but another idea would be to have a dropdown box on each user's own session page, populated via a query at page load time with all the locations the user has in the db. Selecting a location from that dropdown would then apply the filter.

Just a thought!

Cheers,
Martin

Martin.....

I'll improve on this when I have time but for now here's a start:

1. Go to the Session Diaries
2. Click on 'Stats' in the menu at the top.

This brings up page with your total statistics from the diaries. The default display is based on activity type covering all years but the drop downs at the top allow you to filter this. So...

3. Select 'Location' from the 'sort by' drop down menu at the top (Keep the 'From' drop down as 'All years' for now as I've yet to implement this new feature for individual years.)

This gives you a complete list of all the locations you've ever entered into the diaries along with the number of sessions and average session rating for each location. The Location names in the list should now all be links.

4. Click on the Location name on this screen and it should take you to a listing of all sessions from that particular location.

Does that work OK?

You can also see similar stats for other members in the database by clicking on the little 'stats' button on one of their diary entry details pages. That button takes you to a similar stats section but for that member rather than for you. From there you can then drill down to their location stats in exactly the same way - If you fancy being a little voyeuristic that is!

Al.

Hi Al,

I'd say that's pretty much spot on. It certainly shows up the inconsistencies in one's data entry!! I have 5 different locations, all of which were Weston.

Many thanks,
Martin

Yep, same here, although, not for Weston obviously!

Al.