Primary Key and Foreign Key off by 1 second

cblakley4

New Member
Joined
Mar 10, 2011
Messages
25
I have 2 tables that are being populated from a .Net Application. The inserts are happening one after the other. Occasionally the 2nd falls 1 second after the 1st.

The Key for these 2 tables are LnId, UserId, and TransDate

I have accounted for the time difference with the following, but I am now being asked for the Date range twice. Is there a better way to accomplish this that won't ask me for my date range twice?

SELECT tblTransactions.LnNumber, tblTransactions.TransDate, tblCallCatValues.Level2, tblReRegTransactions.OldLoan, tblReRegTransactions.NewLoan, tblReRegTransactions.Reason
FROM (tblTransactions INNER JOIN tblCallCatValues ON tblTransactions.CallCategory = tblCallCatValues.CatValue) LEFT JOIN tblReRegTransactions ON (tblTransactions.LnNumber = tblReRegTransactions.LnId) AND (tblTransactions.PricerID = tblReRegTransactions.UserId) AND (datediff("s",tblTransactions.TransDate , tblReRegTransactions.TransDate) =1) OR (datediff("s",tblTransactions.TransDate , tblReRegTransactions.TransDate) =0)
WHERE (((tblTransactions.TransDate)>[StartDate] And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In ('120','121','152','153','205','206','178','179','31','69','50','77','36','38','43','66','15','139','173','195','217','6','49','42','19','223','224','225','226','227','228','229','230','231','232')))
ORDER BY tblTransactions.LnNumber;
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Unless one of your queries has an unused or forgotten parameter (which can account for a popup), you don't have the Date being asked for twice. You have two dates, each being asked for once:

WHERE (((tblTransactions.TransDate)>[StartDate]
And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In

Note: are you sure the transdate is part of a primary key - that would be highly unusual (for one thing, if it's a key it wouldn't be generated as you describe, since it wouldn't be a key anymore).
 
Last edited:
Upvote 0
Unless one of your queries has an unused or forgotten parameter (which can account for a popup), you don't have the Date being asked for twice. You have two dates, each being asked for once:

WHERE (((tblTransactions.TransDate)>[StartDate]
And (tblTransactions.TransDate)<[EndDateComp]) AND ((tblTransactions.CallCategory) In

Note: are you sure the transdate is part of a primary key - that would be highly unusual (for one thing, if it's a key it wouldn't be generated as you describe, since it wouldn't be a key anymore).


Ok, so you caught me a little bit. Technically it isn't defined as a primary key in the database, but the loanNumber, userId, and timestamp together is the only thing that uniquely identifies the record. I can't just change it to a Date, as Time is still very important in the data. Most records come in with the exact same time stamp, but a few come in a few milliseconds after the new second. Thus, the timestamp difference.

As for the query, I'm getting 4 pop ups. [StartDate], [EndDateComp],[StartDate],[EndDateComp]
 
Upvote 0
Ok, so you caught me a little bit. Technically it isn't defined as a primary key in the database, but the loanNumber, userId, and timestamp together is the only thing that uniquely identifies the record. I can't just change it to a Date, as Time is still very important in the data. Most records come in with the exact same time stamp, but a few come in a few milliseconds after the new second. Thus, the timestamp difference.

As for the query, I'm getting 4 pop ups. [StartDate], [EndDateComp],[StartDate],[EndDateComp]

I've got a solution. Instead of having the column defaulting to now, I'll control the date/Time in the insert query on the application side. Now that I've thought it, I feel kind of silly for asking in the first place.


Thanks for any time I may have wasted.

Casey
 
Upvote 0
Great. Not that it matters but some judicious rounding can also be a way to shave off a little variation in a time value to get values that fit into a certain tolerance.
 
Upvote 0

Forum statistics

Threads
1,214,319
Messages
6,118,881
Members
448,855
Latest member
joshpep96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top