Pull specific number of records for each user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with multiple records for each name. I want to pull 10% of the
records for each name. I have created a query that calculates how many
records it should be. How can I pull the specific number of records for each
name from the table?
 
In the table there must be some field that unique each record, let say for
the example ID

Try this type of SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name])

If you want the 10 percent of records that return to be in a certain order,
for example that highst Id's the add a sort to the nested SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name] Order By ID Desc)
 
Are you assuming I am using 2 tables?
The unique field in the table I am using is the loan number. There are many
loan numbers tied to one employee.

Susan 1256
Susan 4398
Susan 5278
Ted 4326
Ted 7302
Linda 2467

I want to pull 10% of the loans for each employee. I would need both the
employee and loan fields in the query.

Ofer Cohen said:
In the table there must be some field that unique each record, let say for
the example ID

Try this type of SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name])

If you want the 10 percent of records that return to be in a certain order,
for example that highst Id's the add a sort to the nested SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name] Order By ID Desc)


--
Good Luck
BS"D


lreecher said:
I have a table with multiple records for each name. I want to pull 10% of the
records for each name. I have created a query that calculates how many
records it should be. How can I pull the specific number of records for each
name from the table?
Click to expand...
Click to expand...
 
No, in the example I used only one table (TableName)
It a nested query, a select query withinn a select query

Select T1.* From TableName As T1
Where T1.[loan number] In (Select 10 Percent T2.[loan number] From TableName
As T2 Where T2.[name] = T1.[Name] Order By T2.[loan number] Desc)

You can copy this SQL and paste it to a query, just make sure that the
fields and Table names are correcet

Replace the table name with TableName

Also, if you have a name, table or field that consist of two names, put it
in square brackets
--
Good Luck
BS"D


lreecher said:
Are you assuming I am using 2 tables?
The unique field in the table I am using is the loan number. There are many
loan numbers tied to one employee.

Susan 1256
Susan 4398
Susan 5278
Ted 4326
Ted 7302
Linda 2467

I want to pull 10% of the loans for each employee. I would need both the
employee and loan fields in the query.

Ofer Cohen said:
In the table there must be some field that unique each record, let say for
the example ID

Try this type of SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name])

If you want the 10 percent of records that return to be in a certain order,
for example that highst Id's the add a sort to the nested SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name] Order By ID Desc)


--
Good Luck
BS"D


lreecher said:
I have a table with multiple records for each name. I want to pull 10% of the
records for each name. I have created a query that calculates how many
records it should be. How can I pull the specific number of records for each
name from the table?
Click to expand...
Click to expand...
Click to expand...
 
Yeah!!! It worked. Thank you so much.

Ofer Cohen said:
No, in the example I used only one table (TableName)
It a nested query, a select query withinn a select query

Select T1.* From TableName As T1
Where T1.[loan number] In (Select 10 Percent T2.[loan number] From TableName
As T2 Where T2.[name] = T1.[Name] Order By T2.[loan number] Desc)

You can copy this SQL and paste it to a query, just make sure that the
fields and Table names are correcet

Replace the table name with TableName

Also, if you have a name, table or field that consist of two names, put it
in square brackets
--
Good Luck
BS"D


lreecher said:
Are you assuming I am using 2 tables?
The unique field in the table I am using is the loan number. There are many
loan numbers tied to one employee.

Susan 1256
Susan 4398
Susan 5278
Ted 4326
Ted 7302
Linda 2467

I want to pull 10% of the loans for each employee. I would need both the
employee and loan fields in the query.

Ofer Cohen said:
In the table there must be some field that unique each record, let say for
the example ID

Try this type of SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name])

If you want the 10 percent of records that return to be in a certain order,
for example that highst Id's the add a sort to the nested SQL

Select T1.* From TableName As T1
Where T1.ID In (Select 10 Percent T2.ID From TableName As T2
Where T2.[name] = T1.[Name] Order By ID Desc)


--
Good Luck
BS"D


:

I have a table with multiple records for each name. I want to pull 10% of the
records for each name. I have created a query that calculates how many
records it should be. How can I pull the specific number of records for each
name from the table?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top