xzquala
New member
- Local time
- Today, 11:50
- Joined
- Jul 12, 2002
- Messages
- 5
heya all.
First I want to apologize if I seem long-winded, but I could really use some help.
Background- the only access I have to the raw data on the server is through ad-hocs, the only way I can save the ad-hoc is text with headers. Then I must copy it to a floppy, and import it into excel or access on another lan.
The data I am trying to analyze starts off with a list of stock numbers that were locally ordered but not locally available during the past month.
These are depot level repairable aircraft parts. sometimes these parts are a part of a "family", and within the family there are alternate parts that could be issued when the original part was not in stock. They are grouped by the system in a family group code.
most of the time there is only one stock number within the family.
My task in the whole arrangement is to look at all of the maintenance data for these stock numbers, or niins (national identification numbers), count all of the successful repairs during the past year, add all of the hours spent by the maintenance depot fixing it, and also count all of the non successful (hey, its junk now, lets carcass it for spare parts) repairs.
Then I need to perform a small calculation to see if the family or niin within the family warrants a request for an increase in fixed allowance, which is only given outby the inventory control point (icp) for aircraft repairables. The request must contain at least 5 data elements specific to the niin, just some info from the system (about what type who pays for it, what type of aircraft uses it....).
It must also contain the count of repairs, sum of repair man-hours, and the count of non repairs. We must also report our two different allowances for the niin.
In addition to that, if it is in a family, all of these values have to be summed for the whole family as well.
As of two months ago, I was doing this by hand, looking through archaic dot matrix reports on green and white lined paper. a good 60 pounds of paper was given to me every week for this purpose. I knew there was a better way, and I finally proved that I should be given ad-hoc access. so now I can automate it.
I have already figured out how to run the ad hocs quite well, I can pull all of my pertinent data, but it must be done in seperate tables, for each table on the server I need a corresponding ad hoc. for some reason there is no table linking in the ad hoc software.
before I was making pivot tables in excel that counted all of my data, but I still had to make a good dozen pivot tables, and then I still had to go through and manually count and arrange my data for the request off a dozen spreadsheets.
this past week I have spent trying to do the same things in access. I don't have a problem importing data, or even making a simple query. but what I am having a lot of difficulty in is generating the report to do the summarys both at the grouping level and at the sub groups.
now you might think, "hey, shouldn't stock levels me maintained automatically?" , and you would be right, they "should" be.
basically, I have tried just about everything I can think of in terms of database building, but I am new to access. I even tried to import my excel pivot tables.
one of the problems I am having is whenever I try to insert the count expression into the header or footer, I get nothing, but if I count in the detail section it counts every record, regardless if the record has anything to do with the niin or family group code.
I am also being told that now instead of just counts if there is less than 50 records, I must actually list the purcase orders and the job orders as well. I am not trying to automatically generate my requests, just trying to put all of the data necessary in a nice neat orderly fashion so someone else can look at it and say, yes, ask for more of that.
I greatly appreciate anyones help in this matter.
Respectfully,
XzQuala
EDIT: oh, that is lovely, now how did I manage to post this in tables? anyone with the ability, please move this post to general or reports. thank you.
First I want to apologize if I seem long-winded, but I could really use some help.
Background- the only access I have to the raw data on the server is through ad-hocs, the only way I can save the ad-hoc is text with headers. Then I must copy it to a floppy, and import it into excel or access on another lan.
The data I am trying to analyze starts off with a list of stock numbers that were locally ordered but not locally available during the past month.
These are depot level repairable aircraft parts. sometimes these parts are a part of a "family", and within the family there are alternate parts that could be issued when the original part was not in stock. They are grouped by the system in a family group code.
most of the time there is only one stock number within the family.
My task in the whole arrangement is to look at all of the maintenance data for these stock numbers, or niins (national identification numbers), count all of the successful repairs during the past year, add all of the hours spent by the maintenance depot fixing it, and also count all of the non successful (hey, its junk now, lets carcass it for spare parts) repairs.
Then I need to perform a small calculation to see if the family or niin within the family warrants a request for an increase in fixed allowance, which is only given outby the inventory control point (icp) for aircraft repairables. The request must contain at least 5 data elements specific to the niin, just some info from the system (about what type who pays for it, what type of aircraft uses it....).
It must also contain the count of repairs, sum of repair man-hours, and the count of non repairs. We must also report our two different allowances for the niin.
In addition to that, if it is in a family, all of these values have to be summed for the whole family as well.
As of two months ago, I was doing this by hand, looking through archaic dot matrix reports on green and white lined paper. a good 60 pounds of paper was given to me every week for this purpose. I knew there was a better way, and I finally proved that I should be given ad-hoc access. so now I can automate it.
I have already figured out how to run the ad hocs quite well, I can pull all of my pertinent data, but it must be done in seperate tables, for each table on the server I need a corresponding ad hoc. for some reason there is no table linking in the ad hoc software.
before I was making pivot tables in excel that counted all of my data, but I still had to make a good dozen pivot tables, and then I still had to go through and manually count and arrange my data for the request off a dozen spreadsheets.
this past week I have spent trying to do the same things in access. I don't have a problem importing data, or even making a simple query. but what I am having a lot of difficulty in is generating the report to do the summarys both at the grouping level and at the sub groups.
now you might think, "hey, shouldn't stock levels me maintained automatically?" , and you would be right, they "should" be.
basically, I have tried just about everything I can think of in terms of database building, but I am new to access. I even tried to import my excel pivot tables.
one of the problems I am having is whenever I try to insert the count expression into the header or footer, I get nothing, but if I count in the detail section it counts every record, regardless if the record has anything to do with the niin or family group code.
I am also being told that now instead of just counts if there is less than 50 records, I must actually list the purcase orders and the job orders as well. I am not trying to automatically generate my requests, just trying to put all of the data necessary in a nice neat orderly fashion so someone else can look at it and say, yes, ask for more of that.
I greatly appreciate anyones help in this matter.
Respectfully,
XzQuala
EDIT: oh, that is lovely, now how did I manage to post this in tables? anyone with the ability, please move this post to general or reports. thank you.
Last edited:
