Query Problem

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
 
B

bigbore50

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

bigbore50

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

bigbore50

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

giorgio rancati

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;
 
G

giorgio rancati

[tbl_all-info-needed] is not a Table ?

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

Bye
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rank in select query not right 1
help with ranking in a query 2
top 3 values Subquery problems 2
Merge records with same number 0
SQL query to select rows with highest vales 2
Lag 1 day relationship 11
INNER JOIN auto changes 6
Calculate 90th Percentile using top 10 percent 3

Top