imported field list w groups in field

  • Thread starter Thread starter Maarkr
  • Start date Start date
M

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.
 
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)
 
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.
 
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;
 
Back
Top