How to create a query that will return exactly 10 records

[Caber27]

I am trying to do something a little out of the ordinary in Microsoft Access.

I need to create a table from a query that contains exactly 10 results.

the problem is that sometimes the query returns less than 10 results and a simple maketable will only write as many records as it finds.

What I need to do is if a query only returns (say) 6 results, i need to fill 4 records with null records.

(What I am doing is creating a list of football players who have scored a goal in a game - ie. criteria in query is PlayerGoals Is Not Null)

I realise that it is not correct to write query results to a table but I am accessing the database with a broadcast graphic system that can only reference tables in a database (not queries)

I hope I have explained that well enough ! I am a newbie at database design.

[Stewart Ross]

Hi. I do not know how many columns there are in the player table you want to output - so in the example below there are just two columns, the player name and the number of goals. A dummy table with ten rows is needed, which has the same number of columns as your output table.

Start by creating a dummy table called NO PLAYERS. In my example the fields are PLAYER NAME of type text, and GOALS of type Number. Save the table - no need for a primary key.

Add ten rows to the table, leaving the player names field blank and entering 0 as the number of goals for each row.

I have assumed that your query that selects the players is called PLAYERS. If it is not, change the name to whatever it should really be in the query below.

The following make table query selects ten player names from the PLAYERS query unioned with the dummy NO PLAYERS table makes a new table called TOPGOALS.
[code=sql]SELECT TOP 10 * INTO TOPGOALS
FROM (SELECT * FROM [PLAYERS] UNION ALL SELECT * FROM [NO PLAYERS]) AS [ALL PLAYERS];
[/code]

Sample data from a table PLAYERS substituting for your query:

Player Name......Goals
Flintstone, F........1
Pan, P...............2
Mouse, M.............1
Duck, D..............3
Rubble, B............4

Result of make table query:

Player Name......Goals
Flintstone, F........1
Pan, P...............2
Mouse, M.............1
Duck, D..............3
Rubble, B............4
.....................0
.....................0
.....................0
.....................0
.....................0

-Stewart

[Caber27]

Stewart,

I have tried your script and with a little tweaking it works perfectly.

I had to add another column to the query and fill in the dummy table values in this column so the Goals column would be completely blank (ie. not 0)

Your short paragraph reply has taught me plenty about programming in access.

Your timing is perfect too as I am 24 hours away from needing the database fully functional...

Thankyou