Creating a table of contents/index: maketable query, need macro to create a field

[crobaseball]

I have a situation where I have a report which I will generate to print our companies pricebook. I need an index/table of contents for customers to go to the page where a product is to look up prices. The problem is that we add and subtract products on a consistent basis. So a new product will get added, but of course, to run alphabetically, there's no way to insert it, then renumber the pages for it to be easily found.



So here's my solution: to use a maketable query, which sorts all of the products alphabetically. Each time it is run, it will delete the previous table. I then need to have an autonumber field created to create my "page numbers". Yes, each time the pricebook is run, products can and will oftentimes get new page numbers.

Here's the catch: I'm here to design the database, then someone else will be using it moving fwd. So my thought is to have a macro run which creates the autonumber field to the table. This will then be the basis for the form, which will in turn utilize the "page number"/autonumber field to both serve as page numbers in the report. And of course, the table which is created each time will be the "table of contents".

Thoughts and some help? Do I need to write VB code?

[June7]

Does this mean each page has only one product?

Not sure can rely on autonumber to generate in the order of the sorted records. Maybe if there is an index set on the product description.

Alternative is permanent table but delete records before each run of the process. However, autonumber won't start over from 1 unless database is Compacted and Repaired.

Use macro or VBA.

There are macro actions:
Delete Object
OpenQuery
RunSQL
RunMenuCommand can execute commands:
DeleteTableColumn
InsertTableColumn

[crobaseball]

Does this mean each page has only one product?

Not sure can rely on autonumber to generate in the order of the sorted records. Maybe if there is an index set on the product description.

Alternative is permanent table but delete records before each run of the process. However, autonumber won't start over from 1 unless database is Compacted and Repaired.

Use macro or VBA.

There are macro actions:
Delete Object
OpenQuery
RunSQL
RunMenuCommand can execute commands:
DeleteTableColumn
InsertTableColumn

So start over:
1.) Each time a new product is created, before the pricebook is printed to a pdf, a MakeTable query is created, making a table called "Index". This table OVERWRITES and replaces the former table "Index". The table is created, sorting by items, so that they are in order. To clarify, when I refer to "items", they're actually a group of items. There is a style of product, which I'm referring to here as an "item", but actually is a full page with color and size options. But for these purposes, yes, each "item" has its own page.

2.) AFTER the table is created anew, which is created with everything sorted alphabetically, I then create the autonumber field, called page number. This will then line all the content and pages in order.

3.) The report, which already is in place, will generate off this table. More specifically, rather than have the fields "match" the report, I'll have a query which calls all records into the report, so that as things are added and deleted, the report will reflect this.

4.) Finally, I'll have another report which actually prints the pages of the book. This will also be built off a query (or multiple queries to be specific), which will include the "page number" field associated with each "item".

While there are enough steps for this to sound complicated, I think it's actually fairly straight forward . . . but I'm sure there's something in here which will make it more challenging.

[June7]

I prefer temp table and deleting records over deleting and making table. The latter is a db design change and design changes can cause db size to grow, mandating more frequent Compact & Repair. But then deleting and adding fields is also a design change so I would use VBA to add the sequential number.

Sometimes things don't fully delete or new object doesn't show in the navigation pane - until Compact & Repair fixes (hopefully). And that's even with manual changes, seems to me doing changes programmatically presents greater risk of corruption.

[crobaseball]

Mmmm. Very good points. I didn't know temp tables existed. I'll look them up.

Q: how does this affect using those page numbers in my report?

[June7]

A temp table is just a permanent table that holds records only for the duration of a process. If database is split, temp table should be in frontend so each user has independent table and won't interfere with other users.