CONVERT ROW_NUMER() to MS ACCESS QUERY

Please help convert the below to be an autonumber that will work in MS Access like it does in SQL. Thank you cause I am kind of stumped.

SELECT ROW_NUMBER() OVER (ORDER BY Object_ID) as Rec_ID, *
FROM
(SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
UNION
SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
UNION
SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name,
'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID) x
Kelly MartensAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in Access there's no row_number function available, you got to self join the table with a unique key to generate such running number.
0
Kelly MartensAuthor Commented:
Can you show me how to do that to this query please?
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
your query is a bit complex, can you upload a sample here?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kelly MartensAuthor Commented:
HOld on I am trying to figure out how to do an attachment on here.....
0
Kelly MartensAuthor Commented:
Hope this helps with it. SQL I love. THis not so much.
0
Pawan KumarDatabase ExpertCommented:
ROWNUMBERS ARE NOT THERE In MS ACCESS. You can use DCOUNT or below logic to generate the row number. Please try this-

SELECT A.*, 
    (SELECT COUNT(*) FROM 
		(
				SELECT *
				FROM
				(
					SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
						FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
					GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
					UNION
					SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
						'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
					UNION 
					SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
					'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
				) 
				AS x 
			)AS w
			WHERE A.Template_ID>=w.Template_ID
		) AS RowNumber
FROM 
(
		SELECT *
		FROM
		(
			SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
				FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
			GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
			UNION
			SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
				'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
			UNION 
			SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
			'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
		) 
		AS x
)AS A
ORDER BY A.Template_ID;

Open in new window

0
Kelly MartensAuthor Commented:
0
Pawan KumarDatabase ExpertCommented:
Please try the last solution i gave.
0
Kelly MartensAuthor Commented:
Thank you. I am getting a syntax error with its usual unhelpful reason why. WOrking on it.
0
Kelly MartensAuthor Commented:
Access is not very helpful in diagnosing this stuff.
0
Kelly MartensAuthor Commented:
Got it. ITs almost there


Template_ID      TempName      Object_ID      Object_Name      Object_Type      RowNumber
3      NONE_0      6      PIVOT TEST      Queries_Temp      8
3      NONE_0      5      SQL QUERY      Queries_Temp      8
3      NONE_0      4      MySQL Query      Queries_Temp      8
3      NONE_0      3      PIPE QUERY      Queries_Temp      8
3      NONE_0      2      SECOND JOIN      Query_Joins_Temp      8
3      NONE_0      2      CSV QUERY      Queries_Temp      8
3      NONE_0      1      PIVOTTEST1      Pivot_Objects_Temp      8
3      NONE_0      1      FIRST JOIN      Query_Joins_Temp      8
0
Pawan KumarDatabase ExpertCommented:
You can change this logic ..if you are getting duplicate values in rownumber column.

Change this
A.Template_ID>=w.Template_ID

to

A.Template_ID=w.Template_ID AND A.Object_ID >= w.Object_ID
0
Kelly MartensAuthor Commented:
Almost there. Last two rows.

Template_ID      TempName      Object_ID      Object_Name      Object_Type      RowNumber
3      NONE_0      6      PIVOT TEST      Queries_Temp      8
3      NONE_0      5      SQL QUERY      Queries_Temp      7
3      NONE_0      4      MySQL Query      Queries_Temp      6
3      NONE_0      3      PIPE QUERY      Queries_Temp      5
3      NONE_0      2      SECOND JOIN      Query_Joins_Temp      4
3      NONE_0      2      CSV QUERY      Queries_Temp      4
3      NONE_0      1      PIVOTTEST1      Pivot_Objects_Temp      2
3      NONE_0      1      FIRST JOIN      Query_Joins_Temp      2
0
Pawan KumarDatabase ExpertCommented:
Add one more column to the logic. lot of duplicate data is there :)

A.Template_ID=w.Template_ID AND A.Object_ID >= w.Object_ID AND A.Object_Name >= w.Object_Name
0
Kelly MartensAuthor Commented:
ok but does >= work on a string value? But I will try it.

Actually put the whole script here with it please?
0
Pawan KumarDatabase ExpertCommented:
ok but does >= work on a string value?

Yes it will work.
0
Pawan KumarDatabase ExpertCommented:
Updated. Entire code.

SELECT A.*, 
    (SELECT COUNT(*) FROM 
		(
				SELECT *
				FROM
				(
					SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
						FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
					GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
					UNION
					SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
						'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
					UNION 
					SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
					'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
				) 
				AS x 
			)AS w
			WHERE A.Template_ID=w.Template_ID AND A.Object_ID >= w.Object_ID AND A.Object_Name >= w.Object_Name
		) AS RowNumber
FROM 
(
		SELECT *
		FROM
		(
			SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
				FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
			GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
			UNION
			SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
				'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
			UNION 
			SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
			'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
		) 
		AS x
)AS A
ORDER BY A.Template_ID;

Open in new window

0
Kelly MartensAuthor Commented:
Template_ID      TempName      Object_ID      Object_Name      Object_Type      RowNumber
3      NONE_0      6      PIVOT TEST      Queries_Temp      5
3      NONE_0      5      SQL QUERY      Queries_Temp      7
3      NONE_0      4      MySQL Query      Queries_Temp      3
3      NONE_0      3      PIPE QUERY      Queries_Temp      3
3      NONE_0      2      SECOND JOIN      Query_Joins_Temp      4
3      NONE_0      2      CSV QUERY      Queries_Temp      1
3      NONE_0      1      PIVOTTEST1      Pivot_Objects_Temp      2
3      NONE_0      1      FIRST JOIN      Query_Joins_Temp      1
0
Pawan KumarDatabase ExpertCommented:
can you please post your final query you are using?
0
Kelly MartensAuthor Commented:
Just what you gave me....

SELECT A.*, 
    (SELECT COUNT(*) FROM 
		(
				SELECT *
				FROM
				(
					SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
						FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
					GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
					UNION
					SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
						'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
					UNION 
					SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
					'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
				) 
				AS x 
			)AS w
			WHERE A.Template_ID=w.Template_ID AND A.Object_ID >= w.Object_ID AND A.Object_Name >= w.Object_Name
		) AS RowNumber
FROM 
(
		SELECT *
		FROM
		(
			SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
				FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
			GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
			UNION
			SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
				'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
			UNION 
			SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
			'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
		) 
		AS x
)AS A
ORDER BY A.Template_ID;

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this instead:

SELECT 

(
     select count(*) from
   (
   SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
UNION
SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
UNION
SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name,
'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
) y where y.Object_ID & y.Object_Name & y.Object_Type <= x.Object_ID & x.Object_Name & x.Object_Type


) as row_number,
x.*

FROM
(
SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
UNION
SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
UNION
SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name,
'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
)  AS x 
order by  x.Object_ID , x.Object_Name,  x.Object_Type

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Please try this-

ADDED A.Object_Type >= w.Object_Type

SELECT A.*, 
    (SELECT COUNT(*) FROM 
		(
				SELECT *
				FROM
				(
					SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
						FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
					GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
					UNION
					SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
						'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
					UNION 
					SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
					'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
					GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
				) 
				AS x 
			)AS w
			WHERE  A.Object_ID >= w.Object_ID AND A.Object_Name >= w.Object_Name AND A.Object_Type >= w.Object_Type
		) AS RowNumber
FROM 
(
		SELECT *
		FROM
		(
			SELECT Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID as Object_ID,Query_Name as Object_Name, 'Queries_Temp' as Object_Type 
				FROM Queries_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Queries_Temp.Template_ID 
			GROUP BY Template_ID,Templates_Temp.TempName, Queries_Temp.Rec_ID, Queries_Temp.Query_Name
			UNION
			SELECT  Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Rec_ID as Object_ID,Query_Join_Name as Object_Name, 
				'Query_Joins_Temp' as Object_Type FROM  Query_Joins_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Query_Joins_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Query_Joins_Temp.Query_Join_Name,Query_Joins_Temp.Rec_ID
			UNION 
			SELECT  Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Rec_ID as Object_ID,Pivot_Name as Object_Name, 
			'Pivot_Objects_Temp' as Object_Type FROM Pivot_Objects_Temp INNER JOIN Templates_Temp on Templates_Temp.Rec_ID = Pivot_Objects_Temp.Template_ID
			GROUP BY Template_ID,Templates_Temp.TempName, Pivot_Objects_Temp.Pivot_Name,Pivot_Objects_Temp.Rec_ID
		) 
		AS x
)AS A
ORDER BY A.Template_ID;

Open in new window

0
Kelly MartensAuthor Commented:
If you get a chance if you could explain what you did here. just kind of put comments in it so I can follow the pieces. But this is perfect. Thank you very much.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
the crucial part is at:

where y.Object_ID & y.Object_Name & y.Object_Type <= x.Object_ID & x.Object_Name & x.Object_Type

Open in new window


in which I try to create a "unique" key for comparison, so that the count(*) returned will reflect correctly.
1
Pawan KumarDatabase ExpertCommented:
The answer you selected was completely driven by my logic with an small extra condition which I had already given in my last comment.
1
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The answer you selected was completely driven by my logic with an small extra condition which I had already given in my last comment.

i felt disappointed for one of the top experts to make such claim...
0
Kelly MartensAuthor Commented:
This was great Pawan. I almost had it several hours earlier today but I got frustrated with the duplicate numbers. Again thank you so much. I can go to bed and to work tomorrow and get this done.....
0
Kelly MartensAuthor Commented:
I am sorry Ryan. I am a little new to this site so please forgive any over sight I made if I did not award points properly.
0
Pawan KumarDatabase ExpertCommented:
Cheers :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.