469,133 Members | 984 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

How to create a query that will return exactly 10 records

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.
Mar 31 '08 #1
2 1637
Stewart Ross
2,545 Expert Mod 2GB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 10 * INTO TOPGOALS
  2. FROM (SELECT * FROM [PLAYERS] UNION ALL SELECT * FROM [NO PLAYERS]) AS [ALL PLAYERS];
  3.  
Sample data from a table PLAYERS substituting for your query:
Expand|Select|Wrap|Line Numbers
  1. Player Name......Goals
  2. Flintstone, F........1
  3. Pan, P...............2
  4. Mouse, M.............1
  5. Duck, D..............3
  6. Rubble, B............4
  7.  
Result of make table query:
Expand|Select|Wrap|Line Numbers
  1. Player Name......Goals
  2. Flintstone, F........1
  3. Pan, P...............2
  4. Mouse, M.............1
  5. Duck, D..............3
  6. Rubble, B............4
  7. .....................0
  8. .....................0
  9. .....................0
  10. .....................0
  11. .....................0
-Stewart
Apr 1 '08 #2
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
Apr 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

22 posts views Thread by Stan | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.