imported field list w groups in field

[Maarkr]

I've been getting a lot of this and I just can't make it work. People will
do a data dump from another system, in either a txt or xls format, and the
field will include a list of names with the agency... so like the records in
the field will include finance, list of 10 names, financeB, list of 4 names,
safety, list of 3 names, Support, list of 15 names...
field1
Finance Offc
bob...
sally...
joe...
financeB Offc
will...
fred...
etc.
In Excel, if I break out the agency name to a new column, you can run a
'copy down' routine to list the agency in another column beside the name. I
can run a query to break out the office names from the personnel, so thats
not a problem.
I hope you get the idea. How can I treat this data dump in access to have
an end product of people separated into their agencies? I thought about a
vba loop routine but not sure how I might set it up.

 

[S.Clark]

The family of string functions can help to breakout the data.
Left$
Right$
Mid$
InStr
Replace

x = "Steve Clark"
intSpace = InStr(x, " ")
strFirst = Left$(x, intSpace-1)
strLast = Mid$(x, intSpace+1)

 

[KARL DEWEY]

Do you have a list of offices to use in separating the data?

 

[Maarkr]

no, but I can run a maketable query to parse out a list; they said the list
should not change...
this particular case is strange cause the office names run across several
fields:
f1 f2 f3
*100 Fin ance Dep artment Section 4
*Bob Smith 414 ssn's
*Mary Jones 528
*150 Fin ance Dep artment Section 5
*Joe Schmo 213
*10 more names
different dept etc
so I can run a query to get the name by like, f1+f2+f3 = like "*Section*"
but how do I tag Bob and Mary belonging to the 100 Finance Dep...?
I even tried some subqueries but I didn't get it to work.

 

[Maarkr]

I can parse out the dept names, I need to have a report of the names by dept.

 

[KARL DEWEY]

You are showing the first character as an asterisk. Is this correct?

 

[Maarkr]

the f1 field has an asterisk...

You are showing the first character as an asterisk. Is this correct?

 

[KARL DEWEY]

This works for your sample data but might not for larger dataset. Add
autonumber plus another record on the end. Change table name from Maarkr to
what you have.

Maarkr_1 --
SELECT Maarkr.Auto,
IIf(Val(Replace([Maarkr].[F1],Chr(42),""))>0,Replace([Maarkr].[F1],Chr(42),"")
& [Maarkr].[F2] & [Maarkr].[F3],Replace([Maarkr_1].[F1],Chr(42),"") &
[Maarkr_1].[F2] & [Maarkr_1].[F3]) AS Expr1,
IIf(Val(Replace([Maarkr].[F1],Chr(42),""))>0,"",Replace([Maarkr].[F1],Chr(42),""))
AS Expr2,
IIf(Val(Replace([Maarkr].[F1],Chr(42),""))>0,"",Right([Maarkr].[F2],3)) AS
Expr3, Maarkr_1.Auto
FROM Maarkr, Maarkr AS Maarkr_1
WHERE (((Maarkr_1.Auto)=[Maarkr].[Auto]+1));

SELECT Maarkr_1.Maarkr.Auto,
IIf(Val([Maarkr_1].[Expr1])>0,[Maarkr_1].[Expr1],[Maarkr_1_1].[Expr1]) AS
Expr4, Maarkr_1.Expr2, Maarkr_1.Expr3
FROM Maarkr_1, Maarkr_1 AS Maarkr_1_1
WHERE (((Maarkr_1_1.Maarkr.Auto)=[Maarkr_1].[Maarkr.Auto]-1) AND
((Maarkr_1.Maarkr_1.Auto)=[Maarkr_1].[Maarkr.Auto]+1) AND
((Maarkr_1.Expr2)>"") AND
((Val(IIf(Val([Maarkr_1].[Expr1])>0,[Maarkr_1].[Expr1],[Maarkr_1_1].[Expr1])))>0))
ORDER BY Maarkr_1.Maarkr.Auto;