Hello MS Access Community
I have TestQ1 which gives me the count of each ID (query is perfect). Depending on the values returned, for example, ID 120 has a count of 9 so what I would like is the following for TestQ2 query:
Look at the Test table value column and since ID 120 count is 9, return the top 3 rows with the highest random number. Same for other ID's, ID 123 should return the top 5 highest when you follow the same rules.
Thoughts?
Last edited by AccountingJ; 10-28-2021 at 04:53 PM.
Suggest you upload a sample DB for us to analyze and calculate. To this, you will need to make your data anonymous (ie. Dummy Data that is representative of your actual DB) then run a compact and repair. Zip the file as ACCDB files will not load and then go to "Go Advanced" and upload your DB.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I did a little research and I was able to return random records every time I ran the query (see query2).
I just need to automate it so it can return the number found in the TestQ2 query Test field. I have gone through numerous videos/articles and can't seem to find what I need to be able to achieve this.
In order for that to happen, you will need to join the existing query with the table that has the random numbers and then group the new query by Top 3 if I am understanding your issue correctly since you did not provide a mocked up solution of what you want.
Sorry - so basically I have my raw data in the "Values" table. I have a query that counts the total number of IDs and I have a "Test" table that indicates how many random records I should return.
So if we look at the picture below, ID 120 has a count of 9, and if we look at the "Test" table we should return 3 random records for that ID. ID 123 has a total count of 13 so that ID should bring 5 random records from the Values table. The "Copy of Values" is an example of what I would like the query to return.
The "Test" table changes weekly, sometimes we need more random records, sometimes we need less.
I just don't know how to reference the "Test" table.
Tables.pngResults.JPG
Last edited by AccountingJ; 10-25-2021 at 11:36 PM.
You need to have a common field in both tables. Without that, I see no way for that to happen. There has to be a relationship between tables in order to access the data.
What about this new query? query no. 4 has the raw data w/the no. of random rows I would want for that specific ID?
To be honest, you have now confused me. I have no idea what you are trying to do. How about explaining in simple English what the objective is. What you are presenting makes no sense to me. As I said before, you need to have relationships in order extract data from any table/query. There has to be a common field.
I misunderstood your last post.
Okay, so I would like a query to return random rows depending on the ID count (see new DB).
As you can see in the "Values" table, you will find ID 120 has a total count of 9 rows. If you then look at the "Test" table, you will find three columns (ID, Count_Tot, and Test_Num).
If you filter the "Test" table you will see that ID 120 with a Count_Tot of 9 has a Test_Num of 3 which means, I should have a query to pull 3 random rows from the "Values" table.
This should happen for every ID but the random rows to return is dependent on their count.
Or perhaps, can I limit the results of a query from another table or query? Like if I have a query or table that says to limit my results for ID 123 to 4, can that be tied to a query? After that, I can add the RND function and set it to DES and that should give me what I need.
If you have common fields then you can join them. Otherwise you have a cartesian join and I don't think that is what you are looking for.
That's good news if it's possible.
Check out MasterQ. It returns all the rows. How can I get it to return the number of records indicated under the "Test_Num" field per "ID"?
If successful, the query should return 12 total records, 3 for ID 120, 5 for ID 123, 2 for ID 456, and 2 for ID 789.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks