Table Field Issue, Potential Removal of offending Table, and Inner Join "ghost" queries

[AlexB123]

Hi all,

I'm working on a db I inherited, and I'm troubleshooting a query that returns duplicate values. The problem is a table that either generates a timestamp, or the timestamp is passed from VBA during some other query, BUT the timestamp is invalid, inconsistent, and changes frequently. Additionally, the foreign keys in this table seem to disappear. However, apart from this one duplicate issue, all other reports seem to be fine. Apart from 8 queries that select the time stamp column and join onto the table, this information is non-essential and can be removed, potentially.

I am considering just removing the table and updating all the queries that reference it. Below is what the table looks like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ClientID[/TD]
[TD]BOB[/TD]
[TD]timeStamp[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]900012318171329[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]00000XYZ[/TD]
[TD][/TD]
[TD]900012318117139[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]00000ABC[/TD]
[TD][/TD]
[TD]9000111118134353[/TD]
[/TR]
</tbody>[/TABLE]

In addition to the blank field in "ClientID", these values constantly change. Sometimes there is only one row, other times there are two or three, and "ClientID" changes. ClientID should contain about ten values for the purposes of joining. Here is an example of the queries referencing this table.

SELECT tblClients.ClientID, 
tblClients.Client_Name, 
 tblRegisterCreation.timeStamp,
 tblChecks.Check_Number, 
 tblChecks.Check_Date,
 tblChecks.Check_Amount, 
 tblCheckType.Check_Type_Name, 
 tblChecks.CheckDBID,
tblChecks.AddressID


FROM tblRegisterCreation, ((tblChecks INNER JOIN tblAddress 
         ON tblChecks.AddressID = tblAddress.AddressID) INNER JOIN tblCheckType 
         ON tblChecks.Check_Type = tblCheckType.CheckTypeID) INNER JOIN tblClients
         ON tblChecks.ClientID = tblClients.ClientID
WHERE (((tblChecks.Check_Date)<DateAdd("m",-3,Now())) AND ((tblClients.Client_Status)=1) AND ((tblClients.BOB)=[tblRegisterCreation].[BOB]) AND ((tblChecks.Status)=1) AND ((tblClients.ExcludeRegister)=False))
 OR (((tblClients.ClientID)=[Forms]![frmRegisterCreation]![ClientID]) AND ((tblChecks.Check_Date)<DateAdd("m",-3,Now())) AND ((tblChecks.Status)=1) AND ((tblClients.ExcludeRegister)=False))
 OR (((tblClients.ClientID)=[Forms]![frmRegisterCreation]![ClientID]));
<dateadd("m",-3,now())) ((tblclients.excluderegister)="False))
 OR (((tblClients.ClientID)=[Forms]![frmRegisterCreation]![ClientID]) AND ((tblChecks.Check_Date)<dateadd(" m",-3,now()))=""></dateadd("m",-3,now()))>

 

[AlexB123]

Thank you for the explanation. I understand what you're saying, but would still prefer to use "inner join", and I can't get it to compile? Here's what I've attempted:

FROM tblChecks
 (inner join tblAddress
 on tblChecks.AddressID = tblAddress.AddressID 
 (inner join tblCheckType
 on tblChecks.Check_Type = tblCheckType.CheckTypeI
 (inner join tblClients
 on tblChecks.ClientID = tblClients.ClientID 
(  inner join 
  on tblClients.ClientID = Forms!frmRegisterCreation!ClientID))))

 

[AlexB123]

Got it! Thanks a ton!

 

[Micron]

Glad you got it. Wasn't ignoring you; I'm on vacation and can't do this every day, though I try.