No relationship betwwen 2 tables

[goslincm]

I need some help here. I have been asked to create a query that I'm not sure I can do it. Its working with 2 tables that are not related.

Table 1: Dbo_LocalUnitAudit
PK ID, other fields include: [CountyCd], [LocalUnitType],[LocalUnitCd], [FiscalYear], [AuditReceivedDate]

Table 2: Audit Reviews
PK DOES NOT HAVE ONE, other fields include: [CountyCd], [LocalUnitType], [LocalUnitCd], [DateCompleted], [FYE]

I'm being asked to show a list of those audits that have been received, but to which no DateComplated exist in the Audit Review table.

Am I making to much of this, but I'm having a very difficult time given this database has no relationship existing between them.

 

[randy700]

How about...

SELECT ID, Dbo_LocalUnitAudit.CountyCode, AuditReceivedDate
FROM Dbo_LocalUnitAudit, [Audit Reviews]
WHERE Dbo_LocalUnitAudit.CountyCode = [Audit Reviews].CountyCode 
AND IsNull(DateCompleted)

... note: not tested ...


Randy

 

[goslincm]

Negative, doesn't work

 

[straybullet]

Negative, doesn't work

Are you receiving an error message?

Also, did you adjust the references of "CountyCode" to be "CountyCd"?

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

What are the non-ambiguous values common to both tables when an audit is received and completed ?

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

 

[goslincm]

[Countycd], [LocalUnitType], [LocalUnitCd] are in both tables, and the ackward part is that dbo_localunitaudit the other pertinent field is [FiscalYear] which is a text field used for inputting a 4 digit year.

The Audit Reviews table is capturing the field [fye] which is a text field as well, capturing the fiscal year end date but in a xx/xx/xxxx format.

The dbo_localunitaudit table is one that is linked into this particular database.

 

[PHV]

What about something like this ?
SELECT L.ID, L.CountyCd, L.LocalUnitType, L.LocalUnitCd, L.FiscalYear, L.AuditReceivedDate
FROM dbo_localunitaudit AS L LEFT JOIN [Audit Reviews] AS A
ON L.CountyCd=A.CountyCd AND L.LocalUnitType=A.LocalUnitType
AND L.LocalUnitCd=A.LocalUnitCd AND Val(L.FiscalYear)=Year(A.FYE)
WHERE A.DateCompleted Is Null


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

 

[goslincm]

I think were getting there. Is there a way to specify the localunittype I get? I want to pull out only the records with the values of: 0,1,2 or 3.

 

[PHV]

WHERE A.DateCompleted Is Null AND L.LocalUnitType In (0,1,2,3)

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

 

[goslincm]

Can I pose another question to this query you helped me with? The query below work as needed, however it was discovered that the tables we are working with do not provide us with a "name" field, which is needed.

SELECT L.ID, L.CountyCd, L.LocalUnitType, L.LocalUnitCd, L.FiscalYear, L.AuditReceivedDate, A.[unit name]

FROM dbo_localunitaudit AS L LEFT JOIN [Audit Reviews] AS A ON (L.CountyCd=A.CountyCd) AND (L.LocalUnitType=A.LocalUnitType) AND (L.LocalUnitCd=A.LocalUnitCd) AND (Val(L.FiscalYear)=Year(A.[FYE 2000]))

WHERE A.[Date Completed] Is Null and L.LocalUnitType In("1","2","3","0","7") and L.FiscalYear In( "2005","2006", "2007") and L.AuditReceivedDate Is Not Null;

So, another table would be needed to be brought into this which provides a [name] field, but I'm not certain how to bring it into this JOIN.

The table does have the same Countycd, LocalUnitType, LocalUnitCd, and the needed Name field. Can anyone show me how to incorporate this?