 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006A Complicated Query
Here's what's going on: Table A has a series of records. Table B contains another series of records. I want to query all the records in Table A. However, if Table B contains the Unique ID of one of the records in Table A, some of the data in Table B will replace some of that record displayed in Table A. I can (obviously) display all the records in Table A. I can also already display all the records that match between Table A and Table B, and replace the data the way I want. The hard part is making both display together. I could, of course, use a union query, but then I only want to show the records from Table A that are not linked to in Table B. Does that make any sense? Any ideas on how to get this to work? Thanks! Dustin Hi Dustin, you will need to do something like this in each field where the swap can happen: Fieldname_: iif(isnull(B.Fieldname), nz(A.Fieldname), B.Fieldname) notice the underscore at the end of the column label ... this is because the column label for a calculated field must be unique... Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * > Here's what's going on: > [quoted text clipped - 14 lines] > > Dustin I would test to see if the relating field (Unique id) in B is not null. Then replace the A.FieldName value with the B.FieldName value. That takes care of the case where the B.Field is null and the corresponding A.Field has a value. IIF(B.RelatingField is Not Null, B.FieldName,A.FieldName) > Hi Dustin, > [quoted text clipped - 34 lines] >> >> Dustin Okay, I am trying the following code: TITLE: IIF(tblEstimateDetail.ED_Primavera_ID Is Not Null, tblEstimateDetail.ED_Title, tblPrimaveraDetail.PD_ActivityDescription) It comes up with the error: "The expression you entered contains invalid syntax." Ideas? Thanks! Dustin Okay, figured that out... Now I have the query, but it still only returns the records that are linked to in Table B. SELECT IIf([tblEstimateDetail].[ED_Primavera_ID] Is Not Null,[PD_ActivityID],[PD_ActivityID]) AS ACT, (I want this field to always be the same.) IIf([tblEstimateDetail].[ED_Primavera_ID] Is Not Null,[tblEstimateDetail].[ED_Title],[tblPrimaveraDetail].[PD_ActivityDescription]) AS TITLE, IIf([tblEstimateDetail].[ED_Primavera_ID] Is Not Null,[ED_Qty],[PD_BudgetQuantity]) AS BQ, IIf([tblEstimateDetail].[ED_Primavera_ID] Is Not Null,[ED_LaborUnitRate],[PD_BudgetCost]) AS BC, IIf([tblEstimateDetail].[ED_Primavera_ID] Is Not Null,[Rate_Desc],[PD_Resource]) AS RES (Yes there is a third table, so that if there is a link, the data comes from Table C instead of Tables A or B. Is this messing me up?) FROM (tblEstimateDetail INNER JOIN tblPrimaveraDetail ON tblEstimateDetail.ED_Primavera_ID = tblPrimaveraDetail.PD_ActivityID) INNER JOIN tblLaborRates ON tblEstimateDetail.ED_Rate_ID = tblLaborRates.Rate_ID WHERE (((tblPrimaveraDetail.PD_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID])); Thanks! Dustin I guess what I really need is a query that simply prints each record in Table A IF there are no links to the record in Table B. Boy...this seems a lot more complicated than it should be... Dustin Unfortunitely, as you'll see in the post below, that's not really working...it's still only printing the records that have matching records in Table B. Is there any way just to query and select the records in Table A that do not have a matching record in Table B? Dustin > Hi Dustin, > [quoted text clipped - 34 lines] > > > > Dustin Okay, I got it... ...if I click on "Create Query", and click on the Wizard, there is a type of query that does exactly what I want it to do. Now I feel silly... Thanks for all the help! Dustin Hi Dustin, happy you got it! wish you continued success with you database ;) Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote programming and Training strive4peace2006 at yahoo.com * > Okay, I got it... > [quoted text clipped - 6 lines] > > Dustin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.