Old man need help with Access!

[jamforbreakfast]

Hi - Can anyone help me??

I have produced an Excel based form, for my company, to allow members of staff and teams to do various quality checks as they walk round the factory floor. There are 114 questions in total. There is a data capture sheet in each of these workbooks (hidden) so that when I receive them back from each team, I can see how well they have done against the questions set out - there is a number of positive responses received for each question and a total responses received, so I can work out the compliance rate for each.

This is fine, but I need to now put it into a database to enable me to produce reports on a regular (monthly) basis but I do not know how to do this! Can anyone please help me / illustrate to me how to do this??

I have attached the data capture sheet.

Regards

 

[Trevor G]

James welcome to the forum, from your spreadsheet, you can copy the whole table then create a database and paste the copy into the table section this will create a table based on the data and also give you the headings. When you need to add more data then you would look to use a MACRO and select the TransferSpreadsheet option this will then copy the data into the same table. Then you would look to set up the queries for your reports.

I hope this helps?

 

[jamforbreakfast]

Hi - I now have all the data in a table in Access. Just about got there!

Now, how can I produce a query which will allow me to compare all of the departments and teams I have now input into Access? Obviously all of the questions are the same for each department / team, so I need to somehow produce a query to be able to show this...can anyone help?

Thanks

 

[Big Pat]

Hi,

Create a query using the "wizard" to begin with. It will ask you which table you want and then which fields. You will probably want to have "detailed" view rather than "summary" at this stage.

When the query is created, consider that a "first draft" and go into design view (button with a pencil, ruler and set-square). That will show you the table you have selected withthe fileds in the grid below.

To limit the results of the query add some criteria on the appropriate row at the bottom. For instance you may wish to limit a field named Dept to the value "Sales", or limit the Question Number field to <21, for instance if you wanted to show only the first 20 questions in this particular query.

Click the Totals button (greek letter sigma) and experiment with grouping some fields and summing or averaging others.

The key thing I realised when learning Access (and I learned so much right here!) is to get your query right first. When your query contais the right information, then it's pretty easy to put it into a report. The report wizard will do a lot for you and then you can adjust.

Good luck!