Data analysis issue

[cafirlanda]

I work for a train maintenance company and to keep track of the defects we use access. Our data is stored in tables (eg unit1) and each defect is assigned a fault code (eg TRD.99). These codes are then used to report to our customer where errors our occuring on the trains.

There are 17 categories of code defined by the 3 letters at the start and the specific problem is stated by the digits. I need a method of tabulating the codes by unit number and a total given in another column. To do this I need a code to count the number of times each three letter code appears in the column of each units table and place the value in the corresponding column in the overview table. I then need a code to add up the total faults for each unit like the sum function in an excel spreadsheet. The final table should look something like this

Unit No BOG TRD
3001 0 21
3002 0 17
3003 1 17
3004 0 4
3005 0 5
3006 1 18
3007 0 3
3008 1 7
3009 0 4
3011 0 0
3012 0 2
TOTAL 3 98

Any help will be greatly appreciated

 

[Birdy]

Looking at this.....

It looks like you need a group by query.

To show the data in the report, link the report to the query and then use the report functionality to show the totals.

 

[cafirlanda]

Re. Solution

Do I do this for the overall table. If so how do I get the details from the seperate tables into one table to analyse them?

 

[Birdy]

How many tables is your original query looking at?

 

[cafirlanda]

I currently have table for each individual unit holding the information. I want to put all the information I need for the analysis into one table automatically. As well as this I want to have an automatic process for taking the three letters at the start of the fault codes and giving the total for each type of fault code in a related column in the new table.

What do you think?

 

[Birdy]

Ok.

To start, run queries to put the information into 1 table.

In this part, you could break down the fault code into 2 fields using a Left function.

Then run the query on this 1 table to get the totals using Sum on the BOG and TRD fields.

Then use this query to run the report and in the Report Footer, have your totals.

Alternatively, email me the DB (zipped up) & I will have a look.

 

[cafirlanda]

RE analysis issue

i will email it to you if you give me your email address. There are lots of tables etc. but the important ones are unidad1 etc. A lot of it is in spanish as my employers are a spanish company. There are queries for each unit too. The important ones are outstanding defects 3001 etc. The units are 3000 series so 3001 is unit 1.

 

[Birdy]

Check your Private Messages.

Email address is in there.

 

[Birdy]

Did that work?

I sent the db back to you this morning. Did it work?