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]

SELECT tblClients.ClientID, tblClients.Client_Name, 
tblClients.CAM, tblClients.Plan_Exceptions,
 tblRegisterCreation.RegisterStamp,
 tblChecks.Check_Number, 
 tblChecks.Check_Date,
 tblChecks.Check_Amount, 
 tblCheckType.Check_Type_Name, 
 tblChecks.CheckDBID,
 Trim([Payee_Last_Name] & IIf(Nz([Payee_First_Name],"")="","",", " & [Payee_First_Name])) AS Name,
 Trim([Address1] & " " & [Address2] & " " & [Address3] & " " & [City] & " " & [ST] & " " & [ZIP] & " " & [Country] & " " & [Country_Code]) AS Address,
 IIf([Check_Date]>Now()-180,"Check outstanding 3-12 months",IIf([Check_Date]>Now()-390,"Check outstanding 3-12 months If $15+ address search conducted at month 7","Check outstanding 13+ months")) AS Dated
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())) ((tblclients.excluderegister)="False))
 OR (((tblClients.ClientID)=[Forms]![frmRegisterCreation]![ClientID]) AND ((tblChecks.Check_Date)<dateadd("m",-3,now())) ((tblclients.excluderegister)="False))
 OR (((tblClients.ClientID)=[Forms]![frmRegisterCreation]![ClientID]));

</dateadd("m",-3,now()))></dateadd("m",-3,now()))>

 

[AlexB123]

What can I do? What is the best approach?

Is deleting the table and updating the queries feasible?

Is there a way to find out what process populates this table?

 

[Micron]

I'm afraid not much of your post makes any sense. I would expect a time stamp to change, wouldn't you? Time changes, so how can a time stamp not change?
If you mean it's invalid because it's a huge number, that's one thing, but if you mean because it's 900012318117139 and should be 900012318117140 then that would be something else. Regardless, the number is huge even if it's in milliseconds.

Neither of these 2 parts make any sense either: WHERE (((tblChecks.Check_Date) OR ... ((tblChecks.Check_Date)
It's like saying WHERE car.color. Where car.color is what?? At best, those parts might default to Boolean values, likely False; or maybe Nulls or just plain jibberish. I'd say there's no way anyone can answer your questions, save to say, copy the db, delete as you wish and see what happens in the copy. Make sure that if this is a split db, you don't adversely affect any linked tables that shouldn't be messed up. As for what populates the table, yes but it might require some digging. If it's an append or update query and you mouse over them, the bubble help usually identifies the table affected. I find that as of version 2016, it seems to append the table name directly onto the query name, so it's not obvious any more. If it's vba sql, then you'll have to search the db for the table name (Ctrl+F when the vb editor is open and showing code). Be sure to set the search scope for the entire project.

 

[AlexB123]

Thanks Micron,

For some reason I am unable to properly post all of the code or everything I have to say. I'm giving up posting the "Where" statement.

As to the number, it is:

Private Sub cmbBOB_AfterUpdate()
Me.cmbClientID = ""
Me.txtRegisterStamp = "9" & Right("00" & Me.cmbBOB, 2) & Format(Now(), "MMDDYYHHNNSS")
Me.Refresh
End Sub
Private Sub cmbClientID_AfterUpdate()
Me.cmbBOB = ""
Me.txtRegisterStamp = "900" & Format(Now(), "MMDDYYHHNNSS")
Me.Refresh
End Sub

The time stamp is hidden from the user, and in design view the control source is set to RegisterStamp in tblRegisterCreation. When the query I need runs, it does:

RegisterStamp = DLookup("RegisterStamp", "tblRegisterCreation")

I'm convinced either the table or the text box with txtRegisterStamp needs something to drive it once the user has used the form. Nothing is populating RegisterStamp in the table, but spreadsheets are being named with the RegisterStamp included. Is there a formula or method I could use to begin repopulating tblRegisterCreation with a time stamp formulated this way?

 

[xenou]

The information in your last stamp shows that the "timestamp" is based on whenever the value in control cmbBOB or cmbClientID is modified (assuming that the record is then saved).

Its more of a code than a true time stamp - since it has the 900 (or 90 maybe) stuff at the front, and then it used mmddyyhhnnss stuff afterwords.

You can't recreate that since there is no way of knowing when the record was originally modified (assuming you can't figure that out from some other evidence). You could fill it in with the current time if you wanted to.

Also if both of these records are updating the same timestamp field on the same record (row) then it would also be merely a guess which came first or second - and only the second "timestamp" would be saved.

 

[Micron]

Not sure how we can help solve if you have post limitation issues, or something like that. The WHERE part looks complete in your first post, and I stand by what I wrote about it not making sense. Maybe re-review that and try to apply what I'm saying; anything that looks like WHERE (((tblChecks.Check_Date) is an incomplete thought.

RegisterStamp = DLookup("RegisterStamp", "tblRegisterCreation")

Also, without providing criteria, DLookup will return the first value from the specified table field, which often will be the first record but I wouldn't guarantee that. If you only have one record in that table, then could be OK. If not, then don't expect accurate results. Could be why there's no table population; could be the faulty WHERE clause.

 

[AlexB123]

Thank you both for your replies! Each was very helpful and informative.

I have one last question related to this thread ...

I ended up rewriting the query, but the only way I could do it was through an "implicit inner join" (my understanding is developing, please bare with me) of the several conditions. The problem is that within design view, the query has no relationships, and I would prefer to have this query using proper inner join syntax and established relationships. Could either of you suggest a better version of my query?

SELECT tblClients.ClientID,
 tblClients.Client_Name,
 tblChecks.Check_Number,
 tblChecks.Check_Date,
 tblChecks.Check_Amount,
 tblCheckType.Check_Type_Name,
 tblChecks.CheckDBID,  
 
FROM tblChecks, tblClients, tblAddress,tblCheckType
WHERE tblChecks.AddressID = tblAddress.AddressID AND
   tblChecks.Check_Type = tblCheckType.CheckTypeID AND   
   tblChecks.ClientID = tblClients.ClientID AND
   tblClients.ClientID = Forms!frmRegisterCreation!ClientID;<dateadd("m",-3,now())) and

</dateadd("m",-3,now()))>

 

[AlexB123]

I have a few simple conditions that actually need the WHERE clause, but again I am having trouble posting them. But if I can get a better query in terms of joins I can handle the last bit.

Much thanks!

 

[xenou]

I ended up rewriting the query, but the only way I could do it was through an "implicit inner join" (my understanding is developing, please bare with me) of the several conditions. The problem is that within design view, the query has no relationships, and I would prefer to have this query using proper inner join syntax and established relationships. Could either of you suggest a better version of my query?

You can use inner joins with or without "established relationships". The joins will work the same.

For what it's worth there is in SQL the older style of join syntax:

select * 
from 
    Table1, Table2
where 
    Table1.ID = Table2.ID

and the new style of join syntax:

select * 
from 
    Table1
    inner join Table2
    on Table1.ID = Table2.ID

Functionally these are the same. The first uses an older standard for SQL. The latter is a new syntax that is generally preferred and recommended (but let's not worry about why right now).

In short, your posted query uses an older syntax where join conditions are in the where clause, but it will work. It can also be rewritten with the "JOIN" and "ON" clauses instead.

Established relationships don't make joins different, but they are there to provide correctness and reliablity for your database, among other things (for instance, no missing primary keys, no invalid foreign keys).