Split a table into multiple tables based on criteria

[mattster1010]

Morning All,

I have a table called User_Stats that contains records of enquires that have been completed by a certain employee. The table contains multiple employees that I would like split into thier own seperate tables. I could achieve this by a make table queiry with the employee name specified as the criteria, however that would mean making over 60 make table queries.

Is there an easier way to do this without the need to create lots of seperate queries?

Regards,

Mattster

[OTWarrior]

IMO the easiest way would be to create a form that has a drop down menu attached (which calls the values of the employee names), and changes a subfrm which has one query as a source object.

Give me a few mins, I'll give you a sample of what I mean....

[OBP]

But why make all those tables, the data will still be the same?

[mattster1010]

I want to make seperate table for each employee as Im finding datedif in time between the first time in a table and the last. I have sorted each time column by descending to get the lowest to highest value.

If I keep all records in one table I wont be able to find out each individual employees time difference due to the the first and last time column being for everybody.

[OTWarrior]

This is kind of what I am thinking.

Obviously you will need to use your table, and modify the query and form (including the subform), but this should point you in the right direction.

note, I included a datediff textbox on the main form which will calculate the times for you, to show that it will change depending on what record you are currently on.

PS: I don't know how well that will work on multiple records, let me know if it is suitable :)

[mattster1010]

Thank OT, legend.

I will elaborate on the example you have given me and take it from there.

I think the fact that each employee does have multiple records could possibly be an issue. I may think of changing my stratergy slightly, instead of working out the difference between the first and last record in the time field, I may calculate the difference in time between the first and second, second and third records etc.... using BOF & EOF to loop through the records in a table and then display the total in textbox on a form.

Is that possible?

[OTWarrior]

possibly....but you could also use the datediff function as a field in the first query, then use that with a second query which has grouping on and sum the datediff value, giving you a total.

so you would have (fields names separated with a minus sign):

qry1:
ID - name - Firsttime - LastTime - Expr1

qry2:
(Grouped)
Name - SumofExpr1