One records => Many Records UNION query

[crmarrio]

Hi,

I am trying to translate data from 3 linked Oracle tables to perform a Make Table query in access.

Each person has many registers for a particular year (average is about 12), each register contains 53 fields (one for each week).

I need to get the 53 fields (all equally set up) into 53 records. I can do this using a Union all pass through query.

e.g. SELECT Person, 1 as Week, Attendance_1 as Mark from Registers
UNION ALL SELECT Person, 2 as Week, Attendance_2 as Mark from Registers
etc...

However, when I want to then do a make table on the data, Access 2K hangs. I think this is because the pass through has generated about 5 million records.

At the moment I can't create a view (due to third-party oracle software house restrictions) and put indexes and the like on at the server end. Does anyone have any thoughts as to how I might get round this?

Any help much appreciated - but be warned my Oracle SQL is limited!

 

[Len Boorman]

Not an Oracle person myself so this is just a suggestion

Run your UNION query in Oracle, Use the results to make a temp table again in Oracle.

Then use Access pass through query to extract the records from the temp table.

Thinking behind this

Oracle has a bit more umph to do the UNION and Make Table crunching. You are only then using Access to do the relatively simple SELECT. If you are doing this over a network then hope that it is better than ours.

Len

 

[crmarrio]

Len,

Thanks for your reply - I would agree with what you are saying.

However, our Oracle system is provided under license and therefore we do not have the relevant permissions to create views, or make tables on the Oracle server.

Any further ideas appreciated...

Not an Oracle person myself so this is just a suggestion

Run your UNION query in Oracle, Use the results to make a temp table again in Oracle.

Then use Access pass through query to extract the records from the temp table.

Thinking behind this

Oracle has a bit more umph to do the UNION and Make Table crunching. You are only then using Access to do the relatively simple SELECT. If you are doing this over a network then hope that it is better than ours.

Len

 

[Len Boorman]

What about doing the UNION under Oracle and then doing an Access SELECT on the UNION.

Struggling.

Maybe someone over the pond will have some better help this afternoon

Len

 

[neileg]

I understand the Oracle licencing problems. It's effectively a runtime licence.

Can't you import the data into discrete Access tables and then use a union query there. You wouldn't need the make table action, then (perhaps?).

 

[crmarrio]

I have tried making tables locally and then performing the UNION from there.

However, Access seems to fall down at some point. There should in effect be 53 unions (one for each week in the year, plus a spare). Access gives the message 'query too complex'.

Is there a maximum number of UNIONS access can handle? Would it work to split them into four smaller UNIONS and then a UNION of UNIONS??? Or am I just getting too confused??

 

[Len Boorman]

Think you have got
an incorrect field and/or
field missing and/or
table name incorrect and/or
mix of data types for a field

These are generally the probs with UNIONS.

Check 1 and 2 first.

I normally create a select query and then copy and paste the SQL into my UNION. I can then check that the select does not fall over and also check the UNION as it is built.

If there is a limit I have not found it yet and would suspect it is quite high

Len

 

[Len Boorman]

I have tried making tables locally and then performing the UNION from there.

However, Access seems to fall down at some point. There should in effect be 53 unions (one for each week in the year, plus a spare). Access gives the message 'query too complex'.

Is there a maximum number of UNIONS access can handle? Would it work to split them into four smaller UNIONS and then a UNION of UNIONS??? Or am I just getting too confused??

Have just found this query too complex myself. Found the limitation on mine was 50 queries with 16 fields per query. You need to do it in two queries and then UNION the UNIONs as you state. I also found that it was better to take a healthy bite out of the query giving thje message otherwise the UNION of UNIONs came up with the too complex message.

Do not know what the limit is. Could be number of SELECT statements or fields or combination

Len B