Phantom Table (or Query)

[newguy86]

Hi all,
I have this Access 2007 db that I inherited. And in this db I have a query that pulls data from a table (or another query) that I cannot find anywhere in the db. I have looked at every object, turned on the option to have hidden objects displayed, and even used the search function on both the VBA code and the objects list and I still cannot find anything that has the name of the table (or query) i'm looking for. I did notice that the name of the table (or query) was similar to the name of the query that i'm working on but I wasn't sure if that was relevant since the names are not exact matches.

So is there anything that i'm missing or has something changed between Access 2003 and 2007 that would explain this?

Any assistance would be greatly appreciated!


Travis

http://www.apexsystemsinc.com

 

[dhookom]

Does the object name have an ending like "_1"? Do you understand tables and queries in the FROM clause can be aliased with any name?

Duane
Hook'D on Access
MS Access MVP

 

[newguy86]

No. The object name is "CurrProjWorkstream".

Yes, but how do I determine where the data is actually comimg from?

Here is the SQL code if needed:

SELECT CurrProjWorkstream.FundingSponsor, tblProjects.DIO_PrgMgr, CurrProjWorkstream.BusnessPM, CurrProjWorkstream.projID, CurrProjWorkstream.Workstream, CurrProjWorkstream.DM_POS_Mgr, CurrProjWorkstream.RM, tblAssignments.empName, tblAssignments.assignStatus, CurrProjWorkstream.Pnum, CurrProjWorkstream.ProjectName, tblAssignments.Role, CurrProjWorkstream.Release, CurrProjWorkstream.projStart, CurrProjWorkstream.projEnd, CurrProjWorkstream.PM_AllocationPct, CurrProjWorkstream.calcHrs, CurrProjWorkstream.origEstHrs, [origEstHrs]-[calcHrs] AS ETC, qryTotalPIDByProject.SumOforigEstHrs AS [PID Total], tblProjects.dateAdded AS [Date/Time Added]
FROM qryTotalPIDByProject RIGHT JOIN ((tblAssignments RIGHT JOIN (SELECT tblProjects.FundingSponsor, tblProjects.PM_AllocationPct, tblProjects.DM_POS_Mgr, tblProjects.BusnessPM, [tblProjects-OtherSystemIDs].projID, tblProjects.Workstream, tblProjects.RM, tblProjects.Pnum, tblProjects.projName AS ProjectName, tblProjects.projReleaseTiming AS Release, tblProjects.projStart, tblProjects.projEnd, Sum(tblAssignments.calcHrs) AS calcHrs, Sum(tblAssignments.origEstHrs) AS origEstHrs FROM (tblProjects INNER JOIN [tblProjects-OtherSystemIDs] ON tblProjects.Pnum=[tblProjects-OtherSystemIDs].Pnum) INNER JOIN tblAssignments ON tblProjects.Pnum=tblAssignments.Pnum WHERE (((tblProjects.projStatus)="Open") And (([tblProjects-OtherSystemIDs].projID) Is Not Null) And ((tblAssignments.assignStatus)="Active" Or (tblAssignments.assignStatus)="TBD") And ((tblAssignments.empStatus)<>"Inactive") And ((IIf(Now()<tblAssignments.AssignEnd,1,0))=1) And ((Switch(Left([projID],1)='P',"Non-Merger",Left([projID],1)='I',"Merger",Left([projID],1)='S',"Other",Left([projID],1)='A',"Other")) Is Not Null)) GROUP BY tblProjects.FundingSponsor, tblProjects.PM_AllocationPct, tblProjects.DM_POS_Mgr, tblProjects.BusnessPM, [tblProjects-OtherSystemIDs].projID, tblProjects.Workstream, tblProjects.RM, tblProjects.Pnum, tblProjects.projName, tblProjects.projReleaseTiming, tblProjects.projStart, tblProjects.projEnd, tblAssignments.Role HAVING (((tblAssignments.Role)="DM PM Primary")) ORDER BY tblProjects.Pnum, Last(tblAssignments.dateAdded) DESC)  AS CurrProjWorkstream ON tblAssignments.Pnum = CurrProjWorkstream.Pnum) LEFT JOIN tblProjects ON CurrProjWorkstream.Pnum = tblProjects.Pnum) ON qryTotalPIDByProject.FirstOfPnum = CurrProjWorkstream.Pnum
GROUP BY CurrProjWorkstream.FundingSponsor, tblProjects.DIO_PrgMgr, CurrProjWorkstream.BusnessPM, CurrProjWorkstream.projID, CurrProjWorkstream.Workstream, CurrProjWorkstream.DM_POS_Mgr, CurrProjWorkstream.RM, tblAssignments.empName, tblAssignments.assignStatus, CurrProjWorkstream.Pnum, CurrProjWorkstream.ProjectName, tblAssignments.Role, CurrProjWorkstream.Release, CurrProjWorkstream.projStart, CurrProjWorkstream.projEnd, CurrProjWorkstream.PM_AllocationPct, CurrProjWorkstream.calcHrs, CurrProjWorkstream.origEstHrs, [origEstHrs]-[calcHrs], qryTotalPIDByProject.SumOforigEstHrs, tblProjects.dateAdded
HAVING (((tblAssignments.assignStatus)="Active" Or (tblAssignments.assignStatus)="TBD") AND ((tblAssignments.Role)="DM PM Primary"));

Travis

http://www.apexsystemsinc.com

 

[dhookom]

The CurrProjWorkstream is derived in the query.

(SELECT tblProjects.FundingSponsor, tblProjects.PM_AllocationPct, tblProjects.DM_POS_Mgr, tblProjects.BusnessPM, [tblProjects-OtherSystemIDs].projID, tblProjects.Workstream, tblProjects.RM, tblProjects.Pnum, tblProjects.projName AS ProjectName, tblProjects.projReleaseTiming AS Release, tblProjects.projStart, tblProjects.projEnd,
 Sum(tblAssignments.calcHrs) AS calcHrs, Sum(tblAssignments.origEstHrs) AS origEstHrs
 FROM (tblProjects INNER JOIN [tblProjects-OtherSystemIDs] ON tblProjects.Pnum=[tblProjects-OtherSystemIDs].Pnum)
 INNER JOIN tblAssignments ON tblProjects.Pnum=tblAssignments.Pnum
 WHERE (((tblProjects.projStatus)="Open") And (([tblProjects-OtherSystemIDs].projID) Is Not Null) And ((tblAssignments.assignStatus)="Active" Or (tblAssignments.assignStatus)="TBD") And ((tblAssignments.empStatus)<>"Inactive") And ((IIf(Now()<tblAssignments.AssignEnd,1,0))=1) And ((Switch(Left([projID],1)='P',"Non-Merger",Left([projID],1)='I',"Merger",Left([projID],1)='S',"Other",Left([projID],1)='A',"Other")) Is Not Null))
 GROUP BY tblProjects.FundingSponsor, tblProjects.PM_AllocationPct, tblProjects.DM_POS_Mgr, tblProjects.BusnessPM, [tblProjects-OtherSystemIDs].projID, tblProjects.Workstream, tblProjects.RM, tblProjects.Pnum, tblProjects.projName, tblProjects.projReleaseTiming, tblProjects.projStart, tblProjects.projEnd, tblAssignments.Role HAVING (((tblAssignments.Role)="DM PM Primary")) ORDER BY tblProjects.Pnum, Last(tblAssignments.dateAdded) DESC)  [red]AS CurrProjWorkstream [/red]

Duane
Hook'D on Access
MS Access MVP