How to pull 1 percentage of records (data) in Table

[vijaykk028]

i have One bar-code in Field1 and field2 some records like below
Field1 Field2
000-93-9949-1 1
000-93-9949-1 2
000-93-9949-1 3

000-93-9949-2 22
000-93-9949-2 25
000-93-9949-2 30
000-93-9949-2 30

i need each bar-code values i.e field2 records except 1st and last record 5 percentage values need.

please help any one thank you.

[twinnyfo]

vijaykk028,

I'm not quite sure I understand your question. Could you explain a little better and perhaps we can assist....

[vijaykk028]

thank you very much for your response

we have thousands of bar-codes in field1 example: 000-93-9949-1
field2
1
2
3
4
5
and each bar-code contain one to thousands records available in field2 but not sure how many records available in particular bar-codes, each bar-code 5 percent data i.e field2 records we need pull except 1st and last record (not required 1st land last record) in between 5 percent records we need to pull.

pleas suggest how to pull 5 percent or less than 5.


thank you.

[twinnyfo]

I am sorry but I still don't understand your question. You just restated what I didn't understand before. I don't understand what you mean by 5 percent.

Perhaps if you can explain how these values are used. Are these sequential values? Random? What is the purpose of trying to pull 5%? Which 5%? First 5%? Last 5%? Random 5%?

A little better explanation might go a long way in helping me understand the question better, so we can work toward a solution.

Thanks for your patience with me.

[vijaykk028]

actually we have every bar-code lot of DocID's records available i.e. (numeric) data, some times that will be sequential or UN-sequential order, so purpose is we have to check Randomly 5 percent of docid values in one bar-code except first and last record, for example one bar-code we have Docid number 1 to 5000, so we have to check randomly docid 2 to 4999 in between range with internal database, means 5% of records we have to check the quality, whether indexing done properly or not.

Example:-

Field1 Field2
bar-Code | DocID
000-93-9949-1 | 1254
000-93-9949-1 | 1255
000-93-9949-1 | 1257
000-93-9949-1 | 1259
000-93-9949-1 | 5260
000-93-9949-1 | 5261
000-93-9949-1 | 5262
000-93-9949-1 | 5263
000-93-9949-1 | 5264
000-93-9949-1 | 5265
000-93-9949-1 | 5266

we have to pull 1255 to 5265 in between range 5% of records.so this is possible to pull the 5% of records, i am new in the ms access with vb coding.

thank you so much for your response.

[twinnyfo]

Vijay,

Thanks for the explanation! That really helps a lot. The bottom line is that yes, you should be able to create a solution in MS Access. Your question is relatively straightforward (now that we understand it), but may have a few snags along the way.

You say you are new to VBA coding, so I have to know how new?

Keep in mind that the general principles for this forum are that the help you receive is typically not the final solution, but designed to help you along the way, point you in the right direction or help you troubleshoot your own code.

This is done on purpose, as most of us have jobs that keep us busy throughout the day, and we have our own coding projects to keep us busy. Additionally, we want the help you receive to help you in the long run. If we just develop an answer for you and post it, you have not learned anything except how to cut and paste. However, if we work through the solution with you, you will understand your solution and be able to apply those same principles to other parts of your project--and to new projects as well.

All this being said, I've done some very in-depth step-by-step assistance on this forum, so I am willing to walk you through the steps, but I need to know where you are starting.

Also keep in mind, that along the way, I may make recommendations for structural changes to your DB. These are designed to help you build a project that is in line with established DB design principles.

Standing by for more information.

[Rabbit]

It's doable with just SQL and no VBA but it's very convoluted.

1a. If you mean numerical order when you refer to first and last record, then what you can do is return an aggregate query with the min and max document id and record count grouping by the bar code.

1b. If you mean insert order when you refer to first and last record, then you will need some sort of field that is uniqe and can be sorted that is a proxy for the insert order. Then you do the same query above using that field.

1c. If you do not have this field, theren't not much you can do because you have no way of guaranteeing the order of the records.

2. After that aggregate query, you join it back to the original data and filter out the first and last records. You then add a randomized column that uses one of the fields from the table along with a time factor. The time factor is optional depending on if you want a new set each time and how long you want that set to last.

3. You take this new result and do a partitioned ranking query on it and return only those where the rank is less than or equal to 5% of the count.

[vijaykk028]

Rabbit thank you very much for your valuable suggestion.

i tried, however little bit confusion, please can you explain more detail with some example SQL query's

thank you.

[Rabbit]

Please post the queries you tried and we can help point out which pieces are incorrect and how to correct them.

[vijaykk028]

below query how can i get 5% of data each bar-code? please suggest.

SELECT Table2.Bar_Code, Min(Table2.DocID) AS MinOfDocID, Max(Table2.DocID) AS MaxOfDocID, Count(Table2.DocID) AS CountOfDocID
FROM Table2
GROUP BY Table2.Bar_Code;

Thank you.

[Rabbit]

I went back and numbered the paragraphs.

You completed 1a. Now go to 2 and create that query.

[vijaykk028]

I am new to write SQL queries, still I am confused, first I was created table and I write above I mentioned query. I got result Each bar code records count, next step what? total how many queries need to required? please can I send my (Access File) to your email.

thank you.

[Rabbit]

Sorry, I don't download files from people I do not know.

What part of step 2 are you having trouble with? The join? or the random column?

If you're new to SQL, you should learn the basics first before taking on a task like this. Here's a good tutorial to start with: http://www.w3schools.com/sql/

[vijaykk028]

its ok,thank you for tutorial website.

Yes, how to Join and Random column.

first I have created query i.e. groping the barcode and docid min and max and count, this is one query. next stet what?

thank you

please suggest.

[Rabbit]

Next step is the join. The tutorial I linked has examples of joins.