referencing Queries

[Sheba]

hi,

Could someone advise how to reference a query from another query? I have this query (Query1) which gives me the total number of records that answer "Yes" to the question "Were criteria met?"

Query1
IsCriteria: Sum(IIf([table1].[CriteriaMet]="Yes",1,0))

another question was posed "So if criteria were met, was oxygen administered?" I ran a similar query which gives me the total number of cases where Oxygen was given and called the second query, Query2.

Now from within Query2, I want to also calculate the percentage of the value returned, based on records which met the Criteria.

Using the formula: 100 * ([oxyGiven]/[Query1].[IsCriteria]). It is asking me to "Enter Parameter value"


Your help is greatly appreciated.

[June7]

Use query objects just like tables.

Is that expression in a textbox on form or report? Cannot reference table and query objects directly that way. The table/query must be included in the form/report RecordSource so the necessary fields will be available.

[Sheba]

Thank you June7. I started afresh and created a new Report using "Report Design", taking care to include all the queries as the control source. The report put all the labels in the Header section and the text boxes in the detail section. I didn't like the design so I cut the controls from the Header section and pasted them in the detail section. I have spent much time on the design. I have a Tab control where each page is dedicated to fields a query. Initially I received an error message that a field from a query could possibly be from more than one table so I went into the SQL view of the combined query and specified table.queryname in the FROM clause. Now when I try to run the report, it says "Could not find file c:\users\ddew\documents\table.mdb"!


1) "table" is not mdb
2) the database is split with the backend on a network drive

I appreciate any help you can provide on this

[June7]

A Tab control is not useful on a report.

Don't include the word 'table' as a prefix in the field reference.

Instead of trying to edit field name in query SQL View, why not use Design View and select field from dropdown list in the grid?

Post the complete SQL statement of the report RecordSource.

[Sheba]

hi June,

thanks for getting back to me. I didn't use the reserved word "table" in the actual statement. Just wanted to show what I did. I created the query through query design but when I got an error that a specified field could refer to more than one table listed in the FROM clause, I went into the SQL view and added table name to each query in the FROM clause. Then it told me it couldn't find C:\<tablename>.mdb. Have pretty much been stuck there since then

The SQL statement is below:

SELECT mainQuery.IsCriteria, mainQuery.Targeted_history, mainQuery.TargetPercentage, mainQuery.Cardiac_exam, mainQuery.CardiacPercentage, mainQuery.[12_Lead_ECG], mainQuery.[12LeadPercentage], oxyQuery.Oxygen_Indication, oxyQuery.HowMany, oxyQuery.Not_GivenO2, oxyQuery.IsCriteria, NitroQuery.NitroG_Indicated, NitroQuery.nitro_chestPain, NitroQuery.notIndicated_hypoT, NitroQuery.landEMS, NitroQuery.airMedic, NitroQuery.sendingFacl, opiateQuery.IndicatedANDgiven, opiateQuery.IndicatedNOTGiven, opiateQuery.NotIndicatedGiven, opiateQuery.NotIndicatedNotGiven, antiPlteQuery.IndicatedANDgiven, antiPlteQuery.IndicatedNOTGiven, antiPlteQuery.IndicatedBUTblank, antiPlteQuery.NotIndicatedGiven, antiPlteQuery.NotIndicatedNotGiven, anticoagQuery.antiCoagulantINDICATED, anticoagQuery.IndicatedANDgiven, anticoagQuery.IndicatedNOTGiven, anticoagQuery.IndicatedBUTblank, anticoagQuery.NotIndicatedGiven, anticoagQuery.NotIndicatedNotGiven, ASAQuery.ASA_Given, ASAQuery.IsCriteria, ASAQuery.asaPercentage, ASAQuery.emsASA, ASAQuery.airASA, ASAQuery.sendingASA, pciQuery.PCI_Indicated, pciQuery.PCI_NOTIndicated, pciQuery.PCI_Undetermined, pciQuery.IndicatedANDdone, pciQuery.IndicatedNOTdone, pciQuery.PCI_in_90minutes, pciQuery.NotIndicatedDone, pciQuery.IndicatedBUTblank, pciQuery.NotIndicatedNotDone, pciQuery.NotIndicatedUndetermined, pciQuery.PCI_STEMI, pciQuery.PCI_Fibrinolysis, pciQuery.Cardio_shock, pciQuery.PCI_NOT_90minutes
FROM anticoagQuery, oxyQuery, QueryTime, opiateQuery, NitroQuery, mainQuery, ASAQuery, antiPlteQuery, pciQuery;

[June7]

Is this Query1?

There is no JOIN or WHERE clause. This results in a Cartesian relation of records. Each record in each table/query will associate with each record in other tables/queries. A Cartesian relation does have uses but don't think it is a good idea in this case.

[Sheba]

hey June what I did was, use Report design and in specifying the record source, indicated the fields (plus queries) involved. Not a good way to do it...?

[Sheba]

no not query1. more like the mega query - combination of many queries

[Sheba]

I have changed it into a number of SQL UNIONs but received the error message "Syntax error in FROM clause"

SELECT IsCriteria, Targeted_history, TargetPercentage, Cardiac_exam, CardiacPercentage, [12_Lead_ECG], [12LeadPercentage]
FROM mainQuery UNION
SELECT Oxygen_Indication, HowMany, Not_GivenO2, IsCriteria
FROM oxyQuery UNION
SELECT NitroG_Indicated, nitro_chestPain, notIndicated_hypoT, landEMS, airMedic, sendingFacl
FROM NitroQuery UNION
SELECT IndicatedANDgiven, IndicatedNOTGiven, NotIndicatedGiven, NotIndicatedNotGiven
FROM opiateQuery UINON
SELECT IndicatedANDgiven, IndicatedNOTGiven, IndicatedBUTblank, NotIndicatedGiven, NotIndicatedNotGiven
FROM antiPlteQuery UNION
SELECT antiCoagulantINDICATED, IndicatedANDgiven, IndicatedNOTGiven, IndicatedBUTblank, NotIndicatedGiven, NotIndicatedNotGiven
FROM anticoagQuery UNION
SELECT ASA_Given, IsCriteria, asaPercentage, emsASA, airASA, sendingASA
FROM ASAQuery UNION
SELECT PCI_Indicated, PCI_NOTIndicated, PCI_Undetermined, IndicatedANDdone, IndicatedNOTdone, PCI_in_90minutes, NotIndicatedDone, IndicatedBUTblank, NotIndicatedNotDone, NotIndicatedUndetermined, PCI_STEMI, PCI_Fibrinolysis, Cardio_shock, PCI_NOT_90minutes
FROM pciQuery;

[June7]

Each SELECT line must have the same number of fields. The first SELECT line sets the field name and data type. If a SELECT does not have the corresponding fields then provide an alternate value such as "" or 0 or "NA" or Null or #1/1/1900#, depending on the type of data you want in the field, so that like data lines up.

Why would you use UNION query? The mainQuery data is completely different from the data in the other queries.

Do the other queries have a relationship to mainQuery?

[Sheba]

Thanks June for your feedback. Each query is supposed to analysed a different set of data from the other query but all the data is obtained from one table. The fields in each query and possibly data types are different. Even then, I need ALL the info that these queries provide to generate my report - one report. I know that there is a smart efficient way to go about this, just not figured it out

[Sheba]

to answer your last question:
not necessarily. mainQuery is the record source for my report and thus, just a combination of all the individual queries

[June7]

I am lost. If mainQuery is the RecordSource of report and is a combination of individual queries why do you show it included in another query with all the individual queries?

Why do you need individual queries to analyse data, why not all in one query?

I don't understand your data and the analysis you want to do.


BTW, could you use a slightly larger font in your posts?

[Sheba]

a thousand apologies June mainQuery is not the recordsource of the report. Sorry for the confusion. It is one of the individual queries. The recordsource is called QueryRpt.

I decided on individual queries for performance reasons. I reckoned that if I needed to get just one particular information, I could run that query instead of ALL of them. Second reason is, if I specified ALL the fields in one query, I'll have more than 30 columns, with each column calculating something...

Could you confirm whether this is the better way to represent the query please? The way I've done it hasn't worked for me so far.

[June7]

If you have individual queries, how do you plan to build reports? A separate report object for each query? But if you want all this data on one report, then have to recombine. Not making sense to me.

If at all possible, I would use one query and apply filter criteria as needed. But maybe that is slower performance than what you have. Would have to test.