How do I do this?

[Tomyumflavour]

Hello, I would be grateful if someone could help me with this:

I have a large table:

Column 1) Client IDs
Column 2) Their weights measured every two weeks from say 2008.
Column 3) What exercise class they did during those two weeks. There are many different classes but the one thing that they have in common is that all of them have done a zumba class at some point. The date for this class is different for all clients, so I can't just pick a date e.g. 01/01/2009. Some will have done ten zumba classes.
Column 4) Date


How do I extract the data so that I end up with the dates and weights of people 4 weeks before and 4 weeks after their first zumba class?

Thanks

 

[Beetle]

Well, generically speaking, it's not difficult to get the first Zumba class for each Client using a Totals query. You would use Group By on the Client and the Class and Min on the Date, with "Zumba" (or the ID value of Zumba) as the criteria for the Class.

You could then create another query that uses the first query in a join, or a subquery, to extract the dates 4 weeks before and after (actually, it could probably all be done in one query/subquery but that's a little difficult to explain without seeing an actual sample of your data).

If you want to post a small (1 table) db with a sample of your actual data (with any sensitive info removed of course) then we can get more specific.

Edit: BTW - if Date is the actual name of that field in your table, you should change it, as this is an Access/VBA reserved word and should not be used as the name of any user defined object.

 

[Tomyumflavour]

This is a mock up of the data. I know that it is on excel and that the weights do not make sense, but I just made them up for this example.

The actual database is much bigger and has 1000s of entries.