Query to select different row number from each group ?

[Marta_mtm]

Hi access forum members,

Can you help me with a selection doubt?

I would like to select a different number of rows from each group. I have two tables, one with the number of rows that I need from each group. And a 2nd table with all my data. I don't need all data of the 2nd table, I would select only a little concrete specific part of each group.

Somebody knows, If it's possible make that with Access?

Thanks you

Marta

[ItsMe]

If by, selecting a number of rows, you mean that you want to select a specific quantity if rows, that is not a conventional request. The quantity of rows retrieved is usually based on the criteria in the query. The quantity is a result not a criteria. A criteria usually starts with a key value or search string. The criteria may find a certain number of matches and retrieve those rows of data.

One solution for your request may be to use SELCT TOP. you can retrieve the TOP results of an SQL statement. For instance, if you want to see only the top 10 finishers in a race, you could create a query that retrieves everyone who finished. In the query, there would have to be each individual's finish position. You would sort the order by [FinishPosition] and include

SELECT TOP 10

.

[Marta_mtm]

If by, selecting a number of rows, you mean that you want to select a specific quantity if rows, that is not a conventional request. The quantity of rows retrieved is usually based on the criteria in the query. The quantity is a result not a criteria. A criteria usually starts with a key value or search string. The criteria may find a certain number of matches and retrieve those rows of data.

One solution for your request may be to use SELCT TOP. you can retrieve the TOP results of an SQL statement. For instance, if you want to see only the top 10 finishers in a race, you could create a query that retrieves everyone who finished. In the query, there would have to be each individual's finish position. You would sort the order by [FinishPosition] and include

SELECT TOP 10

.

Hi I must to select different number (specific quantity in field2:Table1) of rows in each group(field1:Table2). I have these 2 tables:

Table1				Table2
field1	field2			field1	field3	field4	field5
1	3			1	0.375	Sp	Rm1
2	6			1	0.416667	Sp	Rm2
3	9			1	0.458333	Sp	Rm3
4	3			1	0.5	Sp	Rm4
5	3			1	0.541667	Sp	Rm5
6	3			1	0.583333	Sp	Rm6
7	3			1	0.625	Sp	Jm1
8	9			1	0.666667	Sp	Jm2
9	6			1	0.708333	Sp	Jm3
10	3			1	0.75	Sp	Jm4
11	3			1	0.791667	Sp	Jm5
12	3			1	0.833333	Sp	Jm6
13	3			1	0.875	Sp	Jm7
14	3			2	0.916667	Sp	Jm8
				2	0.958333	Sp	Jm9
				2	1	Sp	Jm10
				2	1.041667	Sp	Jm11
				2	1.083333	Sp	Jm12
				2	1.125	Sp	Jm13
				2	1.166667	Sp	Jm14
				2	1.208333	Sp	Jm15
				2	1.25	Sp	Jm16
				2	1.291667	Sp	Jm17
				2	1.333333	Sp	Jm18

[ItsMe]

You can use the query builder to build Queries. In design view of your query object, you can add criteria to a field. Use the grid towards the bottom of the window to add "Criteria" for a given field. The result will be a WHERE clause or maybe a HAVING clause added to your SQL statement.

If you only want to see records that = 9 for field2, type 9 in the criteria for that field.

[Marta_mtm]

But I have more than thousand groups, It would be great something more automatic.

[ItsMe]

Perhaps you can use a combobox or a listbox to filter a subform's recordset. Another option may be to create a Dynamic Parameterized query. You can also use VBA to create a query object and define its properties.

[Marta_mtm]

Perhaps you can use a combobox or a listbox to filter a subform's recordset. Another option may be to create a Dynamic Parameterized query. You can also use VBA to create a query object and define its properties.

I'm sorry, I'm new in Access. I tried to make with combobox. I can filter since one combobox to another, but all the records of the selection. I can get fix a different number in each group.

Thanks for your answer

[ItsMe]

I don't know what to suggest. You are not saying what you did exactly and I don't really know what you are trying to do. I will typically apply criteria to a query and use the query for a form or report's recordsource.

[rpeare]

I agree with ItsMe your request is not clear.

I understand you are trying to select 3 records for item 1 and 6 records for item 2 but there are literally dozens of ways you could do this.

What are your selection criteria for table 2? is it alphabetic on Field5 (Descending or Ascending)? is it Random? Is it by increasing or decreasing values in field 3? There are so many ways to interpret your request as to leave one stymied.

[Marta_mtm]

I agree with ItsMe your request is not clear.

I understand you are trying to select 3 records for item 1 and 6 records for item 2 but there are literally dozens of ways you could do this.

What are your selection criteria for table 2? is it alphabetic on Field5 (Descending or Ascending)? is it Random? Is it by increasing or decreasing values in field 3? There are so many ways to interpret your request as to leave one stymied.

The 2 tables are from different databases, however they have in common one field (Field1-number of week) I need to get several data of this one to another.

My criteria selection for rows is the number of sightings multiplied by 3 (field2-table1), by these reason change between different groups(week-field1 Table1 and 2). And the Field5 (table2) must be a latitude(txt format, for use in other programme that requires that). The field 3(table2) is time data (number format), by this reason is increasing values.
If I have 1 sighting for the field1, I need chose 3 records. I have a huge database, and make that handful will be a lot of work.
If you can make a suggestion, I would be very happy.

[rpeare]

I am not making myself clear.

*HOW* do you want to select records form table 2?

Is the selection random?
OR
Is the selection based on specific criteria?

If it is based on specific criteria, what are those criteria?

[Marta_mtm]

I am not making myself clear.

*HOW* do you want to select records form table 2?

Is the selection random?
OR
Is the selection based on specific criteria?

If it is based on specific criteria, what are those criteria?

Yes It's random

[rpeare]

Attached is an example of pulling records and appending them to a temporary table (I don't know what you want your output to be). There is one module that pulls the records you'd have to run. I don't think what you're asking for is possible in a static query because you will have a variable number of field1 values and a variable number of records you want to pull.

Someone may have more familiarity with the RND/RANDOMIZE functions I used to be able to do this more efficiently but I could not get the RANDOMIZE function to properly reseed values for the RND function.

Marta_mtm.zip

[Marta_mtm]

The selected subset it doesn't works. And I'm using the RND function for get the random values.

I get the subsamples with other software.

Thanks for send me the file with your suggestions.

[ItsMe]

I am not sure what the objective is but

Randomize
iRecNum = (Int((max - min + 1) * Rnd + min))

is the same as

Randomize
iRecNum = (max * Rnd) + 1

So iRecNum should be returning a random between 1 and the dcount result. Because iRecNum is a long integer, it will automatically truncate the Rnd and there is not any need for the Int() function.

I did not study the results of the procedure carefully,only read the code. Nothing is jumping out at me why Rnd would not be reseeded for each iteration. The only thing that should be able to affect the iRecNum would be

If iRecNum >= 2 Then
For j = 1 To iRecNum - 1
rst_Tbl2.MoveNext
Next j
End If

and I do not see how the second Dcount would allow this If Then statement to ever run. Maybe commenting it out and studying the results would shed some light.