asked on

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

Ryan Chong

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.

Kelly Martens

ASKER

Can you show me how to do that to this query please?

Ryan Chong

your query is a bit complex, can you upload a sample here?

Kelly Martens

ASKER

HOld on I am trying to figure out how to do an attachment on here.....

Kelly Martens

ASKER

Hope this helps with it. SQL I love. THis not so much.

Pawan Kumar

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

Kelly Martens

ASKER

Templates.zip

Pawan Kumar

Please try the last solution i gave.

Kelly Martens

ASKER

Thank you. I am getting a syntax error with its usual unhelpful reason why. WOrking on it.

Kelly Martens

ASKER

Access is not very helpful in diagnosing this stuff.

Kelly Martens

ASKER

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

Pawan Kumar

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

Kelly Martens

ASKER

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

Pawan Kumar

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

Kelly Martens

ASKER

ok but does >= work on a string value? But I will try it.

Actually put the whole script here with it please?

Pawan Kumar

ok but does >= work on a string value?

Yes it will work.

Pawan Kumar

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

Kelly Martens

ASKER

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

Pawan Kumar

can you please post your final query you are using?

Kelly Martens

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

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

Kelly Martens

ASKER

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.

Ryan Chong

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.

Pawan Kumar

The answer you selected was completely driven by my logic with an small extra condition which I had already given in my last comment.

Ryan Chong

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...

Kelly Martens

ASKER

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.....

Kelly Martens

ASKER

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.

Pawan Kumar

Cheers :)