[Mylo]

Is there a way to create a table like the following in SQL:

Title
------
1
2
3
4
5

Or will I have to create a table of it through Access? Which I'd rather not do. Or... I am wanting this to appear in the drop down box in the datasheet views, perhaps there is another way?

Thanks

UPDATE: Ok, hate finding the solution just as I post for help. I could change the row source type.

This post has been edited by Mylo: 30 August 2012 - 09:54 AM

[e_i_pi]

You need not create a table. Any flavour of SQL, the following will work:

SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5

Alternately, you can use temporary tables and a while loop to do this, like so in MSSQL for example:

DECLARE @count int;
DECLARE @tempTable table(ID int);
SET @count = 1 
WHILE (@count <= 5) 
BEGIN 
   INSERT INTO @tempTable (ID) VALUES (@count) 
   SET @count = (@count + 1) 
END 
SELECT * FROM @tempTable

If you are using Access specifically, the second option might not work, as Access SQL has some very distinct differences to MSSQL, and that syntax might fail.

[Mylo]

That's a good method e_i_pi as will surely keep that in mind. I don't have access to access at the moment, but I will try that 2nd query when I can just out of interest. Thanks.

And another question, I'll just use this thread

How can one rank each record in a group based on a field. Well just need to rank each record in the group, ORDER BY will take care of the field.

Event - User

EventA - User 1
EventA - User 2
EventA - User 3
EventB - User 1
EventB - User 2
EventB - User 3

Turns into... (Where the highest scores is the highest position)

Event - User - Rank

EventA - User 1 - 1
EventA - User 2 - 2
EventA - User 3 - 3
EventB - User 1 - 1
EventB - User 2 - 2
EventB - User 3 - 3

It does relate to a small part of my assignment (just mine specifically, set up all the tables how I think would be best, but not familiar enough with SQL to do the queries for them), but since it is the foundation of the other many queries I am pretty much stuck without it, or I'd have to simplify my database (which is no fun, practical maybe)

I've been trying the DCOUNT function suggested by some other sites, but I just end up with 0, or the number of records filling the column.

This post has been edited by Mylo: 05 September 2012 - 06:20 PM

[e_i_pi]

If it's for Access, I'm not sure (I don't use Access any more nowadays). I found an MSDN article on it here though, which seems to do what you require. Ranking results is notoriously difficult using just SQL, and I've seen numerous methods, none of which are straight-forward. MSSQL seems to have a solution using RANK() OVER (PARTITION...), but I've never had the need to try it myself. I'm not sure if Access SQL would have that functionality, as it seems pretty advanced and from what I remember, Access lacks some features that are available in more advanced SQL interfaces. Could be wrong though, as I said, I don't use Access nowadays.

[Mylo]

Ok, I have looked at the link, Method 1 seems to work, but Method 2 (which I think I'm after) doesn't seem to work with the 2010 version (A CategoryID does not exist). Anyway, I tried applying the same process to my database, but am again ending up with one group with the number of records.

I have to get my written part done so I don't have time to continue playing with it, if I have some time after that, I may be able to do something with VB.

Thanks.

[Mylo]

Partial Success! After fiddling around for a while I just decided to try something in desperation. Each group of events now has its own ranking.

The only problem is that because the score field needs to accept both a discrete value and a time, the discrete values are ranked correctly, but the times are done in reverse. Higher discrete values earn a better rank, lower times earn a better rank.

SQL:

SELECT 
Prod1.ChildEvent,
Prod1.ParentEvent, 
Prod1.StudentID,
Prod1.Score, 
(
     SELECT Count(*) 
     FROM 
     (
          SELECT 
          tblParticipation.ChildEvent, 
          tblParticipation.ParentEvent, 
          tblParticipation.Score
          FROM tblParticipation
     ) 
     WHERE [Score] >[Prod1].[Score] 
     AND (ChildEvent = Prod1.ChildEvent) 
     AND (ParentEvent = Prod1.ParentEvent) 
) + 1
AS Ranking
FROM 
(
     SELECT 
     tblParticipation.ChildEvent, 
     tblParticipation.ParentEvent, 
     tblParticipation.StudentID, 
     tblParticipation.Score
     FROM tblParticipation
) AS Prod1
ORDER BY 
ParentEvent, 
ChildEvent, 
VAL(Prod1.Score), 
Val(FORMAT(Score, 'MM')) DESC, 
Val(FORMAT(Score, 'NN')) ASC;

OUTPUT:

ChildEvent	ParentEvent	StudentID	Score	Ranking

Javelin	Sports Carnival 2012	zwinc3	25.4	2
Javelin	Sports Carnival 2012	trere3	26.5	1
Long Jump	Sports Carnival 2012	acheu8	5.1	3
Long Jump	Sports Carnival 2012	ttaka5	6.6	2
Long Jump	Sports Carnival 2012	mxion1	7.8	1
Running 100M	Sports Carnival 2012	bthom364	10:49	6
Running 100M	Sports Carnival 2012	acroo7	11:57	5
Running 100M	Sports Carnival 2012	aclar392	12:27	4
Running 100M	Sports Carnival 2012	acheu8	12:43:59	3

This post has been edited by Mylo: 07 September 2012 - 07:15 AM

[Mylo]

Done!

Should have been obvious last night, but all I had to do was place an inline if statement in a where statement

     WHERE IIF([Score] LIKE '*:*', [Score] < [Prod1].[Score],  [Score] > [Prod1].[Score])
     AND (ChildEvent = Prod1.ChildEvent) 
     AND (ParentEvent = Prod1.ParentEvent)