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!
8 3521 Rabbit 12,516
Recognized Expert Moderator MVP
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.
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 12,516
Recognized Expert Moderator MVP
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 32,586
Recognized Expert Moderator MVP
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 12,516
Recognized Expert Moderator MVP
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 32,586
Recognized Expert Moderator MVP
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
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 32,586
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Irene |
last post by:
Hi all again,
Well, I have my Athletics database with Athletes, Competitions, Scores
tables.
I have a ranking query where I get back the list of the
competitions-athletes and scores opportunely sorted.
I also want to be able to:
1) Select just the top <n> best scores
|
by: ED |
last post by:
I currently have vba code that ranks employees based on their average
job time ordered by their region, zone, and job code. I currently
have vba code that will cycle through a query and ranks each employee
based on their region, zone, job code and avg job time. (See code
below). My problem is that I do not know how to rank the ties. Right
now if two people have the same avg time one will be ranked 3rd and
the other ranked 4th. I would...
|
by: Joseph Bloch |
last post by:
In all the threads on ranking in Access queries, I've not seen
anything that deals with ranking against a subset of the query
results. What I need to do is take the following query results:
Dept Subdept Amount
AAA A1 75
AAA A2 13
AAA A3 45
BBB B1 4
BBB B2 16
|
by: valglad |
last post by:
Hi,
The question below was posted about 4 years ago and noone was able to
answer it back then. I have virtually the same type of problem so would
appreciate if anyone can help.
Thanks
======================================================================
|
by: sara |
last post by:
I hope someone can help with this.
Our director wants to have a report that will have the departments
(Retail stores) across the top, stores down the side and the RANKING of
the YTD dept sales within the chain in the cell.
Store/Dept 1 2 3 4
B 8 1 5 2
R 1 3 2 6
(etc.)
| |
by: Chris |
last post by:
I was wodering if there was a way to rank numbers in a query like this
#'s Rank
100 1
99 2
98 3
98
97 5
96 6
96
|
by: OtheymAverian |
last post by:
For the past few days I have been trying to figure out how to add a Ranking field to a sorted query so I can then reference the rank in a report.
I am using Access 2002 and have no SQL experience, so I have been trying to figure this out with the basic Access functions.
Other than the Rank field (which is not a field on any table, would be something I would have to generate), I pull 2 fields (Name and Score), sorting by score in decending...
|
by: Hunk |
last post by:
Would like some advice on the fillowing
I have a sorted list of items on which i require to search and
retrieve the said item and also modify an item based on its identity.
I think an Map stl container would be most suited. Am i correct in my
assumptions
Problem
Reocrds are already stored with a unique identity in a binary
format.The structure is roughly
----------------------
|
by: Juha Nieminen |
last post by:
Victor Bazarov wrote:
If N is very large, std::set can be a real memory hog.
If a more memory-efficient algorithm is needed, then a heap can be
used. (The exact same type of heap as used in heap sorting.) Simply make
the element comparison so that the element with the lowest score ends up
as the first element of the heap. When the heap grows one element larger
than N, remove this first element.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |