Results 1 to 8 of 8
  1. #1
    jtf1972 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2011
    Posts
    4

    Importing ever-changing Excel data

    I have been trying on and off for months to make an Access DB handle this and I really want to get it finished. Here's my vision:

    • I have data from a partner company website that I can download into Excel workbooks. Each line is for a specific job number (there are over 4000 right now, and 27 fields for each one).
    • Every day, certain fields change for certain job numbers (status, dates, dollar amounts, etc)
    • Every day, new job numbers are added (maybe 5-10 a day)
    There are other fields I'd like to track. For instance, material delivery date, installation schedule date, issues, person assigned to, etc.
    I can't just type these in the spreadsheet because tomorrow, some of the related fields may have changed.

    What I'd like is an access DB in which I can import the newest spreadsheet each day. Then have a table (or several tables) that have the "other fields" I mentioned above. Then, I'd like to have a form in which I can add and edit information.

    Each time the main import is updated, those changes are reflected in the form, along with the added "other fields" which are all combined in an easy to read form.

    Make sense? So how do I go about making this 2nd table and marrying it all together in one form?

    Thanks,


    Jeff

  2. #2
    jtf1972 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2011
    Posts
    4
    Being impatient... bumping.
    Here's a little better explanation of what I want.

    Table 1 - master data file downloaded and imported on a regular basis. Includes primary key (job number) and 27 fields (address, costs, dates etc)

    Table 2 - I want to add to this table manually. It will reference the primary key (how? do we have to type in the job number each time?) and will have additional fields such as order date, order number, install date, etc. This is all done by hand.

    Form 1 - would like a form to fill out table 2

    Report 1 - would like a report that combines a few fields from table 1 and a few fields from table 2

    Thanks,
    Jeff

  3. #3
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    May be I can help. First about my application -

    I have a tracking system with over 200 fields, but some functions are either missing or too rigid so I export the whole db and import into my Access db.

    In my Access db I created a 2nd table with the same key as this imported table and additional info I want to track.

    First I created a form to show selected record from the imported table, no update allowed. As the "AfterUpdate" event of the key is triggered, I search for correspondent info from the 2nd table. If not found, I change the key field background color to red. If found, I display the info on the form and change the key color to green. For every "AfterUpdate" event to the field from the 2nd table, it triggers an update to the 2nd table.

    To support this function, I automated the import function, created many buttons to chart result for Exec review, and to display queries and reports notifying others to take action. I was going to also automate the emailing but got lazy.

    Hope it helps.

  4. #4
    jtf1972 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2011
    Posts
    4
    Hmm, I think I see what you're doing.
    How do you create that 2nd table to share the primary key? Basically, the 2nd table would start out blank and the only field is the primary key from the 1st table... then as you fill out the form, it fills out the table. Right?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you getting a primary key field in the data being exported to you? or are you just using the job number as the primary key? If you are you can link or import the data into an access table every day. I would suggest importing it and performing a duplicate check, any existing records on your table would need to be updated with information coming from the spreadsheet each day.

    Then if you create a subsidiary table tracking the additional things you want you could use a form/subform with the link being the job number (I'm assuming) and if you link them correctly the child item (your additional data) will inherit the parent item's (the spreadsheet job number) ID field.

  6. #6
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    The 2nd table should have the same primary key and additional fields you want to track. The form will display record from the table imported from the excel spreadsheet. When you select a record from the imported table, it will check if the same key exists in the 2nd table. If it exists, it will display these additional fields, if not, blank. As you fill out the form it will either insert a record into the 2nd table or update it.

    My additional fields are projects, target completion date, and latest comment. My form has 4 tabs: import, record update, and report.

    rpeare broght up an interesting point. I do have a duplicate import table named (importtable)_prev. Before the automated import, the old one gets copied into the prev one. The import step will delete all records, and import new, then compare the new and the prev to report any status changes, new keys added, and old keys cancelled (the tracking system doesn't export cancelled records), so I can take some action if necessary.

    Hope that helps.

  7. #7
    jtf1972 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2011
    Posts
    4
    Thanks all. There is no way I'd ever have a duplicate key. Yes, the primary key is the job number that comes from the other database.

    What I don't get is how do I link this 2nd table up if it starts out completely empty? Would I just create a new table with all the job numbers and everything else blank?

    I envision something like this:
    Table 1 has all the data including the primary key. To keep it simple, lets say table 1 has field A (primary key), B, C, D, and E.
    I go to the form and find job number 12345. I can view fields A-E, but enter data into field F, G, H. This new data is now in table 2, which has fields A, F, G and H.
    Does/can it work that way?

    I was also thinking this for having multiple tables.
    Job 12345 could have 3 trouble tickets. So I'd have a one to many relationship between Job 12345 and trouble ticket 4, tt34, and tt58 for example.
    So my table 2 could be just trouble tickets that have a 1 to many.
    My table 3 could be just material ordering info.
    Table 4 could be just scheduling info.
    Etc...

  8. #8
    AndreT is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2011
    Posts
    25
    Yes, you've got it. What you described seems to be a parent-child relationship. My function is a sub-entity relationship - an extension of the imported table.

    Table 2, 3, 4 starts out empty with all the fields established.

    The main form will display the imported table.

    A subform on the main form will display a list of table 2 trouble tickets linked to the main form. As you enter the fields, they will be created or updated.

    Another subform will display a list of table 3 info also linked to the main form, or to the trouble ticket table, depending on the ER structure.

    You can even export your scheduling info out to excel and import back to MS Project to display under MS Project, that's what I do. I need this for one time deal a few times. Can probably interface directly to MS Project.

    How you display and link them up is up to your choice, you can use one form for each sub-table, tabbed form, or use key.column(n) to reference. VBA provides all kinds of alternatives to you.

    Just a heads up, here are some gotchas I run into -
    1) my original system allows single and double quote as data in the fields, and exports csv file only. That messes up the excel conversion,
    2) the original system allows memo fields, excel truncates cell size over 255 characters, haven't figure out the solution yet,
    3) one of the export has over 255 columns, had to split to 2 exports, combine them after import

    Have fun.

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

Similar Threads

  1. Importing from Excel
    By itm in forum Import/Export Data
    Replies: 1
    Last Post: 07-20-2011, 07:12 PM
  2. Replies: 2
    Last Post: 04-22-2010, 10:40 AM
  3. Importing Data From Excel
    By king_bowzow in forum Import/Export Data
    Replies: 1
    Last Post: 09-11-2009, 02:26 AM
  4. Changing data in excel from access
    By ricardo9211 in forum Import/Export Data
    Replies: 1
    Last Post: 08-26-2009, 01:46 AM
  5. iMPORTING DATA FROM EXCEL TO ACCESS
    By Rameez in forum Import/Export Data
    Replies: 7
    Last Post: 06-16-2009, 12:05 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
  •  
Other Forums: Microsoft Office Forums