+ Reply to Thread
Results 1 to 13 of 13

Fetch data from Access table in Excelsheet in specific format

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Fetch data from Access table in Excelsheet in specific format

    Hi All

    I have designed project in Excel as a frontend and Access as a backend. In Access database there are following tables:

    1. tblstaff (This table stores all staff names)
    2. tblprocess (This table stores all process names)
    2. tblAccredit ( This table stores staffname and process name where the staff has been accredited to)
    3. tbltraining (This table stores staffname and process name where the staff has been put into training)

    Now I want to design MI report in Excel that will fetch data from Access table and store in the excelsheet. The format should be like this:

    All staff Names from tblstaff should display in the first column, All process names from tblprocess should be displayed in the first row. And then we need to check the staff names in each table tblAccredit,tbltraining for each process. T is assigned if the staff name has been put into training and A is assigned if the staff is accredited to a process.
    Process
    Staff Name Cheques Banking Complaint handling
    Sarah T A
    Claire T
    Smith A T

    I hope anyone can help me out in this.

    Thanks

  2. #2
    Registered User
    Join Date
    06-11-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Fetch data from Access table in Excelsheet in specific format

    Hi ,


    1. Goto Data tab in excel
    2. select the from access.
    3.locate the access file from which u want that sheet to be populated.
    4.select table from the list of the tables.
    5.select the destination cell.

    for selective data proceed further steps.
    6.goto properties .
    7.goto definition Tab.
    8.In command text pass the string containing the sql Query.


    please rate if u are satisfied with this post by selecting the star from below.

  3. #3
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Thanks for your reply. But I want to do it automatically like when the user press button in Excel userform then it should generate a report that shows all the staff who have been put into training for each process and wohave been accredited in each process.

  4. #4
    Registered User
    Join Date
    06-11-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Fetch data from Access table in Excelsheet in specific format

    To do this there should be some sample data base.
    it is better if u attach sample database file.

  5. #5
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Hi

    Sorry I won't be able to send the sample Access database bcos of security restrictions in my company but I can tell you the structure of the tables as attached in the excel workbook in tabs tblstaff,tblprocess,tblmain. The sheet1 tabl shows how I want to display a report.

    1. tblstaff (It stores all staff names)
    2. tblprocess(It stores all process names)
    3. tblmain (it stores staffname,processname,Date,Status(it stores either accredited or training)

    I hope it helps.

    Thanks a lot.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Can anybody please help me to solve my problem? Its really very urgent to resolve.

    Thanks

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Fetch data from Access table in Excelsheet in specific format

    You need a consultant. That's not an "I'm stuck with part of my problem" question, that's just "Can someone build me an MI system?".
    Remember what the dormouse said
    Feed your head

  8. #8
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Thanks romperstomper for your reply. I have designed all the forms in Excel and they are working perfectly and storing data in Access database but now my manager wants me to display a report like sheet1 in attached excel workbook. But I am not sure how to start with it as I never designed this kind of report in the past.

    I hope anyone can help me in this.

  9. #9
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Can anybody please advise me in this?

    Thanks for any help in advance!

  10. #10
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Its really strange that nobody has answer of my question. Please can anybody help me in this?

  11. #11
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Fetch data from Access table in Excelsheet in specific format

    I also think that you need a consultant. not only for the reports, but also for the DB design.
    If you are pleased with a member's answer then use the Star icon to rate it.

  12. #12
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    As I have to solve this problem so I can even change the DB design if its wrong. Can you help me how to change DB design?

    May be after that it will be easy to generate a report I want.

    Thanks

  13. #13
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Fetch data from Access table in Excelsheet in specific format

    Hi All

    If you say I need to redesign the database then I am ready to do it but I need someone's help as the only purpose of this
    will be generating a report in a format I mentioned earlier.

    Can anyone please help me in this?

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1