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;
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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:

cblakley4

New Member
Joined
Mar 10, 2011
Messages
25
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]
 

cblakley4

New Member
Joined
Mar 10, 2011
Messages
25
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
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

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
Top