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 Mar 27 2017, 08:58 AM
Post#1



Posts: 41
Joined: 9-March 17



Hi,
I am trying to avoid duplicates based on two fields in a table with a twist:
Serial Number
Model Number

If model numbers are 5892G5, or 5892G6 or 5892G7 then serial number must be unique.
If model numbers are 5893G5, or 5893G6 or 5893G7 then serial numbers must be unique.
But 5893G5 can have a serial number common to 5892G5.

Can someone help?jj

Thanks,
Vector1
Go to the top of the page
 
BruceM
post Mar 27 2017, 09:25 AM
Post#2


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


welcome2UA.gif
Since it isn't quite true that the serial numbers must be unique, it will help if you describe a little more fully exactly how "unique" is defined. Does this summarize it?

5892G6, 5892G7, 5893G6, and 5893G7 must have SNs not shared by any other model numbers
5892G5 may share a SN with any other 5892G5 item, but may share a SN with a 5893G5 item
5893G5 SN uniqueness is similar to how uniqueness is handled for 5892G5

It would help to know more about the table structure, too. It sounds as if model number and serial number are both stored in the same table. Most likely there should be a table for Models, and a related table for SNs within each model. Based on the model numbers you have posted the last two characters (G5, etc.) could be used to determine if and how uniqueness must be checked.

DLookup can be used, but if it is to be a large recordset that method could slow things down. There are options, but more information is needed first.
Go to the top of the page
 
Vector1
post Mar 27 2017, 11:12 AM
Post#3



Posts: 41
Joined: 9-March 17



Table Structure:
Table Name: tblModelNo (Master Table)
ModelID
ModelNo
RevLevel

Table Name: tblBarcodes (Data Table)
BarcodeID
ModelNo
SerialNo


Serial Number and Model Number Senerio:
5892xx CANNOT share any serialno with any other 5892xx. Same goes for 5893xx model BUT 5892 MAY share a serial number with 5893 and vice versa

Does this help?
Is there a way to create a validation logic when user is in a form view and enters a serial number and picks model number from a drop down box?
Go to the top of the page
 
BruceM
post Mar 27 2017, 11:56 AM
Post#4


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


That is different than what you initially stated. Nevertheless, I think I understand that serial numbers may not be shared among any 5892 or 5893 items, but any 5892 item may share a serial number with any 5893 item, regardless of the last two characters. Or is it closer to what you indicated in the first posting: 5892G5 and 5893G5 can share a SN, and same for 5892G6 and 5893G6, and same for 58925G7 and 5893G7, but 5892G5 cannot share a number only with 5893G5, but not 5893G6 or 5893G7, and similar for 5892/3G6 and 5892/3G7.

Regarding the table structure, it may be best to link on the ModelID rather than ModelNo (that is, use ModelID rather than ModelNo in tblBarcodes). This assumes ModelID is an autonumber primary key or other unique value not shown to the user. If ModelNo ever changes, ModelID will not be affected, and relationships between tables will remain intact. You can bring in the ModelNo any time you know the ModelID, so for users there is no difference.

Another question: Are you saying it is OK if 5892 and 5893 share a serial number, or it is a deliberate choice (and requirement) that they share a serial number?

Related questions: How are serial numbers assigned, and are there any rules as to how they are assigned? If there is a deliberate choice to have 5892 and 5893 share a serial number, it should be possible to design a query that limits selections to valid serial numbers. That query could be used as the row source of a combo box, with the option of typing in a completely unique entry.

The short answer is that if you are starting with the serial number and selecting the model afterward, you could apply a validation rule in the After Update event of the model number selection. I need to say that the approach seems backward, though. I would have expected the serial number to be assigned to the model, not the model to the serial number. Before I can suggest anything I need a better understanding of the process.
Go to the top of the page
 
Vector1
post Mar 27 2017, 01:43 PM
Post#5



Posts: 41
Joined: 9-March 17



Let me see if I can explain how it works:
1- The serial numbers are assigned to the model numbers. They are taken out ahead of time and they do have a logic.
example:
If I need 3 records for model number 5892G5, then it has serial numbers 17C0001, 17C002, 17C003
If I need 3 records for model number 5892G6, then it has serial numbers 17C0004, 17C005, 17C006
In any given situation, the model number 5893G5 or 5893G6 may intersect with 5892G5 or 5892G6 serial numbers, e.g.
If I need 3 records for model number 5893G5, then it may have serial number 17C003, 17C004, 17C005
If I need 3 record for model number 5893G6, then it may have serial number 17C006, 17C007, 17C007

The issue is that the operator may make a mistake during retyping the numbers. I would rather automate the whole process and not take out serial numbers ahead of time.
The serial numbers are assigned as follows:
17 (year)=2017;
C (month) -> A=Jan, B=Feb, C=Mar, D=Apr, E=May, F=Jun,G=Jul,H=Aug, "I" not assigned (too close to 1), J=Sep, K=Oct, L=Nov, M=Dec
Last for digits start with 0001

I do understand that IDs should be linked and not "ModelNo"

First: Can you help with an after update event in the model number combo box?
Second: Is it possible to automate serial numbers so there is no need for validation at all?
Go to the top of the page
 
BruceM
post Mar 27 2017, 02:56 PM
Post#6


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


Last question first: Yes, it is possible to automate most of this.

Are the serial number year and month values based on a field in the record, or the current date, or something else? You can produce the value with a function like the following:

CODE
Public Function YearMonth(ByVal DateValue As Date) As String

  Dim strYrMo   As String
  Dim intMonth  As Integer
  Dim intBase   As Integer
  
  intMonth = Month(DateValue)
  
  If intMonth < 9 Then
      strYrMo = Chr(64 + intMonth)
    Else
      strYrMo = Chr(65 + intMonth)
  End If
  
  YearMonth = Format(DateValue, "yy") & strYrMo
  
End Function


Add this code to a standard code module, add error handling as you wish, compile. In the Immediate code window:

?YearMonth(Date()) {Enter}

If you are not familiar with the use of the Immediate code window, see this link for information about this and other debugging techniques.

Of course you would not be using the Immediate code window in the runtime version. I mention it for demonstration only.

Take a look in Help about the Chr function. Chr(65) is A, 66 is B, and so forth. Lower case letters start at 97. Most if not all standard keyboard characters, and a number of characters not on the keyboard, can be expressed with the Chr function. While I'm on the topic, you can press and hold the Alt key on a standard keyboard, then a number on the numeric keypad (not the top row above the letters) to produce values. This works in many other applications as well, as long as you are in a Windows environment. For instance, Alt & 248 produces the degree symbol, so I can write 32°F here.

Note that the date value passed to the function could be a table field or any other valid date/time value. If the record already has a date field there is no need to store the 17C or whatever. If not, you could store it. However, it should be stored in a separate field from the incrementing number. They can be combined at any time for display.

Assuming you have a main form for the model number, and are entering the serial numbers in related subform records, you could use some variant of:

Me.SerialNumber = Nz(DMax("[SerialNo]","tblBarcodes","ModelID = " & Me.Parent.ModelID),0) + 1

I know this does not follow the business rule you are using, but I am still trying to understand that. Does every model number start with 5892 or 5893? If not, what is the business rule for model number assignment?
QUOTE
In any given situation, the model number 5893G5 or 5893G6 may intersect with 5892G5 or 5892G6 serial numbers, e.g.

Do you mean 5893G5 may intersect with either 5892G5 or 5892G6, or that it may intersect only with 5892G5? In other words, does the intersecting 5892 model need to have the same last two characters (G5, etc.) as the 5893 model?

Assuming the shared SNs are only for models that share the same last two characters, is there any requirement that 5892G5 and 5893G5 start with 17C0001 this month? That is, is there a connection between 5892G5 17C0001 and 5893G5 17C0001, or is it just that the duplicated SN is allowed?

I know this is a lot of questions, but automation can only happen in the context of defined business rules, and I still do not fully understand those.

To the first question, it would depend on what you are trying to do in the After Update event. Perhaps it will not be needed at all.
Go to the top of the page
 
Vector1
post Mar 27 2017, 05:02 PM
Post#7



Posts: 41
Joined: 9-March 17



Currently Serial numbers are manually created by someone controlling the log. As the month and year change they manually add new numbers in a spreadsheet and then populate database (one text field) with the serial numbers and model numbers one at a time.
-----

"Do you mean 5893G5 may intersect with either 5892G5 or 5892G6, or that it may intersect only with 5892G5?"
Your first part is correct....
------
There is no business rule per say. Currently I have these model numbers and they are lumped under categories which is why their serial numbers may intersect:

5892G5, G6, G7 (these are lumped together, unique among models)
5893G5, G6, G7, G8 (these are lumped together, unique among models)
8249G1, G2, G4 (these are lumped together, unique among models)
8250G1, G2 (these are lumped together, unique among models)

5892xx may share common serial number with 5893xx, or 8249xx, or 8250xx and vice versa

The duplication automatically comes up in cases when a 5892G2 is issued 17C0001 and then we build a model 8249G1 the same time frame 17C0001.

I didn't follow the DMAX function. Can you explain?
Go to the top of the page
 
BruceM
post Mar 28 2017, 06:47 AM
Post#8


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


DMax finds the highest value in a particular field in a specified recordset. DMax("[SerialNo]","tblBarcodes") finds the highest SerialNo value in tblBarcodes. DMax("[SerialNo]","tblBarcodes", "[ModelID] = " & ModelID) finds the highest SerialNo value in a record that has the same ModelID as the current record. The Nz function substitutes a value (0 in this case) if there are no SerialNos for the ModelID:
Nz(DMax("[SerialNo]","tblBarcodes", "[ModelID] = " & ModelID),0)
1 is added to the highest value to create an incrementing value:
Nz(DMax("[SerialNo]","tblBarcodes", "[ModelID] = " & ModelID),0) + 1
Additional criteria can be applied so the number restarts from 1 every month. Do not be concerned with leading zeros. That is just a matter of formatting. However, I still don't know if the date is determined from a field in the record or from the current date or something else. Without that information I can't suggest anything.

Are you saying a 5892 serial number may be duplicated for any model other than one starting with 5892, and the same for every other model? It looks to me as if the model number consists of two parts: a number, then a letter/number combination such as G5. If so, is it the first part that restricts duplication, so that you can have 17C0001 for 5893, 8249, etc.? If so, that is a business rule that can be put to good use toward automation, but it leads to more questions. If the answer to any one of the following is yes, you have a rule that can be put to use.
1) Is the first part always four digits (no letters or other characters)?
2) Is the second part always two characters consisting of a letter and a number?
3) If No to either 1) or 2), does the second part always start with a letter?

In any case, best practice would be to place the first part in its own field. If another barcode record has the same first part, no duplication, otherwise duplication is allowed.

Again, I need to know about the source of the date value too.
Go to the top of the page
 
Vector1
post Mar 28 2017, 08:49 AM
Post#9



Posts: 41
Joined: 9-March 17



Thank you Bruce for sticking with me!

The model number has three parts. I didn't provide you first part as it is common across all models: 115E, 5892/5893/8249/8250, G1/G2/G3/G4/G5/G6/G7/G8. So far, I have not seen any other variations than what I provided.
The date value is not determined using any date in the database. It is a manual thing. If we are in march of 2017, and we have built, say 5893G7 upto serial number 17C3001. Now if we go in April, the person responsible would change the serial number to 17D3002.
One thing that I was told is that the person responsible creates these records a week ahead. They can do it as they take out the serial numbers ahead of time based on the forecast of the models being built.

I was thinking that if I were to put a form in front of the main form which creates serial numbers based on the selection of a model number and quantity and then inserts those records in the main form.
The assumption is still that someone keeps track of the last serial number generated for that Model Category.
The thing is that I need to provide a validation rule during creating a series of serial numbers as to whether the serial numbers being generated are good or not. Please take a look at the serial creation form picture.
Attached File(s)
Attached File  SerialNoCreation.PNG ( 10.89K )Number of downloads: 0
 
Go to the top of the page
 
Vector1
post Mar 28 2017, 08:55 AM
Post#10



Posts: 41
Joined: 9-March 17



Here is the main form where the serial numbers and model numbers are entered by a supervisor and then barcode labels can be generated or other information can be entered as the product moves through the line.
Attached File(s)
Attached File  MainForm.PNG ( 23.09K )Number of downloads: 1
 
Go to the top of the page
 
BruceM
post Mar 28 2017, 09:13 AM
Post#11


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


I thought you were trying to automate the process. I wish you had told me sooner that you are not interested in that approach.
Go to the top of the page
 
Vector1
post Mar 28 2017, 10:19 AM
Post#12



Posts: 41
Joined: 9-March 17



I am sorry if I lead you to believe that I don't want to automate the process. I do want to automate.
Right now I am trying to work an interim process as well to satisfy the needs for the production.
Please help work the solution out for automating the process.
----
Let's stick with the last response...

1- The model number has three parts. I didn't provide you first part as it is common across all models: 115E, 5892/5893/8249/8250, G1/G2/G3/G4/G5/G6/G7/G8. So far, I have not seen any other variations than what I provided.
2- The date value is not determined using any date in the database. It is a manual thing. If we are in march of 2017, and we have built, say 5893G7 upto serial number 17C3001. Now if we go in April, the person responsible would change the serial number to 17D3002.
3- One thing that I was told is that the person responsible creates these records a week ahead. They can do it as they take out the serial numbers ahead of time based on the forecast of the models being built.
Go to the top of the page
 
BruceM
post Mar 28 2017, 12:45 PM
Post#13


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


The complexity here, which applies equally to validation and automation, is that ModelID does not tell the whole story. In other words, it is the middle part of the model number than precludes or allows duplicate serial numbers. It is not possible simply to look for the highest serial number this month for a given ModelID since there are other ModelID values that cannot share the serial number of the current record. 5892G1 and 5892G2 are different ModelIDs, but cannot share a serial number.

There are two main approaches I can see, although no doubt there are other options. One is a table structure that uses the middle of the model number (5892, etc.) as the model number, and the prefix 115E and suffix G2 etc. in a related ModelDetails table. The Barcode table, then would be related to the ModelDetails table.

The other option is to move the automation (or validation) to a VBA routine that opens a recordset consisting of all Model records with the same middle part, and searches that recordset for the highest serial number value.

Actually, the simplest would be to assign sequental serial numbers regardless of the model number, starting from 1 (0001) for the first record of a new month.

Let me know.
Go to the top of the page
 
Vector1
post Mar 28 2017, 02:46 PM
Post#14



Posts: 41
Joined: 9-March 17



I am leaning towards, "The other option is to move the automation (or validation) to a VBA routine that opens a recordset consisting of all Model records with the same middle part, and searches that recordset for the highest serial number value." , as I would have strip everything from an existing setup if we go to option 1.
Option 3 is not adoptable based on company policy.
Go to the top of the page
 
Vector1
post Mar 28 2017, 03:16 PM
Post#15



Posts: 41
Joined: 9-March 17



Hi Bruce,
I spoke with the crew creating these records and they would like a little less automation but would like control over dates. Is it possible for you to help with some routines based on the form I am attaching?
Just so you know the form I am attaching is on the fly form to generate and then insert these model, and serial numbers into the 'tblbarcodes'.
Let me know if its doable.
Here is logic:
1- Pick Model Number
2- Pick Year
3- Pick Month
4- Enter Qty of records to be produced
5- Create records

Compare this forms Model Number to tblbarcodes Model Number ( VBA routine that opens a recordset consisting of all Model records with the same middle part, and searches that recordset for the highest serial number value)
Openrecordset of tblbarcodes
loop until reaches specified qty:
Concatenate "Year" "Month" "serialnumber
end
Add to tblbarcodes
(May need validation anyway?)
Attached File(s)
Attached File  SerialNoCreationNew.PNG ( 11.78K )Number of downloads: 0
 
Go to the top of the page
 
BruceM
post Mar 29 2017, 06:56 AM
Post#16


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


I can't infer much about the structure by looking at the form. It seems you have a Model table, but it is not part of the form. Rather, the form seems to be based on tblBarcodes. Is that correct?

I mentioned earlier that I was using the current date because you did not mention otherwise, but that other values can be used. The process is much the same if you are selecting the month and year, except that those values need to be converted into a date field that is stored in the tblBarcodes record. You have to store the date, or at least the month and year values in separate integer or long integer fields. You could convert the current date to 17C and store that, but more on that in a moment. Anyhow, I need to know which route you are going or I can't make a suggestion.

I would have thought there is some value in knowing when a serial number is entered into the system. Do the users know they can simply select or enter a date, and converting the value to 17C (or whatever) can happen automatically? They can also enter a date as 3/29 {Tab}. The current year will fill in automatically. If they prefer the two step approach of entering the year and month, do they really want to remember that J is September? I showed a function that seamlessly converts any date/time value to 17C, etc.

It is interesting that they want less automation, yet they want to enter several serial number records at once, which is quite an automated process. Perhaps they just mean they want flexibility for the date, which I always said is an option.

I can show a basic procedure to generate serial numbers based on any model number, but it will depend on how the date/time value is stored.

Keep in mind that how data are stored and how they are presented are different things. I understand that this month is 17C. They can see it as that any time they want. There is no specific value to storing it in the table in that format. If they have the option of using a real date and getting the same output display, why not do it?
Go to the top of the page
 
Vector1
post Mar 29 2017, 08:17 AM
Post#17



Posts: 41
Joined: 9-March 17



The first field "Model#" is pulling data from tblModel. I understand your question about the date. I can add an unbound field "Date" and they can pick date from the standard calendar or enter date instead of year and month.

Will need to loop thru the quantity and update the tblbarcodes with the picked model#, date, and convert date into correct serial numbers.

The users are aware that whatever they pick (whether year, and month or date from calendar) when they actually open the main form all the records will be populated with new records showing Model#, date, and serial numbers.
Attached File(s)
Attached File  SerialNoCreationNew1.PNG ( 11.31K )Number of downloads: 0
 
Go to the top of the page
 
BruceM
post Mar 29 2017, 09:29 AM
Post#18


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


You need to store the date value somewhere, whether the actual date or the 17C format in a text field. Which one is it? Without that information it is not possible to automate either serial number entry or validation of manually entered serial numbers.

I would ask the users if they will ever have any interest in knowing when the serial numbers were added (that is, the day of the month), but I can do nothing until I know how and where you are storing that value.

Remember, how it is stored and how it is displayed are entirely different. I showed how you can display a date/time value in the format 17C, with almost no extra user effort needed, and no need to remember which letter to associate with which month.
Go to the top of the page
 
Vector1
post Mar 29 2017, 10:09 AM
Post#19



Posts: 41
Joined: 9-March 17



I am confused! The "serial creation form has an unbound text box, where user picks a date. And then, I have a field in the tblbarcodes called "Date_CrateLabel" where we can store the date picked in the "serial creation form"
Go to the top of the page
 
BruceM
post Mar 29 2017, 10:23 AM
Post#20


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


Why are you confused? I am asking for the information so that I can formulate a suggestion. You have now provided that information, which I did not know until this moment.

I now have enough information, I think, but it may be a few hours before I can get something posted.
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 01:21 AM