Hi I have a query with these columns...

PersonsName, YearNumber: "2012"

...and this gives

John, 2012
James, 2012
Jim, 2012

How can I make it so the query gives an extra row for each person so the results will look like...

John, 2011
John, 2012
James, 2011
James, 2012
Jim, 2011
Jim, 2012

Many Thanks

Is this for a query only, or do you want to add additional rows to the table?

If it's for a query only, create a second table that has one row for each year you want to display. Let's call that table WhichYears, with a field YearNumber:



Create a query that joins your given table (let's call it MyTable) to the WhichYears table. You'll want to use a Left Join (or Right Join) when joining the tables. If you're doing this through the Query Design tool, ensure there's a line joining the two tables on the YearNumber field. (If there isn't, add one!) Right-click on that line to highlight it, and choose Join Properties. You'll have three choices:

1. Only include rows where the joined fields from both tables are equal.
2. Include ALL records from 'MyTable' and only those records from 'WhichYears' where the joined fields are equal.
3. Include ALL records from 'WhichYears' and only those records from 'MyTable' where the joined fields are equal.

You'll want the one which includes ALL records from WhichYears (the highlighted one above, which, depending on how you constructed the query in the first place, could be option 2, not option 3 as above!). Once you've done that, the line connecting the two tables should change to have an arrow head at the MyTable end.

Drag PersonsName from MyTable into the grid. Drag YearNumber from WhichYears into the grid.

The SQL for your query should look something like:



Run the query. Is that what you want?

Thanks for that, yep that will work.

I was just hoping there would be a simple way with a command i didnt know of!

thanks so much for your detailed reply explaining