Repeat entries in base query.

eland · General User Joined: 07 May 2006 Posts: 5

I'm querying a base database and dropping the query into calc to analyse the results.

Sometimes I want one of the individual query results to appear twice in the calc sheet.

However if I duplicate a criteion entry when making a query using design view the results still only appears once.

Does anyone know of a clever way around this problem??

TIA.

DrewJensen · Posted: Mon Oct 09, 2006 11:15 am Post subject:

I'm not sure I follow totally.

Do you just want the same date repeated in multiple columns?

If that is it, then simply double click on the column name more then once. Each time you do so the column will be added to the result set with a slightly different alias name.

Let's say you have a column in the table named "Val". The first time it is added to the result set it is named "Val", the second time it is named "Val1", the third time "Val2" and so on.

Now - if that is not what ou want but rather you want values from a tables column returned in seperate columns in the result set, and each of these column have some different criterian then you will need to use more then one query and join them together.

If that is what you are looking for let me know and I can show you an example.

Drew

eland · General User Joined: 07 May 2006 Posts: 5

Hi Drew,
I thought I was making a bit of a dog's dinner of my question Smile

.

So perhaps an example.

I'm running a sports league and I need to record the points scored by a team's opponents throughout the season.(Teams don't play everyone so we do a crude strength of schedule adjustment).

Say Team A has played Team B and Team C,then querying the total points scored by teams B and C is easy(even for me).

But say Team A then plays Team B again.

if I have a query with Team B,Team C and Team B(again) when I chuck the query into a calc spreadsheet Team B's numbers only appears once.

I want something like.

Team.....Points scored
Team B......40
Team C......36
Team B......40

but I get

Team B.....40
Team C.....36

I'm currently just cutting and pasting Team B's numbers and tacking them onto the bottom of the column,but as teams play more repeats it's easy to miss one.

Great job BTW.

Eland.

DrewJensen · Posted: Tue Oct 10, 2006 2:52 pm Post subject:

alright, well this would the point where it would help to see the actual Base table strucures you are building the query against.

Depending on how those tables look will dictate the query - now that is a redundant statement if ever there was one.