Is crosstab the answer?

  • Thread starter Thread starter neil40
  • Start date Start date
N

neil40

I have 3 tables in my DB

1 is a list of top 10 results from events

1 is a list of retirements from those events

1 is a list of placings outside of the top 10

I gave similar field names (Driver, DrNat, Car etc...)

I want to create a report that is an individuals record of events, thus
combining the 3 tables.
Is Crosstab query the way to go?
It confuses me as there is only limited fields to include from what I
can make out.

Advice welcomed

Neil
 
We don't know what your data looks like or what you want your results to
look like.

If your tables are similar in structure, is there a good reason why you
didn't use a single table?
 
Does it matter what the data looks like, other than I can tell you each
table follows the same format for each field?

The separate tables are just a historic thing I guess, not sure why
now.
Logically, they could be all one table, but with all the reports and
queries built over time, I'm reluctant to alter it right now because of
the mass of data (22 years of results x approx 15 events per year),
hence me looking for an idea to combine the 3 tables in 1 query to get
my report.

Neil
 
Does it matter what the data looks like, other than I can tell you each
table follows the same format for each field?

The separate tables are just a historic thing I guess, not sure why
now.
Logically, they could be all one table, but with all the reports and
queries built over time, I'm reluctant to alter it right now because of
the mass of data (22 years of results x approx 15 events per year),
hence me looking for an idea to combine the 3 tables in 1 query to get
my report.

Neil
Click to expand...

PMJI, but if you need a single report from the three tables and they all
have the same structure, what you want is a UNION query.

SELECT * from Table1
UNION
SELECT * from TAble2
UNION
SELECT * from Table3

Even if the tables are not identical but have common fields, you could use

SELECT Field1, Field2, Field3
FROM Table1
UNION
SELECT Field1, Field2, Field3
FROM Table2
UNION
SELECT Field1, Field2, Field3
FROM Table3

Hope this is of some help,
Randy Harris
 
And if the names are not identical and the report needs to
know which table the records cam from:

SELECT "top 10" As Tb, Field1, Field2, Field3
FROM Table1
UNION
SELECT "retirements ", FieldA, FieldB, FieldC
FROM Table2
UNION
SELECT "placings ", FieldX, FieldY, FieldZ
FROM Table3
 
Marshall said:
And if the names are not identical and the report needs to
know which table the records cam from:

SELECT "top 10" As Tb, Field1, Field2, Field3
FROM Table1
UNION
SELECT "retirements ", FieldA, FieldB, FieldC
FROM Table2
UNION
SELECT "placings ", FieldX, FieldY, FieldZ
FROM Table3
Click to expand...

And you might consider using this Query (inside a Make-Table Query) to
create a combined Table to replace the others you're currently using.
If you did that, and until you update the objects that use the old
Tables, you could define Queries with the same names as the old Tables
that get their data from the new combined Table. It would take a bit of
work, but doing this might make your database maintenance a bit easier
in the future -- fewer Tables to deal with.

(Oh, yes, be sure to back up your database first!)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Thanks folks, some great thoughts.
I like the last one especially, as I've been mulling over the thought
recently of doing some rationalisation.

Thanks again.
Neil
 
While "rationalisation" is a nice, big word, in database
speak the correct word is "Normalization" , or rather
"Normalisation" <gdr>

Actually, that may make a difference when you are searching
Help or the web for more information.
 
Back
Top