Multiple REP entries in 1 field

[Deborah Mowry]

I am now owner of an imported table from Lotus Notes. It contains contact
information which has a column for what Rep. got the account. The thing is
there are multiple reps sometimes listed. How can I have it relate to a
table of Reps. names etc.?
I will have to download changes on a weekly basis so cannot see the Notes
people making this field become multiple fields for each rep that was part
of the account (the most is 3).
Thanks you

 

[John Vinson]

So sorry, I missed an important point in my original question. The reps are
entered by their ID number, not name, so it would have been cool to relate
to a database of Rep Info.
The IDs are mostly a letter then a number (A1, B3, S4) with the occasional
double letter (SS3). They are imported into Access separated by a comma. I
am sure we could split them at the comma, but into multiple fields? Would I
then have 3 fields of Rep1, Rep2 and Rep3 created in a new table or the
existing one, as multiple reps can be part of an account? I would then
relate these to the tblREPInfo somehow.

No - not three (or four, or five) *fields*; nor three (or four, or
five) *values in a field* as you have it now. Three or more *RECORDS*
- rows - in another table, related one to many to this table.

You'ld have your current table, and then a second table with the
unique ID of your current table as a foreign key field. This table
would have records like

ProjectID Rep
312 A1
312 B3
312 S4
313 K9
314 A2
314 S5

Project 312 would have three reps, 313 (a real dog of a project) only
one, 314 two.

Secondly, if I do split these up into another table, I would see each weekly
download having a macro applied to it that would do this. Once done, I would
append the result to the existing account table. Therefore, how exactly do I
write a macro to do the split? I assume it would be the right or left as
seen once in Excel and is it done to a query then make a table from that
query to append?

You would need some VBA code using the Split() function and appending
records to the related table. I don't think it would be easy to do it
in a Macro or directly in a Query.

I prefer to start with Macros over VBA if possible. Thank you so much for
any help, I am eager to learn more about access and finally have a chance
with this huge database.

Well... in this case I don't think you have a choice! I'm running on
two hours sleep today so I hesitate to post anything, it may make no
sense at all, but I'll flag this thread and try to come back to it
tomorrow (or, of course, anyone else is invited to chip in!)

 

[Deborah Mowry]

TOTALLY makes sense. Thank you. Now...how do I split the field? I can not
use the Left or Right as some reps are A2 and some are SS4.
I assume there is a way in a query, make a function that will find the text
before the comma....

Again, thank you very much.
Deborah