reserving a certain # of rows ?

[AidanCambel]

Forgive me, but I have a questino that is probably so simple - but I can't seem to figure it out. I am going to go into as much detail as possible.

I have recently been handed the task of generating reports from the backend of our software systems using Access2000. Before, all reports were done from the front end and dumping into Excel. We have one Excel report that is very complex and has a lot of information contained in its many worksheets. We have 16 software products that we sell and service, and each one is tracked independantly. this report tells the "open order" status for each product.

Now, since each one is tracked seperately, there is basically a seperate sheet for each product, a totals sheet, a "buffer" sheet, and a divider sheet. What they have done in the past is to take the data into excel, sort my product code, and copy into the "buffer" sheet. Then on the "divider" sheet, it breaks into a certain number of rows per product, and each product is then c&p into its respective area on the divider sheet, which then dumps it into respective worksheets. (There is a TON of info per row, all very important)

ANYWAY.. I have developed a query from the back end that makes it about 10 times faster to get the data intothe "buffer" portion, but if I could design the query differently I could take it straight into the divider sheet.

*** Finally, the question - IS there a way to say --"ok, the first 200 rows I want only to be product ABC, 201-300 product DEF, 301-400 is product GHI",etc.?

If I can do this, then I can make the whole sheet work in access. If it throws blank rows in, thats fine. Just as long as it clearly defines the range for each product, and does not allow the products to "bleed over".

thanks.

 

[HenryAnthony]

You wrote,

"I have developed a query from the back end that makes it about 10 times faster"

You are moving in the right direction but you are asking the wrong question. In that you have a already used Access to solve a problem difficult in Excel you need to ask how do I do the rest of the data management in Access and just dump those spread sheets.

*** Finally, the question - IS there a way to say --"ok, the first 200 rows I want only to be product ABC, 201-300 product DEF, 301-400 is product GHI",etc.?

What is the backend database and where does the data come from?

Best regards,

Henr¥


I believe not. Access allows null values in fields but not in records. You cannot insert a row in a table like in Excel.

 

[AidanCambel]

Henry,

The frontend Program is Softrax, but I am accessing the tables via ODBC. I understand that the whole thing needs to be ran from Access, but we have no one here proficient in Access, although there are several really good with excel. I am to be attending classes soon to get certified in Access, but that doesn't help right this minute.

We have a report we run weekly that tells open sales order per product. there are 17 products. We end up with about 2,000 rows every week. We can generate the report from the application, dump it into excel - but then it involves sorting by product code, and then taking and cutting and pasting those into another sheet that has the products broken out into a range. The reason we do this is because there is a seperate sheet for each product, and each product lead is responsible for noting a "close date" for each open order, then the spreadsheet does a bunch of calculations to forcast revenues for the upcoming months based on info they provided. There are some really complex formulas and vlookups and links from page to page that I don't know how to duplicate in access *yet*.

My first goal, to get us by until I can do the rest, was to find a way to generate the report in Access with it already broken into "block segments", so I can export the query straight into the sheet that startd the calculations, and eliminate the need for 2 hours a week of cutting and pasting. I already have the relationships in place and a query built to give me the information, but I still end up cutting and pasting product A into the range on the excel sheet designated for Product A, then Product B, etc.

Eventually the whole thing will run from access, but this is to get us by until September, which is when I finish my Access classes.

Thanks for your help.