Hello,
I'm working on a study about Nurses, every two months we send out a survey, (batch 0,2,4,6,8, e.t.c) some of the nurses started later and some have dropped out, what i need to do is go down the list pull the nurse name, then the batch # and all the other instances of the nurse name. Then dump the info onto a table of its own for analysis. THEN (and heres the stumbling block) ineed it to go to the next name (row) in the table and run the same query, over and over until i have a report on how many surveys EACH nurs turned in and which.
As icing on the cake would be if it could ignore duplicates (i.e the next time it hits "Mary Riley" it passes)
I have a table in Access with 1600 rows but probably only 3-400 unique users
Is this possible??
I'm new to SQL and Access and if i can't do this here i'll have to use SPSS or STATA (which i'd rather not)
Thanks in advance
Brownie

Brownie,

What kind of analysis do you plan on doing? i.e., do you need to create a saved table in the database for each nurse for analysis, or can the analysis be accomplished dynamically, with the results being input into a report?

If you can do it dynamically, you can use VBA to query the main table for all surveys completed by a certain nurse (take from an employees table?), preform the analylsis, dump the info in a recordset in memory, move to the next nurse, repeat. Finally, use the recordset in memory to create a report you can print to the screen or printer or input into a results table (not recommended).

It doesn't matter if its one table for all results or seperate one for each, though one table witha different row for each nurse, and column for each Batch would save space and be easier to anylise.
Again i'm new to Access and SQL and VB. COuld you point me towards a tutorial or give some ideas how to code this??
i'm not asking for a handout or someone to "Just fix it" but i am kinda crunched for time so any shortcut to a tute would appretiated
Thanks
Brownie

It sounds like everything you need to do (except maybe the analysis) can be constructed using a query. Here's a link to a good overview of Access queries and SQL.

Search this forum and the Access help file for "crosstab" queries to analyze your data in a 'spreadsheet' format.

If you need more help we're here.
Jason K.

p.s. one thing to keep in mind with queries is that they don't "move" from record to record. They group and sort records based on criteria, more like separating M&M's by color.