MHadden
Programmer
- May 13, 2001
- 105
I have spent 6 days on this, I have searched every website I could find (including Tek-Tips), the help files & spoken to a few “Gurus” I know.
I am trying to help the Assistant Purchasing Manager at work.
Every morning, she must run 3 reports from the main computer. These reports are in CSV format. She then imports these into 3 different spreadsheets, then visually compares the daily purchase orders to the inventories of the other branches. Our goal is that if a location has an overstock of an item that someone else needs, we have them transfer the item, rather than purchasing more. There are 15 locations with over 500 products each, not including the serialized inventory, so she has to look through each spreadsheet to compare & you can imagine the amount of time, not to mention the possibility of human error.
I don’t want to be too long-winded, but want to provide as much information as possible, so I will try to explain what I’ve done so far.
Report 1 provides a list of all open purchase orders.
Report 2 provides a list of inventory in the other 15 branches.
Report 3 provides a list of inventory that is “serialized” in the other branches.
Rather than having to import everything, I linked the text files to access, then created queries from each, so that I could further manipulate the data. Report 3 has many duplicate lines, that are delineated by asset numbers, so I created a crosstab query that counts the occurrences of each item number / model number, to obtain a total amount.
I made a form that is based on the purchase orders, with 2 subreports (reports 1 & 2). They linked fine using the parent / child properties. I then made a report the same way, also linked fine. My problem is that it takes 20 minutes, or so for the report to build & usually lock up the computer when trying to print it. I increased the ram in my computer & also tried it on another computer, but I continue to get the same results. I tried to make a query containing all three, but cannot seem to get it to work. I even made a union query, but cannot get it to separate into the proper fields.
If there is a better way, I would appreciate any suggestions. I tried doing something like this in Excel, but I don’t have a clue how to accomplish it. I have worked with Access for quite a while, but know little about Excel.
I thank you in advance for any help you can offer.
- Michael
MichaelHadden@yahoo.com
If you give someone a fish, you have given them a meal. If you teach someone to fish, you have given them MANY meals!
