-
Here’s the SQL for a single query solution:
SELECT ProdID, yearno, weekno, T1.sold, (SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS ItemsPerBox, (SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;I used itemsize and sold as the table names, per your post, but I would recommend changing them to tblItemSize and tblSold. (the sold table has a sold field…which can lead to some confusion.
Note, the query results will have 'nulls' instead of zeros, if there is no itemsperbox value 'yet'. If you must have zeros, then use this (it will just take longer…)
SELECT ProdID, yearno, weekno, T1.sold, IIF(IsNull((SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS ItemsPerBox, IIF(IsNull((SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)),0,(SELECT TOP 1 itemsperbox*T1.sold AS total FROM itemsize WHERE (itemsize.yearno=T1.yearno AND itemsize.weekno<=T1.weekno) OR itemsize.yearno<T1.yearno ORDER BY yearno DESC, weekno DESC)) AS total
FROM sold AS T1
ORDER BY ProdID, yearno, weekno;You can get zero's with the first query (which would be faster, by putting records in the itemsize table like a,1,1,0, but that may disrupt other systems in your database)
Have fun….
-
-
-
-
-
No problem. I think the underscore is used instead of the question mark too. Quite frankly, they would make life a lot easier if they put a note about ADO wild cards in the MSDN under the definitions of ADO’s .EOF and .BOF properties. Because every time a developer who is used to * in Access, goes to ADO and uses * in his SQL, he’s going to get EOF and BOF, not realizing it’s the * acting as a true Asterick.
I was snagged several times by this, because it happened months apart, and I completely forgot to use the percent symbol! 
More then likely I’ll snag myself again a few months from now. 
Glad to help out….
-
No problem. I think the underscore is used instead of the question mark too. Quite frankly, they would make life a lot easier if they put a note about ADO wild cards in the MSDN under the definitions of ADO’s .EOF and .BOF properties. Because every time a developer who is used to * in Access, goes to ADO and uses * in his SQL, he’s going to get EOF and BOF, not realizing it’s the * acting as a true Asterick.
I was snagged several times by this, because it happened months apart, and I completely forgot to use the percent symbol! More then likely I’ll snag myself again a few months from now. Glad to help out….
-
-
-
You could always just use the API method:
Private Declare Function WNetGetConnection Lib “mpr.dll” Alias “WNetGetConnectionA” (ByVal lpszLocalName As String, ByVal lpszRemoteName As String, cbRemoteName As Long) As Long
Function GetUNCPath(strDrivePath As String) As String
Dim strTemp As String
Dim dwReturn As Long
strTemp = Space(255)
dwReturn = WNetGetConnection(strDrivePath, strTemp, 255)
If dwReturn = 0 Then
strTemp = Trim(strTemp)
GetUNCPath = Left(strTemp, Len(strTemp) – 1)
Else
GetUNCPath = “Error”
End If
End FunctionSo on my machine, GetUNCPath(“T:”) returns NasData which is the share path of our T: drive. I have it just returning ‘Error’ if something is wrong, but there is a bunch of specifics you can get from the error message, like ‘network is not present’, etc.
Hope this helps….
-
You could always just use the API method:
Private Declare Function WNetGetConnection Lib “mpr.dll” Alias “WNetGetConnectionA” (ByVal lpszLocalName As String, ByVal lpszRemoteName As String, cbRemoteName As Long) As Long
Function GetUNCPath(strDrivePath As String) As String
Dim strTemp As String
Dim dwReturn As Long
strTemp = Space(255)
dwReturn = WNetGetConnection(strDrivePath, strTemp, 255)
If dwReturn = 0 Then
strTemp = Trim(strTemp)
GetUNCPath = Left(strTemp, Len(strTemp) – 1)
Else
GetUNCPath = “Error”
End If
End FunctionSo on my machine, GetUNCPath(“T:”) returns NasData which is the share path of our T: drive. I have it just returning ‘Error’ if something is wrong, but there is a bunch of specifics you can get from the error message, like ‘network is not present’, etc.
Hope this helps….
-
Invest in Restorer 2000 Pro. http://www.bitmart.net/r2k.shtml%5B/url%5D Bought it a few years ago, best $30 I ever spent. Bought a personal copy for myself, when I had gone to ‘clean up’ a users machine at work, and completely lost a 6 gig partition full of his actual data files (not OS or program installs, actual data files, like .pst’s, word docs, excel files, etc.). The partition just literally disappeared. (Had reinstalled the OS, and wiped out the first partition, thinking nothing of the fact that the first partition keeps a ‘reference’ to other partitions on a dynamic disk….whooops). So booted to a clean OS, and had NO DATA! Yikes. Spent several hours trying to manually ‘spoof’ the system into seeing the drive, nothing worked. Did a little net research, and found Restorer 2000 Pro (has a demo version for free). Downloaded it, it found the missing partition and all of the data. Bought it, recovered the SIX GIGS of lost data….and was able to go to bed that night….
.Since then, I have used Restorer 2000 Pro several times. The only time it wasn’t able to help me, is when I lost a 2 segments of a three drive RAID. I had 2 of the 3 segments ‘untouched’, but the OS only saw 1 segment. Restorer ‘sees’ NTFS files whether they’ve been deleted, and it ignores partitions, but RAID’s don’t put data directly on the drive, it puts partial data amongst all the drives. Had to find another utility to recover from that (which was about $50, along with $500 in ‘tech support’, to get the right configuration).
Good luck!
-
Invest in Restorer 2000 Pro. http://www.bitmart.net/r2k.shtml%5B/url%5D Bought it a few years ago, best $30 I ever spent. Bought a personal copy for myself, when I had gone to ‘clean up’ a users machine at work, and completely lost a 6 gig partition full of his actual data files (not OS or program installs, actual data files, like .pst’s, word docs, excel files, etc.). The partition just literally disappeared. (Had reinstalled the OS, and wiped out the first partition, thinking nothing of the fact that the first partition keeps a ‘reference’ to other partitions on a dynamic disk….whooops). So booted to a clean OS, and had NO DATA! Yikes. Spent several hours trying to manually ‘spoof’ the system into seeing the drive, nothing worked. Did a little net research, and found Restorer 2000 Pro (has a demo version for free). Downloaded it, it found the missing partition and all of the data. Bought it, recovered the SIX GIGS of lost data….and was able to go to bed that night….
.Since then, I have used Restorer 2000 Pro several times. The only time it wasn’t able to help me, is when I lost a 2 segments of a three drive RAID. I had 2 of the 3 segments ‘untouched’, but the OS only saw 1 segment. Restorer ‘sees’ NTFS files whether they’ve been deleted, and it ignores partitions, but RAID’s don’t put data directly on the drive, it puts partial data amongst all the drives. Had to find another utility to recover from that (which was about $50, along with $500 in ‘tech support’, to get the right configuration).
Good luck!
-
Just an added comment about Mark’s default value suggestion. One handy, yet sometimes overlooked, advantage of setting a default value, instead of setting the actual value, is that a default value will display to the user, BUT WILL NOT enter data in the table, until other fields are used. As an example, if Joe User opens your form, enters one record, then moves to a new record, if Joe decides he doesn’t want to enter that record, he’ll just close the form. If you have used code to set the value (not default value) of the field, then you either end up with a partially empty record, with just the ‘automated’ fields filled in, OR if you have data validation in place/required fields, Joe User will get prompts when trying to close the form, because a record was created, but not completed.
On the other hand, if you have your code set the default value, and Joe User closes your form after going to a new record, no ‘partially empty’ records are created, nor does Joe User get any data validation prompts.
I post this once in a while, when I see this type of topic arise, because I’ve been the Joe User too many times, who was plagued with message boxes when I just wanted to close a form!
-
Just an added comment about Mark’s default value suggestion. One handy, yet sometimes overlooked, advantage of setting a default value, instead of setting the actual value, is that a default value will display to the user, BUT WILL NOT enter data in the table, until other fields are used. As an example, if Joe User opens your form, enters one record, then moves to a new record, if Joe decides he doesn’t want to enter that record, he’ll just close the form. If you have used code to set the value (not default value) of the field, then you either end up with a partially empty record, with just the ‘automated’ fields filled in, OR if you have data validation in place/required fields, Joe User will get prompts when trying to close the form, because a record was created, but not completed.
On the other hand, if you have your code set the default value, and Joe User closes your form after going to a new record, no ‘partially empty’ records are created, nor does Joe User get any data validation prompts.
I post this once in a while, when I see this type of topic arise, because I’ve been the Joe User too many times, who was plagued with message boxes when I just wanted to close a form!
Author
