Subquery with a query that references another query?

[decimal48]

edit: I'm sorry I cant figure out how to edit the title but that should read "query with a subquery that references another query"

I'm having an issue here and first I'm not sure if its possible, second I'm not sure what I've done wrong.

I work for a residential home builder and I'm making a query to pull information about each unit into a line so I can display each record in a form and print the record. One of the things I need to pull, I was hoping to write a subquery that references another query where all of this has been figured out. So first question, can I reference columns in a query like I do for tables? ie:

SELECT queryname.columnName FROM queryname

Second, I wrote it out like the above, and I'm getting an error I dont understand. So the universal key for all the units we sell is UnitInformation.ID. I've selected that column in the the main query. I also used it in the subquery. The SELECT statement above also included

WHERE subqueryname.UnitID=UnitInformation.ID AND FeeID=1

. The objective here being to select the row related to a specific fee for whichever unit the row is about. For some reason I'm getting the error where a dialogue box comes up to ask me to enter the value of UnitInformation.ID. I dont understand why its not auto-populating that value.

[orange]

I recommend you tell us more. Start with a 30000 ft overview of the issue you are trying to automate - in plain English.
Attach a copy of the database with instructions to repeat the issue.

[ano]

a popup means ur spelling is wrong or ur query is not complete.
i see 3 tables? are they joined all? or is 1 table used as field in the select?

[decimal48]

I recommend you tell us more. Start with a 30000 ft overview of the issue you are trying to automate - in plain English.
Attach a copy of the database with instructions to repeat the issue.

OK the objective of the Parent Query (lets call it) is to be opened with forms and I can print an individual record from the forms to send to a lawyer on closing with all of our figures. One of the sets of extra numbers are extra charges (sort of like fines) the customer has incurred. Examples of these fines are: if we need to print and send them their agreement again, or if we're required to have our lawyer contact their lawyer.

So what I want to do is have each of the 5 types of extra charges show up on this paperwork for the lawyer, and the total cost of that extra charge.

Those extra charges are currently entered into a form where you just say the jobsite, the unit number, the type of fee, and the date it was incurred. Then there's another query that groups those charges by the universal key assigned to each unit we sell > the ID of the fee, and then I count the number of occurences for each charge, grab the price of that charge, and then use those two things to get a line-item total for that specific fee for that specific unit. Then what I want to do is use a subquery in the parent query to grab that line item total number, bring it back to the parent query, and then display the totals for ex. fee 1, and then fee 2, and then fee 3.

edit: some extra information

(SELECT FeeLineItemTotal FROM ExtraFeesQuery WHERE ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) AS LateDesignCentreCharge

is the subquery as its written. UnitInformation.ID is already being selected in the parent query, so I was thinking that the subquery should just grab that value from the parent query

[ano]

i not see a join (old/new) u have to put whole query to understand what u try to do

[decimal48]

SELECT UnitInformation.ID,
UnitInformation.UnitNumber, 
UnitInformation.[HomeownerName 1], 
UnitInformation.[HomeownerName 2], 
UnitInformation.Jobsite, 
UnitInformation.JobsiteID, 
UnitInformation.Phase, 
UnitInformation.OccupancyDate, 
UnitInformation.PurchasePrice, 
(ChangeOrders.CO1TotalCost+ChangeOrders.CO2TotalCost+ChangeOrders.CO3TotalCost+ChangeOrders.CO4TotalCost+ChangeOrders.CO5TotalCost)  AS ExtraConsideration, 
(ChangeOrders.CO1TotalCost+ChangeOrders.CO2TotalCost+ChangeOrders.CO3TotalCost+ChangeOrders.CO4TotalCost+ChangeOrders.CO5TotalCost+WaterMeterFee+HydroFee+LPICFee+TarionFee+TarionHST)  AS LawyerExtraConsideration, 
(UnitInformation.PurchasePrice+ExtraConsideration) AS AgreedSalePrice, 
(UnitInformation.PurchasePrice+LawyerExtraConsideration) AS LawyerAgreedSalePrice, 
IIf(AgreedSalePrice>484500,(AgreedSalePrice+24000)/1.13,IIf(AgreedSalePrice>424850,(AgreedSalePrice+52350)/1.193,IIf(AgreedSalePrice>368200,(AgreedSalePrice+28350)/1.133,AgreedSalePrice/1.052)))  AS NetSalePrice, 
IIf(LawyerAgreedSalePrice>484500,(LawyerAgreedSalePrice+24000)/1.13,IIf(LawyerAgreedSalePrice>424850,(LawyerAgreedSalePrice+52350)/1.193,IIf(LawyerAgreedSalePrice>368200,(LawyerAgreedSalePrice+28350)/1.133,LawyerAgreedSalePrice/1.052)))  AS LawyerNetSalePrice, 
(NetSalePrice*0.05) AS FederalHST, 
(NetSalePrice*0.08) AS ProvincialHST, 
(LawyerNetSalePrice*0.05) AS LawyerFederalHST, 
(LawyerNetSalePrice*0.08) AS LawyerProvincialHST, 
IIf(NetSalePrice<=368200,(NetSalePrice*0.05)*0.36,6300*((450000-NetSalePrice))/100000) AS RoughHousingRebate, 
IIf(RoughHousingRebate<=0,0,RoughHousingRebate) AS FinalHousingRebate, 
IIf(LawyerNetSalePrice<=368200,(LawyerNetSalePrice*0.05)*0.36,6300*((450000-LawyerNetSalePrice))/100000)  AS LawyerRoughHousingRebate, 
IIf(LawyerRoughHousingRebate<=0,0,LawyerRoughHousingRebate) AS LawyerFinalHousingRebate, 
((NetSalePrice*0.08)*0.75)  AS RoughOntarioRebate,  IIf(RoughOntarioRebate<24000,RoughOntarioRebate,24000) AS  FinalOntarioRebate, 
((LawyerNetSalePrice*0.08)*0.75) AS LawyerRoughOntarioRebate, 
IIf(LawyerRoughOntarioRebate<24000,LawyerRoughOntarioRebate,24000) AS LawyerFinalOntarioRebate, 
Val(DLookUp("Fee","TarionFees",NetSalePrice & "Between TarionFees.LowEnd AND TarionFees.HighEnd")) AS TarionFee, 
(TarionFee*0.13) AS TarionHST, 
Val(DLookUp("WaterMeter","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS WaterMeterFee, 
Val(DLookUp("Hydro","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS HydroFee, 
Val(DLookUp("LPIC","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS LPICFee, 
Val(DLookUp("Development","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS DevelopmentFee, 
UnitInformation.[Deposit#1Amount],  UnitInformation.[Deposit#2Amount], UnitInformation.[Deposit#3Amount],  UnitInformation.[Deposit#4Amount], 
ChangeOrders.CO1TotalCost, 
ChangeOrders.CO2TotalCost, 
ChangeOrders.CO3TotalCost, 
ChangeOrders.CO4TotalCost, 
ChangeOrders.CO5TotalCost, 
ChangeOrders.CO1AmntReceived, 
ChangeOrders.CO2AmntReceived, 
ChangeOrders.CO3AmntRecd, 
ChangeOrders.CO4AmntRecd, 
ChangeOrders.CO5AmntRecd, 
(CO1AmntReceived+CO2AmntReceived+CO3AmntRecd+CO4AmntRecd+CO5AmntRecd) AS ChangeOrderDeposits,
(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) AS  LateDesignCentreCharge
FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

Is the entire code. The relevant lines I would say are

SELECT UnitInformation.ID,
(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) AS  LateDesignCentreCharge
FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

[ano]

SELECT UnitInformation.ID,
UnitInformation.UnitNumber, 
UnitInformation.[HomeownerName 1], 
UnitInformation.[HomeownerName 2], 
UnitInformation.Jobsite, 
UnitInformation.JobsiteID, 
UnitInformation.Phase, 
UnitInformation.OccupancyDate, 
UnitInformation.PurchasePrice, 
(ChangeOrders.CO1TotalCost+ChangeOrders.CO2TotalCost+ChangeOrders.CO3TotalCost+ChangeOrders.CO4TotalCost+ChangeOrders.CO5TotalCost)  AS ExtraConsideration, 
(ChangeOrders.CO1TotalCost+ChangeOrders.CO2TotalCost+ChangeOrders.CO3TotalCost+ChangeOrders.CO4TotalCost+ChangeOrders.CO5TotalCost+WaterMeterFee+HydroFee+LPICFee+TarionFee+TarionHST)  AS LawyerExtraConsideration, 
(UnitInformation.PurchasePrice+ExtraConsideration) AS AgreedSalePrice, 
(UnitInformation.PurchasePrice+LawyerExtraConsideration) AS LawyerAgreedSalePrice, 
IIf(AgreedSalePrice>484500,(AgreedSalePrice+24000)/1.13,IIf(AgreedSalePrice>424850,(AgreedSalePrice+52350)/1.193,IIf(AgreedSalePrice>368200,(AgreedSalePrice+28350)/1.133,AgreedSalePrice/1.052)))  AS NetSalePrice, 
IIf(LawyerAgreedSalePrice>484500,(LawyerAgreedSalePrice+24000)/1.13,IIf(LawyerAgreedSalePrice>424850,(LawyerAgreedSalePrice+52350)/1.193,IIf(LawyerAgreedSalePrice>368200,(LawyerAgreedSalePrice+28350)/1.133,LawyerAgreedSalePrice/1.052)))  AS LawyerNetSalePrice, 
(NetSalePrice*0.05) AS FederalHST, 
(NetSalePrice*0.08) AS ProvincialHST, 
(LawyerNetSalePrice*0.05) AS LawyerFederalHST, 
(LawyerNetSalePrice*0.08) AS LawyerProvincialHST, 
IIf(NetSalePrice<=368200,(NetSalePrice*0.05)*0.36,6300*((450000-NetSalePrice))/100000) AS RoughHousingRebate, 
IIf(RoughHousingRebate<=0,0,RoughHousingRebate) AS FinalHousingRebate, 
IIf(LawyerNetSalePrice<=368200,(LawyerNetSalePrice*0.05)*0.36,6300*((450000-LawyerNetSalePrice))/100000)  AS LawyerRoughHousingRebate, 
IIf(LawyerRoughHousingRebate<=0,0,LawyerRoughHousingRebate) AS LawyerFinalHousingRebate, 
((NetSalePrice*0.08)*0.75)  AS RoughOntarioRebate,  IIf(RoughOntarioRebate<24000,RoughOntarioRebate,24000) AS  FinalOntarioRebate, 
((LawyerNetSalePrice*0.08)*0.75) AS LawyerRoughOntarioRebate, 
IIf(LawyerRoughOntarioRebate<24000,LawyerRoughOntarioRebate,24000) AS LawyerFinalOntarioRebate, 
Val(DLookUp("Fee","TarionFees",NetSalePrice & "Between TarionFees.LowEnd AND TarionFees.HighEnd")) AS TarionFee, 
(TarionFee*0.13) AS TarionHST, 
Val(DLookUp("WaterMeter","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS WaterMeterFee, 
Val(DLookUp("Hydro","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS HydroFee, 
Val(DLookUp("LPIC","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS LPICFee, 
Val(DLookUp("Development","ClosingFees","JobsiteID="  & UnitInformation.JobsiteID & " AND Phase='" &  UnitInformation.phase & "'")) AS DevelopmentFee, 
UnitInformation.[Deposit#1Amount],  UnitInformation.[Deposit#2Amount], UnitInformation.[Deposit#3Amount],  UnitInformation.[Deposit#4Amount], 
ChangeOrders.CO1TotalCost, 
ChangeOrders.CO2TotalCost, 
ChangeOrders.CO3TotalCost, 
ChangeOrders.CO4TotalCost, 
ChangeOrders.CO5TotalCost, 
ChangeOrders.CO1AmntReceived, 
ChangeOrders.CO2AmntReceived, 
ChangeOrders.CO3AmntRecd, 
ChangeOrders.CO4AmntRecd, 
ChangeOrders.CO5AmntRecd, 
(CO1AmntReceived+CO2AmntReceived+CO3AmntRecd+CO4AmntRecd+CO5AmntRecd) AS ChangeOrderDeposits,
(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) AS  LateDesignCentreCharge
FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

Is the entire code. The relevant lines I would say are

SELECT UnitInformation.ID,
(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) AS  LateDesignCentreCharge
FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

do i understand well that you get a popup " UnitInformation.ID " ? that should mean there is a type error .
pity i can not test the relevant lines , wich seems correct to me just test. run the relevant lines without the field query , if ok then run only the field query (should give a popup) fill in an id in the popup

[decimal48]

No the error I'm getting is "Enter Parameter Value: UnitInformation.ID". I checked for the type error, and UnitInformation.ID is autonumber, and the formatting for extrafeesquery.unitID are all number formats so it looks like thats also coming through as a number

[ano]

did u do the testing ?

[decimal48]

I do not know what the testing refers to

[ano]

test 1 has to be simple or popup which means wrong name

SELECT UnitInformation.ID FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

test 2 has to give popup fill in a valid #

SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1

test 3 has to be simple

SELECT UnitInformation.ID,(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

if this is all correct then add field line by line to find the problem in the long query

[decimal48]

test 1 has to be simple or popup which means wrong name

SELECT UnitInformation.ID FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

test 2 has to give popup fill in a valid #

SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1

test 3 has to be simple

SELECT UnitInformation.ID,(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1) FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

if this is all correct then add field line by line to find the problem in the long query

Thank you for all this work.
Tests 2 and 3 both produce the popup window

[ano]

Thank you for all this work.
Tests 2 and 3 both produce the popup window

did test 2 run well after you fill in a valid #?

next code is doing same as you try to do and run ok

SELECT [Annual Bonus].[Employee ID], [Salary Changes].[Person Number],
 (select name from test where testno=[Salary Changes].[Person Number]) AS namFROM [Salary Changes] INNER JOIN [Annual Bonus] ON [Salary Changes].[Person Number] = [Annual Bonus].[Employee ID];

as you see both have same structure , the [] are for space in the names

SELECT UnitInformation.ID,
(SELECT  FeeLineItemTotal FROM ExtraFeesQuery WHERE  ExtraFeesQuery.UnitID=UnitInformation.ID AND ExtraFeesQuery.FeeID=1)
 FROM UnitInformation INNER JOIN ChangeOrders ON UnitInformation.ID = ChangeOrders.UnitID;

if you not see the differences then upload your zipped database , its ok if the tables are empty with only 1 record in each relevant to each other

goto advance ; click attachment ; click browse and select th zip file ; click upload ; click save

[moke123]

is UnitInformation.ID a field on the form?

you may need to concatenate the query as such

ExtraFeesQuery.UnitID=  " &  Forms(Your Form Name).UnitInformation.ID & " AND ExtraFeesQuery.FeeID=1)

[ano]

is UnitInformation.ID a field on the form?

you may need to concatenate the query as such

ExtraFeesQuery.UnitID=  " &  Forms(Your Form Name).UnitInformation.ID & " AND ExtraFeesQuery.FeeID=1)

do not make sense bec test 1 works
but he can try : copy name from test 1 by selecting the whole name including the space before and after <cntrl>+c or menu/copy and then select same in test 2 but na <cntrl>+v or menu/paste (text) and do the same on test 3
ofcourse you save all and test again , if popup keep coming in 3rd test then upload your database i only need 1 record if contents is problem