Add a ranking to a sorted query for comparison

[AnndieMac]

I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been able to create these summaries easily enough using several sorted queries and a form to select the specific store, or to pull up the regional or national summaries.

The problem is that they want to be able to have one file for each store which lists the top 30 item losses with additional fields showing where that same item ranked at the regional and national level.

One alternative I came up with was to create a new table for each of the higher level summaries (national and regional) and either use Autonumber or code to add ranking numbers. However, this is not ideal because this would require approximately 140 tables to be created to cover all of the regions.

I also tried to write a function which would be called when the user selects the store to summarize which would temporarily add the ranking to the sorted queries. However, I am getting an error that the query is not updateable. I am not sure if this is a limitation in Access, or if it is something I am doing wrong. (In a fit of frustration I deleted the code, so I can't paste it here.)

So, first question: Is it even possible to update a field for each record on a sorted query? If the answer is yes, then I will try again, and then post the code here if I continue to get an error.

THANKS!

[Rabbit]

I don't see where you get 140 tables.

I'm thinking 2 tables and 7 queries. Apart from your main table of course.

One table will be for regional ranking and the other will be for national. Both will have 2 fields.

tbl_National, tbl_Regional
Rank(AutoNumber) ItemID

Two queries to delete the two tables.
Two queries to recreate them.
Two append queries to get the ranking at the regional and national level.
One query to rule them all.

By which I mean join the store ranking and the other tables by ItemID.

A convoluted and UNTESTED theory. Maybe someone else can provide you with a better solution.

[AnndieMac]

Thanks for trying Rabbit, but I guess I wasn't clear enough. There are 34 different regions, and I need a ranking of items within each region, plus the national. So, that's 35 tables for one ranking system. But they also want 4 different ranking systems - loss by qty, gain by qty, loss by dollar and gain by dollar. So 35 times 4 = 140 tables (Plus 140 times 3 queries!)

I'm testing now to see if I can just have one table regardless of system, and use code to force the sort and ranking each time a store summary is selected. That would reduce my tables, but it would also slow down the database significantly, I think.

I don't see where you get 140 tables.

I'm thinking 2 tables and 7 queries. Apart from your main table of course.

One table will be for regional ranking and the other will be for national. Both will have 2 fields.

tbl_National, tbl_Regional
Rank(AutoNumber) ItemID

Two queries to delete the two tables.
Two queries to recreate them.
Two append queries to get the ranking at the regional and national level.
One query to rule them all.

By which I mean join the store ranking and the other tables by ItemID.

A convoluted and UNTESTED theory. Maybe someone else can provide you with a better solution.

[Rabbit]

And all this information is going into one report?

You can take my original idea and modify it slightly.
In the code you can have a for i = 1 to 34 loop and insert the results into another table. You end up running a lot of queries but you don't have to make as many queries.

I originally thought you only needed the ranking information for a chosen store but if you need it for every single store and every region, then i don't think there's a way around running a lot of queries. But with a loop you can cut down drastically on the amount of queries you'll need to create.

[NeoPa]

I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been able to create these summaries easily enough using several sorted queries and a form to select the specific store, or to pull up the regional or national summaries.

The problem is that they want to be able to have one file for each store which lists the top 30 item losses with additional fields showing where that same item ranked at the regional and national level.

One alternative I came up with was to create a new table for each of the higher level summaries (national and regional) and either use Autonumber or code to add ranking numbers. However, this is not ideal because this would require approximately 140 tables to be created to cover all of the regions.

I also tried to write a function which would be called when the user selects the store to summarize which would temporarily add the ranking to the sorted queries. However, I am getting an error that the query is not updateable. I am not sure if this is a limitation in Access, or if it is something I am doing wrong. (In a fit of frustration I deleted the code, so I can't paste it here.)

So, first question: Is it even possible to update a field for each record on a sorted query? If the answer is yes, then I will try again, and then post the code here if I continue to get an error.

THANKS!

Rabbit may not be confident in his suggestions but he's certainly on the right lines. He understands the difference between data items and data layout.
Have a look in (Normalisation and Table structures) and decide how best to store your information. It will run a lot more efficiently if it's in the one table. Above all, never let the requestors tell you how to design a database - you WILL regret it :(.
Please do come back when you've read and digested that article. I feel sure we can be more help at that stage :)

[Rabbit]

Rabbit may not be confident in his suggestions but he's certainly on the right lines. He understands the difference between data items and data layout.
Have a look in (Normalisation and Table structures) and decide how best to store your information. It will run a lot more efficiently if it's in the one table. Above all, never let the requestors tell you how to design a database - you WILL regret it :(.
Please do come back when you've read and digested that article. I feel sure we can be more help at that stage :)

Well I've only been using Access for 2 months now and sometimes I feel like there may be a better solution because I don't yet know a lot of the intricacies of Access and VB.

[NeoPa]

Rabbit, That wasn't a criticism of you. Quite the contrary!
If you've only been using Access for 2 months then I'm even more impressed :)
You've shown good understanding of the concepts so far - I thought you were far more experienced. It's our luck you visited and didn't get thrown out with the rubbish in the morning. Welcome to the party :D

[AnndieMac]

Rabbit, thanks again for your help. Just so you know, append queries didn't work because the records are not appended in the sort order, but rather in the original order on the table, so when I added my rankings, they weren't right.

NeoPa, I'm not sure what to say in regards to your post. I know I don't write like a developer; that's because I'm not one - I'm an accountant. But don't let that fool you. I DO understand the basics of good database development, and while I might not have known the terminology in the post you referenced, I did already know the concepts.

I do have a working database now, although it runs too slowly to be of much use. I will continue working on it, but I probably won't be posting here again.

[NeoPa]

Thanks for trying Rabbit, but I guess I wasn't clear enough. There are 34 different regions, and I need a ranking of items within each region, plus the national. So, that's 35 tables for one ranking system. But they also want 4 different ranking systems - loss by qty, gain by qty, loss by dollar and gain by dollar. So 35 times 4 = 140 tables (Plus 140 times 3 queries!)

I'm testing now to see if I can just have one table regardless of system, and use code to force the sort and ranking each time a store summary is selected. That would reduce my tables, but it would also slow down the database significantly, I think.

Andie,
My intention was not to insult you or to imply lack of intelligence. I do though, find it hard to reconcile this post (#3) with your statement that you understand the concepts in the link I posted. Why would you think you needed multiple tables to hold data of a similar type? :confused:
My intentions are merely to help and guide.