Results 1 to 7 of 7

converting a Delimited field to a linked table

  1. #1
    Dozza111 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3

    converting a Delimited field to a linked table

    Hi, I'm relatively new to Access, but I'd think I am a little bit computer savvy.

    We use a custom add in to Outlook that allows us to track our work time sheets with projects, time etc. from the calendar. This add in uses a file on a SharePoint server to add the project names, project manager and start dates etc..

    What we'd like to do is use access to do our invoicing, so I'd like to be able to change this file without having to use double entry though out the system.

    I can link to the file and it imports only 1 record. in one of the fields is a semi-colon delimited field, and a record delimiter of a semi-colon:

    The information is given in the following order:
    Project;Manager;ColourNo;ProjectRate;StartDate;Fin ishDate;Description;ProjectCode:

    Also where ever there is a comma in the above entries it replaces that with "_COMMA_"
    So I need to get this into a table so we can use it, and also if we update that table it would need to update the original field also.

    I figured a of query with some scripting would be the best way to do it, does any body point me in the right direction on where to start. I have had a look through some of the other forum posts and haven't found any with similar .

    An alternative but least favourable outcome would be just to have a table that when a new entry is added, it would attach the new project info to the end of the field.

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,101
    By 'double entry' you mean duplicate records?

    "I can link to the file and it imports only 1 record. in one of the fields is a semi-colon delimited field, and a record delimiter of a semi-colon:"

    Don't understand these statements, maybe because I have no experience with Sharepoint. By 'link to' you mean set a connection and then do some sort of import operation? This import brings in only one record (of how many)? The Sharepoint file is a text file with semi-colon delimiters for fields and also a semi-colon delimiter for records?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Dozza111 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    Hi Sorry I probably been more clear.
    by double entry I was referring to the having to enter the project data on the time keeping software, which is called TeamTimeSheet(SP). And also into our access database for the invoicing side of things.

    Attached is a screen shot of the table that is imported from the sharepoint list. All the relevant data is in tProjects field of record 1.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	11 
Size:	53.5 KB 
ID:	7427

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,101
    So the import brings an entire file into Access table as only one record? Never seen anything like this. What method are you using for the import?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    Dozza111 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    3
    Quote Originally Posted by June7 View Post
    So the import brings an entire file into Access table as only one record? Never seen anything like this. What method are you using for the import?
    It's a sharepoint list... so I just link it.

    The add-in that we have in our outlook syncs with this list, and this is the format it brings across.

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,101
    Sorry, this is beyond my experience and can't find anything about this issue. Nothing to indicate why this would happen.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    zulukyle is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    1
    Hi Mate

    Send me an email with what you require, khutton@trafficmaster.co.uk

    im sure we can come up with a solution

    Regards

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 03-01-2012, 12:39 PM
  2. Parsing A Comma Delimited Field
    By AccessGeek in forum Import/Export Data
    Replies: 6
    Last Post: 02-03-2011, 11:52 AM
  3. Converting/Upsizing Linked Databases
    By Chaz88 in forum Import/Export Data
    Replies: 2
    Last Post: 08-11-2010, 05:46 PM
  4. Field Types for a Linked Table
    By Jeff_J in forum Access
    Replies: 9
    Last Post: 05-05-2009, 06:12 AM
  5. Linked Table Field Validation
    By yuriyl in forum Access
    Replies: 4
    Last Post: 04-20-2009, 12:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums