Pass through Queries to Oracle

[WaterGeek]

I am trying to make a report in Access. All of the data is in an oracle database that I connect to through ODBC (instaclient 11_1, sorry I am not positive which version of oracle is used).

My finished report should provide a list of services provided by a company, there are 28 possible services and each company may offer one or many of the services. I want this to be displayed in the report as a group of labels with check boxes.

I have determined that the only way to get the report output I want is a query, or possibly temporary table, with one record for each company and one field for each of the 28 possible services. Each of the 28 fields would be yes/no or 0/1 to indicate if the service is offered. The data is in 3 tables:

1) companies (one to many with #2)
2) company to service assignemnets (many to one with #3)
3) Services

What I have done before is a series of select queries that output Company ID and SERVICE for each service type (in this case it would take 28 queries). The a final query to provide all services for each company. When I try this approach Access locks up.

So it seems my choices are either pass through queries to let the server do the work or VBA with temporary tables. I do not really like the idea of the temporary tables...but maybe that is the way to go.

I got a pass through query to work but as soon as I try to add an "IF THEN" or "CASE" statement I get the error: "FROM Keyword not found where expected". Here is the SQL:

SELECT
TINWSYS.NUMBER0 AS PWSID,
TINWSSAA.TINSAT_CLASS_CODE AS ServiceAreaClass,


CASE TINSAT.NME As ServiceAreaName
WHEN "RA" THEN "Residential Area"
ELSE TINSAT.NME
END

TINWSSAA.PRIMARY_IND_CD AS ServiceAreaPrimary

FROM
TINWSYS INNER JOIN
(TINSAT INNER JOIN TINWSSAA ON

(TINSAT.TINSAT_CLASS_CODE = TINWSSAA.TINSAT_CLASS_CODE) AND
(TINSAT.TINSAT_ST_CODE = TINWSSAA.TINSAT_ST_CODE) AND
(TINSAT.TINSAT_NAME_CODE = TINWSSAA.TINSAT_NAME_CODE)) ON

(TINWSYS.TINWSYS_ST_CODE = TINWSSAA.TINWSYS_ST_CODE) AND
(TINWSYS.TINWSYS_IS_NUMBER = TINWSSAA.TINWSYS_IS_NUMBER);


So here are the questions:


1) What is the proper syntax for the CASE (IF THEN is fine too since I do not think I will need multiple cases)?

2) Can I nest multiple select statements into one pass through query? -- My concern is that If a pass through query calls 28 sub queries, should the sub queries be part of the same sql query or can they be seperate pass throughs in access?

3) Should I just go with the temporary tables? I am getting pretty confortable with VBA but it just seems more efficient to run select queries rather than waiting to generate temporary tables each time the report is run.

Thanks in advance.

 

[PHV]

Have a tried an access crosstab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WaterGeek]

I did try a cross tab query, but I kept getting error messages saying that I needed to do a calculation on each of the fields. When I searched on-line for cross tab info all the examples were of summations or averages so I did not think it would work for me.

 

[PHV]

You may use the Count aggregate function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WaterGeek]

PH - the count aggregate lead me to the sum which seems to be the soultion:

1) a select query with one expression field for each possible service returning a 0 if the service is not offered, 1 if it is

2) a sum query, grouping on company id and adding all service fields

I think it can be done in one query but not sure about that yet...and I do not think this query is too much for access.

For some reason I was making it out be much harder than it was...lots of beer last night must of helped!


(it was needed after pulling my hair out all day yesterday and some the day before)

I also think I figured out the pass through sql in case anyone is interested:

SELECT
TINWSYS.NUMBER0 AS PWSID,
TINWSSAA.TINSAT_CLASS_CODE AS ServiceAreaClass,


CASE
WHEN TINSAT.NME = 'RESIDENTIAL AREA' THEN 'It Worked'
ELSE TINSAT.NME
END
AS MyAlias,

TINWSSAA.PRIMARY_IND_CD AS ServiceAreaPrimary

FROM
TINWSYS INNER JOIN
(TINSAT INNER JOIN TINWSSAA ON

(TINSAT.TINSAT_CLASS_CODE = TINWSSAA.TINSAT_CLASS_CODE) AND
(TINSAT.TINSAT_ST_CODE = TINWSSAA.TINSAT_ST_CODE) AND
(TINSAT.TINSAT_NAME_CODE = TINWSSAA.TINSAT_NAME_CODE)) ON

(TINWSYS.TINWSYS_ST_CODE = TINWSSAA.TINWSYS_ST_CODE) AND
(TINWSYS.TINWSYS_IS_NUMBER = TINWSSAA.TINWSYS_IS_NUMBER);