none
Table Structure RRS feed

  • Question

  • I could not find better way to represent Excel sheets in Access tables.

    I'm pasting link to Excel file, since i can't attach a file with post, sheet ROC in attached file i need to represent in Access table. I've tried to explain a bit in Description column in the end, so that it could make little sense to you guys.
    I need to represent other sheets from Excel file to Access as well, but i think I'll be able to start understanding others once i understand how to input this ROC sheet in Access table.

    link to Excel file is https://drive.google.com/file/d/0B40aEpxbK6NfeURCOGpSTmMwRkk/view?usp=sharing

    Thanks.

    Friday, August 28, 2015 4:36 AM

Answers

  • Hi KhurramKZ,

    >> I need to represent other sheets from Excel file to Access as well, but i think I'll be able to start understanding others once i understand how to input this ROC sheet in Access table.

    Do you mean that you want to show the same table in Access table like the image below? If so, I am afraid that it is impossible, and access do not support this function. When you import this excel file into access, error will happen, and it will assign field name to the column headers.

    For a workaround, I suggest you import the data rows without column headers in the excel to access tables, and then design access reports like present in excel file with the data source. With a report, you could design it like what you want.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by KhurramKZ Friday, August 25, 2017 10:41 AM
    • Unmarked as answer by KhurramKZ Friday, August 25, 2017 10:41 AM
    • Marked as answer by KhurramKZ Friday, August 25, 2017 10:42 AM
    Monday, August 31, 2015 2:47 AM

All replies

  • Have you just tried linking it?

    Build a little, test a little

    Friday, August 28, 2015 4:12 PM
  • Would you need to update the data in Access?
    Friday, August 28, 2015 4:19 PM
  • Linking means, i divided the rows and columns in Excel sheet in Access tables and then link them??

    DB guy, most of the entries on ROC page should be manually entered. But these pages are linked with 496G and 496D as well, and value in these pages will change based on the value on ROC page.

    Saturday, August 29, 2015 12:35 PM
  • No, by "linking" he means that you link Access directly to the data in the external Excel spreadsheet, rather than importing the data into a new table.  This lets you work directly with the Excel data in Access rather than with a snapshot of the data copied from the spreadsheet. 
    • Edited by Gary Voth Sunday, August 30, 2015 1:39 AM
    Sunday, August 30, 2015 1:39 AM
  • Thanks for explanation. No I imported to data in Access and everything will be done in Access, once the database is complete.
    Sunday, August 30, 2015 3:50 AM
  • Hi KhurramKZ,

    >> I need to represent other sheets from Excel file to Access as well, but i think I'll be able to start understanding others once i understand how to input this ROC sheet in Access table.

    Do you mean that you want to show the same table in Access table like the image below? If so, I am afraid that it is impossible, and access do not support this function. When you import this excel file into access, error will happen, and it will assign field name to the column headers.

    For a workaround, I suggest you import the data rows without column headers in the excel to access tables, and then design access reports like present in excel file with the data source. With a report, you could design it like what you want.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by KhurramKZ Friday, August 25, 2017 10:41 AM
    • Unmarked as answer by KhurramKZ Friday, August 25, 2017 10:41 AM
    • Marked as answer by KhurramKZ Friday, August 25, 2017 10:42 AM
    Monday, August 31, 2015 2:47 AM
  • Thanks, but i was talking about ROC sheet in Excel attachment earlier, or at least 496D or 496G sheets.

    This summary sheet used above is the simplest of all sheets in Excel attachment, without any row headings, and does not help me understand what i'm trying to understand in this thread.

    Tuesday, September 1, 2015 8:47 AM
  • Hi KhurramKZ,

    >> i need to represent in Access table.
    If you want to achieve the same function with 496D in the excel file, it is impossible. If you want to show all the data in Access table, I think you could import the data into access table, and the result as below:

    If you want to achieve the sum function with group, I think you will need to import the data without the sum value, and then you could use query to sum the value or with a report to sum the value. The link below shows a grouped or summary report.

    # Create a grouped or summary report
    https://support.office.com/en-us/article/Create-a-grouped-or-summary-report-f23301a1-3e0a-4243-9002-4a23ac0fdbf3

    But whatever you used in Access, you will not enable to change the different sum result view like in excel with Access built-in function.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 2, 2015 6:02 AM
  • Ya, right i could not do that so far.

    How do I export data from Access to Excel and then use Excel to generate the report please?

    Wednesday, September 2, 2015 3:12 PM
  • Hi KhurramKZ,

    >> How do I export data from Access to Excel and then use Excel to generate the report please?

    For export data from Access to excel, you could use export function, the link below shows more details.
    # Export data to Excel
    https://support.office.com/en-us/article/Export-data-to-Excel-64e974e6-ae43-4301-a53e-20463655b1a9

    For generating the report, you could use outline(group) data in a worksheet.
    # Outline (group) data in a worksheet
    https://support.office.com/en-us/article/Outline-group-data-in-a-worksheet-3b03f3a0-d000-4f7c-9181-be4355959d90?ui=zh-CN&rs=en-US&ad=CN

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 3, 2015 3:17 AM