A Complicated Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
*
 
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)
 
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
 
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
*
 
Back
Top