Retrieve multiple records

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

Guest

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that have different
grades - note that in the rest of the table there could be same position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
 
Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is that it
also brought back positions titles that only has one grade (on record). I am
looking for all of those who have same position titles by different grades.

THANK YOU
 
Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.
 
I have about 95,000 records in my main table - this query brought back about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
.................
Accountant 6 Joe Michael
Accountant 6 Steven Brian
Accountant 7 Smith Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name, first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

Duane Hookom said:
Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Nancy said:
Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is that
it
also brought back positions titles that only has one grade (on record). I
am
looking for all of those who have same position titles by different
grades.

THANK YOU
Click to expand...
Click to expand...
 
You may need to create a couple queries.
First create "qgrpPosGrade"
==============================
SELECT [POSITION TITLE], GRADE
FROM MainTable
GROUP BY [POSITION TITLE], GRADE;
==============================

Then create "grpPosGradeDubs"
==============================
SELECT [POSITION TITLE]
FROM
GROUP BY [POSITION TITLE]
HAVING COUNT(GRADE)>1;
==============================

Then create your final query of your main table and grpPosGradeDubs. Join
the POSITION TITLE fields.

--
Duane Hookom
MS Access MVP
--

Nancy said:
I have about 95,000 records in my main table - this query brought back
about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
................
Accountant 6 Joe
Michael
Accountant 6 Steven Brian
Accountant 7 Smith Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name, first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

Duane Hookom said:
Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy
GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Nancy said:
Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is
that
it
also brought back positions titles that only has one grade (on record).
I
am
looking for all of those who have same position titles by different
grades.

THANK YOU



:

Create a totals query that groups by POSITION TITLE and GRADE.

--
Duane Hookom
MS Access MVP
--

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that have
different
grades - note that in the rest of the table there could be same
position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
Click to expand...
Click to expand...
Click to expand...
 
That still didn't work!

It brought back about multiple accountants with same grade

PLEASE MORE HELP - PLEASE

Duane Hookom said:
You may need to create a couple queries.
First create "qgrpPosGrade"
==============================
SELECT [POSITION TITLE], GRADE
FROM MainTable
GROUP BY [POSITION TITLE], GRADE;
==============================

Then create "grpPosGradeDubs"
==============================
SELECT [POSITION TITLE]
FROM
GROUP BY [POSITION TITLE]
HAVING COUNT(GRADE)>1;
==============================

Then create your final query of your main table and grpPosGradeDubs. Join
the POSITION TITLE fields.

--
Duane Hookom
MS Access MVP
--

Nancy said:
I have about 95,000 records in my main table - this query brought back
about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
................
Accountant 6 Joe
Michael
Accountant 6 Steven Brian
Accountant 7 Smith Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name, first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

Duane Hookom said:
Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy
GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is
that
it
also brought back positions titles that only has one grade (on record).
I
am
looking for all of those who have same position titles by different
grades.

THANK YOU



:

Create a totals query that groups by POSITION TITLE and GRADE.

--
Duane Hookom
MS Access MVP
--

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that have
different
grades - note that in the rest of the table there could be same
position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
Click to expand...
Click to expand...
Click to expand...
 
Provide more sample records that identify your needs. Do you understand what
my queries attempted to do?

--
Duane Hookom
MS Access MVP


Nancy said:
That still didn't work!

It brought back about multiple accountants with same grade

PLEASE MORE HELP - PLEASE

Duane Hookom said:
You may need to create a couple queries.
First create "qgrpPosGrade"
==============================
SELECT [POSITION TITLE], GRADE
FROM MainTable
GROUP BY [POSITION TITLE], GRADE;
==============================

Then create "grpPosGradeDubs"
==============================
SELECT [POSITION TITLE]
FROM
GROUP BY [POSITION TITLE]
HAVING COUNT(GRADE)>1;
==============================

Then create your final query of your main table and grpPosGradeDubs. Join
the POSITION TITLE fields.

--
Duane Hookom
MS Access MVP
--

Nancy said:
I have about 95,000 records in my main table - this query brought back
about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
................
Accountant 6 Joe
Michael
Accountant 6 Steven Brian
Accountant 7 Smith
Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name, first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

:

Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy
GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is
that
it
also brought back positions titles that only has one grade (on
record).
I
am
looking for all of those who have same position titles by different
grades.

THANK YOU



:

Create a totals query that groups by POSITION TITLE and GRADE.

--
Duane Hookom
MS Access MVP
--

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that have
different
grades - note that in the rest of the table there could be same
position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
Click to expand...
Click to expand...
Click to expand...
 
I was trying to understand the last query - but I am not quite sure

Here is more records of my main table:

POSITION TITLE GRADE LAST NAME FIRST NAME
Accountant 6 ....... ......
Accountant 6 ...... .......
Accountant 7 ...... ......
Admin Asst 4 ....... .......
Admin Asst 4 ....... ......
Admin Asst 3 ....... .......
Admin Asst 4 ....... .......
Admin Asst 4 ........ .......
Admin Asst 2 ........ .......

So, I need to retrieve anyone who has same POSITION TITLE but different GRADE

Accountant 7 ....... ......
Accountant 6 ....... ......
Admin Asst 4 ........ .......
Admin Asst 3 ....... .......
Admin Asst 2 ....... ........

The GROUP BY was a very good idea - but I also need to retrieve all fields

THANK YOU
Duane Hookom said:
Provide more sample records that identify your needs. Do you understand what
my queries attempted to do?

--
Duane Hookom
MS Access MVP


Nancy said:
That still didn't work!

It brought back about multiple accountants with same grade

PLEASE MORE HELP - PLEASE

Duane Hookom said:
You may need to create a couple queries.
First create "qgrpPosGrade"
==============================
SELECT [POSITION TITLE], GRADE
FROM MainTable
GROUP BY [POSITION TITLE], GRADE;
==============================

Then create "grpPosGradeDubs"
==============================
SELECT [POSITION TITLE]
FROM
GROUP BY [POSITION TITLE]
HAVING COUNT(GRADE)>1;
==============================

Then create your final query of your main table and grpPosGradeDubs. Join
the POSITION TITLE fields.

--
Duane Hookom
MS Access MVP
--

I have about 95,000 records in my main table - this query brought back
about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
................
Accountant 6 Joe
Michael
Accountant 6 Steven Brian
Accountant 7 Smith
Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name, first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

:

Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM tblNancy
GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern is
that
it
also brought back positions titles that only has one grade (on
record).
I
am
looking for all of those who have same position titles by different
grades.

THANK YOU



:

Create a totals query that groups by POSITION TITLE and GRADE.

--
Duane Hookom
MS Access MVP
--

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that have
different
grades - note that in the rest of the table there could be same
position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
Click to expand...
Click to expand...
Click to expand...
 
In your sample data, which Accountant 6 record is displayed? There are two
of them in your original and only one in your final results. You can't get
at a proper answer without being able to specify which Accountant 6 and
which Admin Asst 4.

--
Duane Hookom
MS Access MVP


Nancy said:
I was trying to understand the last query - but I am not quite sure

Here is more records of my main table:

POSITION TITLE GRADE LAST NAME FIRST NAME
Accountant 6 ....... ......
Accountant 6 ...... .......
Accountant 7 ...... ......
Admin Asst 4 ....... .......
Admin Asst 4 ....... ......
Admin Asst 3 ....... .......
Admin Asst 4 ....... .......
Admin Asst 4 ........ .......
Admin Asst 2 ........ .......

So, I need to retrieve anyone who has same POSITION TITLE but different
GRADE

Accountant 7 ....... ......
Accountant 6 ....... ......
Admin Asst 4 ........ .......
Admin Asst 3 ....... .......
Admin Asst 2 ....... ........

The GROUP BY was a very good idea - but I also need to retrieve all fields

THANK YOU
Duane Hookom said:
Provide more sample records that identify your needs. Do you understand
what
my queries attempted to do?

--
Duane Hookom
MS Access MVP


Nancy said:
That still didn't work!

It brought back about multiple accountants with same grade

PLEASE MORE HELP - PLEASE

:

You may need to create a couple queries.
First create "qgrpPosGrade"
==============================
SELECT [POSITION TITLE], GRADE
FROM MainTable
GROUP BY [POSITION TITLE], GRADE;
==============================

Then create "grpPosGradeDubs"
==============================
SELECT [POSITION TITLE]
FROM
GROUP BY [POSITION TITLE]
HAVING COUNT(GRADE)>1;
==============================

Then create your final query of your main table and grpPosGradeDubs.
Join
the POSITION TITLE fields.

--
Duane Hookom
MS Access MVP
--

I have about 95,000 records in my main table - this query brought
back
about
94,000 records.

Here is what I have:

POSTITION TITLE GRADE LAST NAME FIRST NAME
................
Accountant 6 Joe
Michael
Accountant 6 Steven
Brian
Accountant 7 Smith
Kelly

Here is what I need:
I need to retrieve all fields (position title, grade, last name,
first
name.....) that have different GRADE by same POSITION TITLE

Accoutant 6 Joe Michael
Accoutant 7 Steven Brian


I APPRECIATE ALL YOUR HELP

:

Try this SQL
SELECT tblNancy.*
FROM tblNancy
WHERE ((([POSITION TITLE]) In (SELECT [POSITION TITLE] FROM
tblNancy
GROUP
BY [POSITION TITLE], GRADE HAVING COUNT(*) >1)));

If that doesn't meet your needs then please come back with more
sample
records (provide some variety) and the desired output.

--
Duane Hookom
MS Access MVP
--

Thank you for getting back to me quickly

I just did what you said which was very helpful. Another concern
is
that
it
also brought back positions titles that only has one grade (on
record).
I
am
looking for all of those who have same position titles by
different
grades.

THANK YOU



:

Create a totals query that groups by POSITION TITLE and GRADE.

--
Duane Hookom
MS Access MVP
--

Hello,

Ex:

POSITION TITLE GRADE
Accountant 6
Accountant 6
Accountant 7

From the above example, how do only pull these titles that
have
different
grades - note that in the rest of the table there could be
same
position
titles with same grades.

PLEASE HELP ASAP - THANK YOU
Click to expand...
Click to expand...
Click to expand...
 
Back
Top