Results 1 to 12 of 12
  1. #1
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6

    Automatic transfer data from Access to Excel

    Hello Everyone



    I am working on an attendance file. I have created one MS access form and one excel sheet. On MS Access sheet I have Student ID, Date and Value. ON excel sheet I have Course, Student ID, NSN No and date.
    I want data to be automatically appear on excel sheet from ms access form and after updating it or after transferring data i want these fields again empty and ready for next entry.

    For example, If I type value .5 in ms access under 01-Jan-2014 for Student ID 1200001, value .5 should automatically appear in cell in ms excel sheet infront of same id (1200001) and under same date (01-jan-2014).

    Please let me know if it is possible. If yes then please let me know the code to make it possible.

    I shall be very thankful

    Regards

    Sarab
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I am confused. You want data from the excel sheet to come into Access, or the other way?
    You want the data from xl to input into access then clear itself? Usu. you bring in data to access then keep it forever. (history)

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Also confused. Why involve Excel?

    Pushing this data from Access to Excel would be more difficult than entering the data on Excel and Access just links to the spreadsheet - if the spreadsheet has simple structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    I am confused. You want data from the excel sheet to come into Access, or the other way?
    You want the data from xl to input into access then clear itself? Usu. you bring in data to access then keep it forever. (history)
    Hello,

    Sorry for confusion. Actually I already have one excel file with the attendance data for 1000 students. It just time consuming to enter the values (.5, .4, A etc.. as per student attended hours). I have to enter this data in front of student id and under the same date when student actually had attended the class. School is in process of getting student management system, but it will take like 3 to 4 months or more as they have to modify as per our school requirement.

    So, date by date I have to enter data in attendance sheet to calculate over all % of student(for one 3 months, 6 months and for one year. Depending upon student requirement). I want to make this process bit easier. So I wanted to have 3 fields in access (student ID, Date, Value), which on typing compare the student ID and date with excel file and fill in the date automatically in excel and then again available for next entry.


    Please let me know if this is possible. If you have any other idea or if you know any other way to accomplish it, please let me know.
    I am attaching screen shot of attendance sheet that I am using here in school.


    Regards

    Sarabjit Singh
    Attached Thumbnails Attached Thumbnails Sample Picture.jpg  

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Still not clear what you want.

    You want to input studentID, date, value on Access form and send that data to an Excel spreadsheet? How would that be simpler than just opening the Excel and entering those data directly on spreadsheet?

    It is possible to build forms in Excel and VBA code to write data from those forms onto the spreadsheet.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6
    Quote Originally Posted by June7 View Post
    Still not clear what you want.

    You want to input studentID, date, value on Access form and send that data to an Excel spreadsheet? How would that be simpler than just opening the Excel and entering those data directly on spreadsheet?

    It is possible to build forms in Excel and VBA code to write data from those forms onto the spreadsheet.


    Hello,
    Thanks for reply. Ok, here is the thing. We have daily attendance sheets which includes two days attendance and is considered as weekly attendance, and then we have one over all attendance sheet, on which all the weeks or papers are recorded for each student under the date student has attended the classes. These physical attendance sheets are filled by tutors on hourly basis. As per rules, student has to spent 20 hours in a week. Which is 2 days classes, Suppose Monday and Tuesday. So our daily sheets are recording 10 hours a day for each student. So 2 days sheet makes one week attendance. This weekly attendance hours then converted in to the value of .5 or .4 etc (.5 for Am and .5 for pm. So am pm attendance makes student present for all day. if student attended 9 hours then in am .5 and pm .4 (if student skipped one hour in pm)) as per student presence in class. This value of .5 or .4 is then transferred to excel sheet (over all attendance sheet) in front of student id and under same date as in physical sheet when student attended the class.



    At this point it is really hard to encode attended on to over all attendance sheet as we have to scroll to next date and to switch over to other sheets to calculate and to look for values.
    So to make it easy I wanted to have some kind of form that can automatically transfer the values in to the main file that is calculating over all attendance of student. (yearly attendance sheet).

    Please let me know if you are still not clear and also let me know if you have better idea.



    Regards

    Sarabjit Singh

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The output on the Excel sheet is not a normalized structure. If you don't want to retain data in normalized relational database structure and all you want Access for is a 'friendly' user interface for data entry and pass the data onto spreadsheet - fine, it is possible. It is also possible to build form objects in Excel that could act as a 'friendly' user interface for entry onto sheet. It is probably complicated code but pushing the data from Access onto Excel sheet is also complicated. Any time two applications must be made to interact, just compounds the difficulties even more.

    This process starts complicated because you want to add data to existing sheet without destroying data already on it. Then in addition want to pass the data from Access to Excel, not simple. The more friendly, the more code.

    I can't just pull code out of the air for this situation. I don't do much coding in Excel - this is an Access forum after all - and not much that involves interaction between Excel and Access - although it is a fairly common topic here and I have contributed to discussions - look at some if you haven't yet seen code involved in Access/Excel interoperations.

    What I am getting at is this project would be a significant dedication of time and I am not interest in tackling it. I am willing to review code you develop and encounter specific issues with. So unless someone else steps up to take this on, you need to learn the programming this would require and build it or find a professional programmer to build for you.

    As I said, possible but is it worth the time and effort?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6
    Hello Sir,

    Sorry for late reply. I understand what you mean. I am short of resources. But I am following your advice. I am trying to do something but I am stuck at one point. I am trying to add two fields and trying to get results in calculated field. When ever I put expression in it, in field it shows #type! instead of result. I am using access 2013. I am using this expression [Attendance AM]+[Attendance PM]. In attendance if I put 0.5 in am .5 in pm, it shows #type!, if I put 1 in am and 1 in pm it shows 11 instead of 2.

    Please let me know where I am doing wrong.

    Regards
    Sarabjit Singh



    Quote Originally Posted by June7 View Post
    The output on the Excel sheet is not a normalized structure. If you don't want to retain data in normalized relational database structure and all you want Access for is a 'friendly' user interface for data entry and pass the data onto spreadsheet - fine, it is possible. It is also possible to build form objects in Excel that could act as a 'friendly' user interface for entry onto sheet. It is probably complicated code but pushing the data from Access onto Excel sheet is also complicated. Any time two applications must be made to interact, just compounds the difficulties even more.

    This process starts complicated because you want to add data to existing sheet without destroying data already on it. Then in addition want to pass the data from Access to Excel, not simple. The more friendly, the more code.

    I can't just pull code out of the air for this situation. I don't do much coding in Excel - this is an Access forum after all - and not much that involves interaction between Excel and Access - although it is a fairly common topic here and I have contributed to discussions - look at some if you haven't yet seen code involved in Access/Excel interoperations.

    What I am getting at is this project would be a significant dedication of time and I am not interest in tackling it. I am willing to review code you develop and encounter specific issues with. So unless someone else steps up to take this on, you need to learn the programming this would require and build it or find a professional programmer to build for you.

    As I said, possible but is it worth the time and effort?
    Attached Thumbnails Attached Thumbnails attendance .jpg  

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The Attendance fields are text type, not number. The + character is valid concatenation operator left over from ancient BASIC language. Because the fields are text, Access is concatenating not summing. However, for some reason Field1 expects a number type and therefore the concatenation of .5 + .5 (.5.5) causes the #Type! error. The other results are valid numbers and don't cause error.

    Change the Attendance fields to number (Double) type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6
    Hi,

    Thanks for your reply. I tried to do as you instructed. Thing is Attendance am and pm fields are Lookup fields. If I turning them to Numbers it is adding but not accepting values in points (0.5 etc).

    Let me know plz

    Regards

    Sarabjit Singh

  11. #11
    Sarabjit is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    6
    Hello,

    Thanks for your help. Its working now. Now my only problem is, If there is a class for 10 hours, then .5 and .5 is good. but what if student have 8 hour classes in some month. how can I calculate that. Also some students have half day classes on Wednesday (like for 4 hours only). How I can calculate that. Can you give me some ideas on that please.

    Regards

    Sarabjit Singh

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why are you use lookups for entering a number?

    I NEVER use lookups in table http://access.mvps.org/access/lookupfields.htm

    Why would you enter .5 for 10 hours?

    What is this data - hours, days? Pick one and be consistent. If you want the input as days then what do you consider a day - 8 hr workday, 24 hr day/night? 4 hours is .5 of 8-hr workday but .167 of 24-hr day. 10 hrs is 1.25 of 8-hr day and .4167 of 24 hrs.

    How precise do you need this to be? Could consider any class <=4 hours as .5 day and any class >4 hours as 1 day.

    How do you know the length of classes? Do you have a table of classes with field for length in hours or days?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-24-2013, 08:33 AM
  2. Replies: 5
    Last Post: 06-12-2011, 03:58 PM
  3. How to transfer data from excel to Access?
    By mit in forum Import/Export Data
    Replies: 4
    Last Post: 06-11-2011, 05:39 PM
  4. Transfer data to excel
    By John Southern in forum Import/Export Data
    Replies: 5
    Last Post: 06-11-2010, 09:26 AM

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