Counter in a query

[Dylan Ross]

I have the following issue. Any help would be greatly appreciated.

Here's the spreadsheet I've got:

EmpID Emp Name DepRelation DepName DepID

81117 Doe, Charles Spouse Doe, Sue 123456789
81117 Doe, Charles Child Doe, Jane 987654321
81117 Doe, Charles Child Doe, Jenny 147258369
81117 Doe, Charles Child Doe, Jamie 963852741
80184 Jones, Mark Spouse Jones, Jane 987321654
80184 Jones, Mark Child Jones, Joey 753412896
80184 Jones, Mark Child Jones, Megan 896753412

I need to establish a counter that would count Charles Doe's dependents 1-4, then start over and count Mark Jones' dependents 1-3.

Is there a way to do this?

Thanks,

DR

 

[dcx693]

What kind of output do you want? Do you want the numbers 1,2,3,4 next to the dependent names? Basically integrated into your existing output? Or do you just want:
Doe, Charles 4
Jones, Mark 3

 

[Dylan Ross]

dcx......what I'm looking for is:

1 81117 Doe, Charles Spouse Doe, Sue 123456789
2 81117 Doe, Charles Child Doe, Jane 987654321
3 81117 Doe, Charles Child Doe, Jenny 147258369
4 81117 Doe, Charles Child Doe, Jamie 963852741
1 80184 Jones, Mark Spouse Jones, Jane 987321654
2 80184 Jones, Mark Child Jones, Joey 753412896
3 80184 Jones, Mark Child Jones, Megan 896753412

Like the above.

Long story short, what my bottom line goal is, is to have one row with all the "Doe" family on it and be able to assign Sue as Dependent 1, Jane as Dependent 2, Jenny as Dependent 3, and Jamie as Dependent 4. Once this is accomplished, I would create a Make Table which would feed a mail merge in Word for a mail out I am trying to create.

Hopefully this is not completely confusing.

DR

 

[WayneRyan]

Dylan,

If you store your data:

tblEmployees:
EmployeeID
LastName
FirstName

tblDependants:
EmployeeID
Relationship
LastName
FirstName

Then it is easy to group the related dependant data
on a report, etc.

Wayne

 

[Dylan Ross]

Wayne,

Thanks for responding. I am trying to do this through queries so I can do a mail merge off of a table once I'm finished organizing the data. That's the only reason I can't put it in a report.

Any other ideas?

DR

 

[namliam]

only way to do this is to put in the data in the table itself using vba.
or create an autonumber field but that might not be acureate.
The way to go i think is to go VBA.

Let me know if you cant figure it i will then whip together a small sample for you....

Regards

Edit to add: Why do you want the numbers anyway? What is your final purpose?

 

[namliam]

Just read your final purpose sorry .... only read part of the post i guess.....

I would still say, GO VBA. Query's are no good at this kind of logic. They are data-retrieval wizards....

Maybe Yes possible with queries only but you have to have autonumbering in. But then do you want the spouce to be the first dependant allways? Cause then again GO VBA.

Regards