one record query

[mike wild]

I have a database with one record in it and 10 fields

It basically totals and keep track of quiz results. Therefore 10 teams - 10 fields.

I am trying go design a query that will sort the scores into ascending order.

HAs anybody any ideas?

 

[dcx693]

There is no really easy way to design a query to handle the sort based on your database structure.

For this data to be easily sorted, this would have to be the database structure:
Field1: QuizNum
Field2: Team
Field3: Score

So your sample data might look like this:

QuizNum  Team   Score
1                1          90
1                2          92
1                3          90
2                1          87
2                3          92
2                5          91

With this data, it's easy to create a query to sort the scores by team for a particular quiz.

Can you do it wih your existing table structure? Yes, but it would not be easy.

 

[mike wild]

I already thought of that idea. But my form has 10 text boxes that display each teams score. I only use the 1 record on the form.

I may need to transfere this to excel. Your thoughts please.

 

[dcx693]

Why are you using Access for this data then? Access is a relational database manager - and when you use forms, you build forms to display the data.

It sounds like you want to structure the data based on how you want to display it. If that's the case, the Excel will be much easier to use if you want to sort your data.

 

[SajDGreat]

I agree you should use excel as it is a flat file database and acess is a relational database

 

[mike wild]

I have been using access for years and it is now easy to use and managable code, excel is all new.

Thanks

 

[dcx693]

OK, but the way you're storing the data is in a very "spreadsheet"-like manner. It makes it difficult to extract data and perform calculations. How would you do something as simple as getting an average of the 10 scores? It's very difficult given your data structure.

In order to get meaningful results from Access with a manageable amount of effort, you need to structure your data a certain way. Else you might as well store your data in Access, but pull out a calculator do do your calculations.