Query Problem

  • Thread starter Thread starter bigbore50
  • Start date Start date
B

bigbore50

Hello
I have something wrong with my query and i can't seem to find it

I want to select put a position number on each of the rows based on
"Completed" date

I have a table like this
5-Digit Dealership Number Completed
12345 12/12/06
12345 11/12/06
12345 5/5/06
23456 4/2/06
etc

Here is my Query

SELECT T1.[5-Digit Dealership Number], T1.COMPLETED, Count(*) AS
[Position]
FROM [tbl_all-info-needed] AS T1 INNER JOIN [tbl_all-info-needed] AS T2

ON (T1.COMPLETED<=T2.COMPLETED)
AND (T1.[5-Digit Dealership Number]=T2.[5-Digit Dealership Number])
GROUP BY T1.[5-Digit Dealership Number], T1.COMPLETED
ORDER BY t1.[5-Digit Dealership Number], T1.completed DESC;

I want it to do this
5-Digit Dealership Number Completed Position
12345 12/12/06 1
12345 11/12/06 2
12345 5/5/06 3
23456 4/2/06 1
etc
The problem is some of the positions are showing up as 4, and 8
and each dealer has a max of 3 completed dates
I want it to group each 5-digit dealer number and put a position on the
completed date


Thanks in advance
 
That will not work
it is giving figures like 55, 38, 27 etc.

all weird numbers
i want it to count 1-3




Duane said:
Try Something like this:

SELECT T1.[5-Digit Dealership Number], T1.[Completed],
(SELECT Count(*)
FROM [tbl_all-info-needed] T2
WHERE T2.Completed <=T1.Completed) As Position
FROM [tbl_all-info-needed] T1;
--
Duane Hookom
Microsoft Access MVP


[email protected] said:
Hello
I have something wrong with my query and i can't seem to find it

I want to select put a position number on each of the rows based on
"Completed" date

I have a table like this
5-Digit Dealership Number Completed
12345 12/12/06
12345 11/12/06
12345 5/5/06
23456 4/2/06
etc

Here is my Query

SELECT T1.[5-Digit Dealership Number], T1.COMPLETED, Count(*) AS
[Position]
FROM [tbl_all-info-needed] AS T1 INNER JOIN [tbl_all-info-needed] AS T2

ON (T1.COMPLETED<=T2.COMPLETED)
AND (T1.[5-Digit Dealership Number]=T2.[5-Digit Dealership Number])
GROUP BY T1.[5-Digit Dealership Number], T1.COMPLETED
ORDER BY t1.[5-Digit Dealership Number], T1.completed DESC;

I want it to do this
5-Digit Dealership Number Completed Position
12345 12/12/06 1
12345 11/12/06 2
12345 5/5/06 3
23456 4/2/06 1
etc
The problem is some of the positions are showing up as 4, and 8
and each dealer has a max of 3 completed dates
I want it to group each 5-digit dealer number and put a position on the
completed date


Thanks in advance
Click to expand...
Click to expand...
 
Thanks for your help

But i fixed the problem
If there are more than two records with the same date it recounts the
record for some reason

Thanks


That will not work
it is giving figures like 55, 38, 27 etc.

all weird numbers
i want it to count 1-3




Duane said:
Try Something like this:

SELECT T1.[5-Digit Dealership Number], T1.[Completed],
(SELECT Count(*)
FROM [tbl_all-info-needed] T2
WHERE T2.Completed <=T1.Completed) As Position
FROM [tbl_all-info-needed] T1;
--
Duane Hookom
Microsoft Access MVP


[email protected] said:
Hello
I have something wrong with my query and i can't seem to find it

I want to select put a position number on each of the rows based on
"Completed" date

I have a table like this
5-Digit Dealership Number Completed
12345 12/12/06
12345 11/12/06
12345 5/5/06
23456 4/2/06
etc

Here is my Query

SELECT T1.[5-Digit Dealership Number], T1.COMPLETED, Count(*) AS
[Position]
FROM [tbl_all-info-needed] AS T1 INNER JOIN [tbl_all-info-needed] AS T2

ON (T1.COMPLETED<=T2.COMPLETED)
AND (T1.[5-Digit Dealership Number]=T2.[5-Digit Dealership Number])
GROUP BY T1.[5-Digit Dealership Number], T1.COMPLETED
ORDER BY t1.[5-Digit Dealership Number], T1.completed DESC;

I want it to do this
5-Digit Dealership Number Completed Position
12345 12/12/06 1
12345 11/12/06 2
12345 5/5/06 3
23456 4/2/06 1
etc
The problem is some of the positions are showing up as 4, and 8
and each dealer has a max of 3 completed dates
I want it to group each 5-digit dealer number and put a position on the
completed date


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
SORRY

Instead of counting the Position
can i just show the Position? like 1st row of 12345 = 1
2nd row of 12345 = 2

etc.




Thanks for your help

But i fixed the problem
If there are more than two records with the same date it recounts the
record for some reason

Thanks


That will not work
it is giving figures like 55, 38, 27 etc.

all weird numbers
i want it to count 1-3




Duane said:
Try Something like this:

SELECT T1.[5-Digit Dealership Number], T1.[Completed],
(SELECT Count(*)
FROM [tbl_all-info-needed] T2
WHERE T2.Completed <=T1.Completed) As Position
FROM [tbl_all-info-needed] T1;
--
Duane Hookom
Microsoft Access MVP


:

Hello
I have something wrong with my query and i can't seem to find it

I want to select put a position number on each of the rows based on
"Completed" date

I have a table like this
5-Digit Dealership Number Completed
12345 12/12/06
12345 11/12/06
12345 5/5/06
23456 4/2/06
etc

Here is my Query

SELECT T1.[5-Digit Dealership Number], T1.COMPLETED, Count(*) AS
[Position]
FROM [tbl_all-info-needed] AS T1 INNER JOIN [tbl_all-info-needed] AS T2

ON (T1.COMPLETED<=T2.COMPLETED)
AND (T1.[5-Digit Dealership Number]=T2.[5-Digit Dealership Number])
GROUP BY T1.[5-Digit Dealership Number], T1.COMPLETED
ORDER BY t1.[5-Digit Dealership Number], T1.completed DESC;

I want it to do this
5-Digit Dealership Number Completed Position
12345 12/12/06 1
12345 11/12/06 2
12345 5/5/06 3
23456 4/2/06 1
etc
The problem is some of the positions are showing up as 4, and 8
and each dealer has a max of 3 completed dates
I want it to group each 5-digit dealer number and put a position on the
completed date


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Hi,

if more than two records with the same date the query doesn't work.

Do you have an Id field in the table ?
ex.
----
Id 5-Digit Dealership Number Completed
90 12345 12/12/2006
85 12345 12/12/2006
77 12345 11/12/2006
22 22345 04/02/2006
----

if you have the id field try this query
----
SELECT T1.[5-Digit Dealership Number],
T1.COMPLETED,
Count(T2.Completed)+1 AS [Position]
FROM [tbl_all-info-needed] AS T1
LEFT JOIN [tbl_all-info-needed] AS T2
ON (T1.[5-Digit Dealership Number]=T2.[5-Digit Dealership Number])
AND (T1.COMPLETED<T2.COMPLETED
OR T1.COMPLETED=T2.COMPLETED
AND T1.ID<T2.Id)
GROUP BY T1.Id,T1.[5-Digit Dealership Number], T1.COMPLETED
ORDER BY t1.[5-Digit Dealership Number], T1.completed DESC,T1.Id DESC;
 
[tbl_all-info-needed] is not a Table ?

If it is, add a new field:
Name: Id
Type: Autonumber

Bye
 
Back
Top