Fetch data from Access table in Excelsheet in specific format

[aman1234]

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

[Md Aejaz]

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.

[aman1234]

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.

[Md Aejaz]

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

[aman1234]

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.

[aman1234]

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

Thanks

[romperstomper]

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?".

[aman1234]

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.

[aman1234]

Can anybody please advise me in this?

Thanks for any help in advance!

[aman1234]

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

[buran]

I also think that you need a consultant. not only for the reports, but also for the DB design.

[aman1234]

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

[aman1234]

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