UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Can I Use A Dcount Function, Access 2016    
 
   
Vector1
post Apr 4 2017, 10:40 AM
Post#41



Posts: 41
Joined: 9-March 17



Correct...
A modelNo type has never reached 10,000.
The changes in the product takes place so quickly that we end up with a new model number...
Go to the top of the page
 
BruceM
post Apr 4 2017, 10:51 AM
Post#42


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


OK then. Even if that changes, you can reformat the serial number to add extra zeros. The numbers will increment to the limits of what the field allows.

Good luck with the project!
Go to the top of the page
 
Vector1
post Apr 4 2017, 10:59 AM
Post#43



Posts: 41
Joined: 9-March 17



Bruce,
I am not sure what changed or I am doing something wrong...
After adding the quotes, the first time I ran several records of same model type 5892 for several months and the data came out perfect.
(remember, I took out the date restriction)
I closed and reopened the database and entered some more records and it starting duplicating based on month again...
Am I doing something wrong with the code?
Go to the top of the page
 
Vector1
post Apr 4 2017, 11:20 AM
Post#44



Posts: 41
Joined: 9-March 17



I am sorry...I am out of wits here...
I feel that the code is not stripping the last two characters of a model number e.g. G5, G7, which is why it duplicates among the same model types.
Can you help...
Go to the top of the page
 
Vector1
post Apr 4 2017, 12:00 PM
Post#45



Posts: 41
Joined: 9-March 17



Please help with the SQL statement:

strSQL_BCode = _
"SELECT ModelID, " & _
"Max(Serial_No) As MaxSN " & _
"FROM tblBarcodes " & _
"WHERE ModelID In " & strSQL_Model & _
"GROUP BY ModelID"



I see that a specific modelID is being selected, and max value of serial number is being selected from tblbarcodes where specific modelID matches the modelID in strSQL_Model

To me the code is looking at the a specific modelID and maximizing the serial number for that modelID AND NOT looking at the maximum serial number among the same model types e.g. 5892G5/G7 et.

which is why when records are generated for 5892G5 and then records are generated for 5892G7 and then again more records are generated for 5892G5, the code dups the last set of records created FOR 5892G5 with 5892G7. OR call it another way, the code starts from the maximum serial number for 5892G5 and starts incrementing from there.
Go to the top of the page
 
BruceM
post Apr 4 2017, 12:29 PM
Post#46


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


The problem is that it is necessary to parse the middle of the the model number. However, when there are two or more model numbers (5892G5 and G7, for instance), the strSQL_BCode query returns as many records as there are model numbers that share the 5892 portion. The value selected for intSN was the value from the first record in that recordset, since nothing else was specifed.

One option would have been to loop through the recordset created by that SQL, and identify the highest SN value. However, the code is simpler with something like this:
CODE
  strSQL_BCode = _
    "SELECT TOP 1 Max(Serial_No) As MaxSN " & _
    "FROM tblBarcodes " & _
    "WHERE ModelID In " & strSQL_Model & _
    "GROUP BY ModelID " & _
    "ORDER BY Max(Serial_No) DESC"

Debug.Print strSQL_BCode  ' Remove when testing is complete


As a test, enter records for both 5892G5 and 5892G7, which of course will cause the code to run. After that, go to the Immediate code window (Ctrl + G), copy what you see (the Debug.Print result), and paste it into SQL view of a query. Switch to datasheet view to see the result. It should be the highest SN value for any 5892 model.

Now go back to SQL view and remove "TOP 1 ". Again, switch to datasheet view.

To see the added SNs more clearly, add tblModelNo to qryBarcodes. Add Model_No, then a calculated field:

ModelType: Mid([Model_no],5,4)

Sort by that field.

Then add Serial_No from tblBarcodes, and sort by it. That is, sort first by ModelType, then Serial_No. No other sorts for now. This is just to present the results clearly.

After adding each batch of SNs, close and reopen qryBarcodes, or leave it open and click Refresh All on the Home tab.

One thing this shows is the perils of repeating values. Your initial evaluation should have showed the repeating value. Having identified it, you could have used the 5892, etc. as the model number, with the prefix and suffix as related records. It may have seemed like over-normalization to break it down to that extent, but I think you can see now that the less normalized version has been quite a challenge. There is little risk to over-normalization. Queries will involve more tables, and may run a little slower as a result, but most likely not enough to notice.
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 02:48 PM