Limiting the number of available records in a table, and recycling/reusing them...?

[Colin@Toyota]

Where to start... it was an incomplete db. I was starting to work a copy of yours, and some questions started popping into my head. So my brain sort of stopped working on the db, and started wondering about db design. Then I got frusterated, and posted the question that made me stop in the first place.

I guess my problem was that I could quite see how your design would allow for one department code to be applied to many line numbers. This raised an alarm for me, as this is often the case. Also, I wanted to populate some of the tables with some real data so you could see what I am working with. Unfortunately, I got sidetracked and posted it before I had added to anything except the department code table. If you look there you will notice that there are 167 possible department codes... I may not have explained how they work too well. They also act like open slots for part recovery information, similar to the line codes. Different combinations of final destination, recovery type, priority, and time frame dictate different department codes. So to sum it up, each single SETR will have one department code based on the aforementioned criteria, multiple models-parts combinations, and each model-part combo gets its own line number.

Another problem I came across was here with the department codes. All of them are unique, except DG, which we use for parts relating to the fuel system, or any other dangerous good that must me handled, packaged and shipped specially. I dont know of anyway to allow duplicates of DG, unless I change my table setup so that the actual department code is not the pk of tblDeptCode, and use an autonumber instead?

Ugh...
Tell you what... why don't I regroup, and finish what I was going to do to the db, properly formulate my questions, THEN post.


Thanks

 

[CraigDolphin]

Let's break this down with the new information.

So, basically dept code is information that Warantee guy sends to the dealer->3rd party receiver that tells them not only where to ship something, but how urgently, and with special precautions for some of the parts that come from fuel systems?

So, my next question for you is best asked via an example.

Say dealer Bob gets a 2003 Corolla and removes the left,front shock absorber, the right, rear shock absorber, and the fuel pump. He files a claim with warantee guy. Warantee guy looks them up and finds that three line numbers match those combinations, all from the same setr.

Warantee guy sends him ONE dept code, or THREE dept codes? (two of which might be the same code)

If only one code is sent then the Dept_Code belongs in the SETR table (if it's stored at all since it is just a combination of three things that you are already storing {location, priority, and whether the parts include fuel-system parts} so it could be logically determined by the db from those three things).

If three codes are sent ('DG' for the fuel pump and another code for each of the shock absorbers) then the Dept_Code belongs in the SETRModelsParts table if you actually store it, and has to be defined for each combination of model/year/part within a setr individually {again, this could be logically deduced by the db based on the other information that has already been entered}.

 

[Colin@Toyota]

If three codes are sent ('DG' for the fuel pump and another code for each of the shock absorbers) then the Dept_Code belongs in the SETRModelsParts table if you actually store it, and has to be defined for each combination of model/year/part within a setr individually {again, this could be logically deduced by the db based on the other information that has already been entered}.

Each warranty claim on a failed part is responded to with a department code that corresponds with an applicable SETR.

Something I just thought of... There will be cases when an SETR may have two dept codes (one being DG). The only time this will happen is when one shipping code is DG. Some blanket parts recoveries include fuel system parts, and while the majority of the parts might be under the dept code A5, and part number that starts with 7, (i.e. part # 7****-*****) is a fuel system related part, and must be assigned the DG dept code. This notifies the dealer and third party shipper to ensure it is packaged properly. However, the dealer is also instructed to write the SETR number on the box, so that the third party shipper can group it with the rest of the recovery, ensuring it ends up at the proper destination.

 

[CraigDolphin]

So does he file a separate claim for each part?

 

[Colin@Toyota]

So does he file a separate claim for each part?

It depends... There is an OFP (original failed part-number) listed, as well as any associated parts. In the case of the shock absorbers, maybe only the left one failed, but if the recovery states we need both for comparison, they would both be filed under one claim.

NOTE: I just edited my previous post.

 

[CraigDolphin]

Ok...now we're getting to the meat of this.
_____________________________________
All SETR's, in reality, have only ONE dept_code.

DG is NOT a dept_code at all: in reality it is actually a dangerous goods warning.

Claims may have one or many parts.

One dept_Code is sent to the dealer/shipper by the warantee guy for EACH item in a claim that has a line number related to that SETR. (If all parts in a claim share the same SETR, then the warantee guy might only send the code once).

If some parts are required by one SETR, and other parts by a different SETR, Warantee guy will send an itemized list of parts vs dept_code.

Some parts included in a claim might be fuel system parts: if so, the code 'DG' is sent for those parts only instead of the true dept_code. (to inform the dealer and receiver to package the part carefully. Currently, the dealer has to write the SETR ID on the DG packages so that the receiver can lookm up where to ship the item when they receive it).

______________________________________

First up: this is a bad system. What the warantee guy ought to be doing is sending the correct dept_code for all parts AND an additional warning code to the dealer for those parts that are in the fuel system. This would avoid the dealer having to write the SETR number on the package and save the receiver from having to look up where to ship the item.

So, you should:
1) investigate whether warantee guy is able/willing to do this if you provide both pieces of information in 2 separate fields along with the line numbers that you send him etc.

2) If warantee guy can't, or won't, deal with two separate fields, can you export the dept_code to him as a 5 character string? Thus, you could supply the dept code for each line number as a concatenation of the proper dept_code and the conditional 'DG' code. For example, instead of sending only 'DG' for a fuel pump requested in a setr with a dept_code of A7, can you use "A7,DG" for the fuel pump's line number, and "A7" for the shock absorber's line numbers?

3) if you can't manage that for some reason, then I guess you're stuck with your status quo.

Regardless, YOUR db's data structure should reflect reality. Dept_Code and Dangerous Goods warnings are two separate things that should NOT be conflated in one field. You can choose to use one, or the other, or both, in your export field but they should be kept as separate pieces of information.

If you choose to store the dept_code then it belongs in the table SETR.
If you want to classify some parts as 'dangerous goods' then this information belongs in the parts table. A simple yes/no field would suffice to classify a part as a dangerous good or not.

Then, instead of simply exporting the dept_code from the setr table you might use an expression like ...

MyConcatenatedDept_Code: IIf([Parts]![IsDangerous]=-1,Nz([SETR]![Dept_Code],"CODE NOT ENTERED") & ",DG",Nz([SETR]![Dept_Code],"CODE NOT ENTERED") )
...to concatenate the two pices of information

Or

MyDept_Code_OR_DGWarning: IIf([Parts]![IsDangerous]=-1,"DG",Nz([SETR]![Dept_Code],"CODE NOT ENTERED") )

....if you must send one, or the other, but not both

OR

if the warantee guy can use two separate fields, then you just add the IsDangerous field to the export results along with the existing Dept_Code field.

 

[Colin@Toyota]

Hey Craig,

It's been a while since I have had a chance to work on this, as there has been an awful lot going on. I have made some changes, but seem to be stuck here.

In subformSETRModels, embedded in fmSETR, we have decided we want to filter by the VDS (a five-digit code that represents a model's combination of trim level, and powertrain) instead of just year and model name. If you take a look at tblModels, you will see a column labeled VDS which contains each five digit code for every combination of model name and year. For example, a 2008 Tundra has 15 different possible VDSs, but there are 25 in the table because I needed some blanket type VDSs (the ones with asterisks) that would ignore variations in trim, bodystyle or powertrain.

Aaaanyways, what I am trying to do is to filter the VDS by model name, then year, instead of giving the user a list of over 700 possible VDSs. I know this is a fairly simple task, yet it eludes me. Maybe I am just too tired today...

Also, the next subform, subformSETRModelsParts, I would like to be able to enter part numbers in here directly, as opposed to putting them in through another form. Is this as simple as creating a text box, and setting its control source to the corresponding field in the Parts table? Or will I need a query to ensure the table also gets the ModelID (year and name) to record it?

When the Part # is recorded, it takes the ModelID as well, but there are cases where one part number might apply to several models. I can't estimate how often a part number might be duplicated within an SETR, but but do you think it would make more sense to allow duplicates in the part-number field? or create a junction table between the model and parts tables?

I was starting to create a new form for creating SETRs, its called frmSETR, if you want to take a look at the direction I was going. I realize there are some things that won't work out, like using list boxes for multiple selection (each line can only have one model, and one model year), etc.

I'm sure I had more questions, but I can't think of them just now. I appreciate any direction or suggestions you have. Thanks again!

 

[CraigDolphin]

Hi again Colin,

In subformSETRModels, embedded in fmSETR, we have decided we want to filter by the VDS (a five-digit code that represents a model's combination of trim level, and powertrain) instead of just year and model name. If you take a look at tblModels, you will see a column labeled VDS which contains each five digit code for every combination of model name and year. For example, a 2008 Tundra has 15 different possible VDSs, but there are 25 in the table because I needed some blanket type VDSs (the ones with asterisks) that would ignore variations in trim, bodystyle or powertrain

At a quick glance, it seems to me that you need to normalize this new information. Each VDS code may be linked to one, or many modelname -year combinations. Each model name-year combination may be linked to one or many VDS codes. To me that spells two more tables: a VDS code table and a junction table to list model name-year combinations vs VDS code id's. Once you have that, you simply set the row source for the VDS combo box to query the junction table using the model name-year combination (ModelID) that you have selected in the sbfmSETRModels form as a criteria.

Now, if more than one vds code needs to be assigned to a model name-year combination in the SETR, then you need to create a third table to handle the one to many nature of that relationship. This would further necessitate that you need to use a nested subform on sbfmSETRModels to contain the VDS codes for that model name-year combination in that SETR.

Also, the next subform, subformSETRModelsParts, I would like to be able to enter part numbers in here directly, as opposed to putting them in through another form. Is this as simple as creating a text box, and setting its control source to the corresponding field in the Parts table? Or will I need a query to ensure the table also gets the ModelID (year and name) to record it?

A form can only be bound one recordset (table or query) at a time so you cannot enter directly into the parts table (tblParts) from a form that is already bound to a different table (tblSETRModelParts).

However, you can use a variety of ways to add items to the Parts table from your subformSETRModelsParts form. One way would be to use the Not In List event of the combo box which is bound to your PartID field. Or, add a command button to open a popup form in data entry mode, which is bound to your tblParts, then requery the combo after the popup closes.

You will likely need two new forms to handle this task. One to add existing parts to tblModelParts for various model name-year combinations, another to add a totoally new part number to the db (tblParts).

In your case, once a user had a added a brand new part to the db, you will also want to consider prompting the user to also list all the model-year combinations that the new part is used in (to go in tblModelParts) so that the combo box knows to include this part number in the list (since it uses the information in the tblModelParts to filter out any parts that are not associated with the selected model name-year). This is one of those areas I told you about that still needed attending to when I gave you the example db (you did say you wanted to do some of this after all). I have already showed you how to open a popup form for adding new engineers and locations etc.

When the Part # is recorded, it takes the ModelID as well, but there are cases where one part number might apply to several models. I can't estimate how often a part number might be duplicated within an SETR, but but do you think it would make more sense to allow duplicates in the part-number field? or create a junction table between the model and parts tables?

There is already a junction table between the model and parts tables. It's called tblModelParts.

You have to remember that there are three tables here. tblParts which lists individual components of vehicles. tblModelParts which stores information about which parts (PartID) belong with which model name-year combination (ModelID). And the third is tblSETRModelParts which your subform is bound to. This stores information about which parts (PartID) from which model name-year combination (ModelID) are requested by this SETR (SETRID).

No matter how you achieve the data entry, you must store the individual PartID for each ModelID in the SETR as a line in that table. You can add each one manually, or you can create a button to 'Add all Parts' for that ModelID
to run some SQL to append the data en-mass (using the information in tblModelParts to provide the appropriate list). Up to you. Regardless, the data structure should never be de-normalized like you're contemplating.

I was starting to create a new form for creating SETRs, its called frmSETR, if you want to take a look at the direction I was going. I realize there are some things that won't work out, like using list boxes for multiple selection (each line can only have one model, and one model year), etc.

List boxes are not my friend. Have never used one except on the menu of your db (for self education purposes). You can bind a multi-select listbox to a field because you can only store one piece of information in a table. You can, however, use an unbound mutliselect listbox to append rows to a table using vba to create the right SQL. However, I'm not going to be much use to you there since I'm not real familiar with them as I find subforms and command buttons more appropriate for that in most instances. In your case, each modelname-year combination has, potentially, a separate list of parts that might be requested within a SETR. That means the multi-select listbox approach is going to be highly problematic. Not saying someone couldn't make it work...but it's more work than I'd undertake.

As for the form, well, you have three 'boxes' for part numbers. What if there's 10 parts requested for your 2008 Tundra and 7 different parts requested for the 2005 Camry? Three boxes won't work. And three boxes on a form indicates three fields in the underlaying recordsource. You're forgetting the cardinal rule of one-to-many relationships and forms: you need a subform bound to a related table to handle that task.

I keep getting the impression you're looking for shortcuts for data entry. Something like wanting to select several models at once, and several parts at once. But the reality I got from your earlier posts is that, because the parts requested might differ for model name-year combinations within setr, you can't adopt such a simplistic data entry convention because the data model is far more complex. Now, you can automate some of that data entry as I suggested above, but the underlaying data model requires much more of you than your form design would allow. But instead of trying to force several items into one box, you should think about using command buttons to run SQL to append records using information in tblModelParts as a guide. Then allow the user to delete any appened records that they don't want.

Further, I'm not sure what the purpose of the line number command button is. Line numbers are assigned automatically during the export process from the main form. What are you trying to do with this button here?

 

[ajetrumpet]

Hey Craig,

I just read through your last post...

Would you like to get in touch with my publisher about it?

 

[CraigDolphin]

LOL

Thanks Adam. Needed a good laugh.

See? I CAN TOO post a short post.

 

[CraigDolphin]

And now to make a liar of myself from the last post I had an additional thought for Colin.

If your SETR's are going to want to obtain parts based on the trim level (vds) of a vehicle, as well as on model name and year, then you will have a much larger problem as that will necessitate a whole new level of data grouping between tblSETR and tblParts.

If this is the case, then it is probably going to involve rebuilding much of the db to deal with this extra level of information (although the general principles will remain the same). If SETR's do not request parts using trim as a factor (like they do with model name and year) then you'll be ok using the information in the prior novel post.

I sure hope it's the latter

 

[Colin@Toyota]

If your SETR's are going to want to obtain parts based on the trim level (vds) of a vehicle, as well as on model name and year, then you will have a much larger problem as that will necessitate a whole new level of data grouping between tblSETR and tblParts.

The parts are not obtained based on trim level. The only reason I incuded VDS is because the warranty system uses it in the recovery of parts. In the text string that is the requirements for a part recovery, there is only a section for VDS, not model name. The warranty system also uses *'s as wild cards, which is why I have included some partially filled VDSs that will allow the warranty system to grab all parts from that model. There are also VDSs there to allow the collection of parts only from say an AWD model, as opposed to all models, and vice versa.

I am working on fixing a bunch of things, and will post a more relevent bd hopefully by the end of the week.

Craig, thanks for your help.
Adam, thanks for being a smart ass - and if there is any communication with your publisher, I want in. I helped write this novel too!

Cheers

Colin

 

[CraigDolphin]

The parts are not obtained based on trim level.

Well, thank goodness for small mercies

In the text string that is the requirements for a part recovery, there is only a section for VDS, not model name.

I'm not sure what text string you're referring to here.

But are you saying that your exports to the warantee guy needs to list parts by unique combinations of VDS and year, NOT combos of Model Name and year as you originally stated?

If so, unless you can guarantee no more than one VDS code per model name-year combination will EVER be assigned, then you will need to redo tables, queries, and some code too.

Instead of tblModelParts you need tblVDSParts to link specific parts to specific VDS codes. You'll have one line number for each unique combination of VDS and year instead of model name and year. That means things like tblSETRModelParts should really be SETR_VDS_Parts and in tblWebPrtalItems you ought to be storing the VDS codeID instead of the ModelID. You'll need to update all the queries etc to reflect these changes and, where necessary, rewrite the code to work with VDS instead of ModelID.

This would be almost as bad as the other scenario.

This VDS thing really should have been mentioned much earlier in the discussion.

 

[Colin@Toyota]

I'm not sure what text string you're referring to here.

Sorry... I meant the info from the web portal.

But are you saying that your exports to the warantee guy needs to list parts by unique combinations of VDS and year, NOT combos of Model Name and year as you originally stated?

If so, unless you can guarantee no more than one VDS code per model name-year combination will EVER be assigned, then you will need to redo tables, queries, and some code too.

I hereby make the guarantee that no more than one VDS code from a uniqe model name/model year combo will ever be assigned to one SETR. For the most part, the VDSs being used will be the ones with *'s in them. It is a very rare scenario that might ask for a certain VDS, besides to differentiate from FWD and AWD models.

You'll have one line number for each unique combination of VDS and year instead of model name and year.

There's no need. Once these parts recoveries are setup, there will not be any overlap in requested parts. The SETRModelsPartsID is basically a placeholder for the duration of the recovery. This means it doesn't matter if say an AWD VDS is selected, and the part number is for an AWD component, but the partID is connected to all trims of that model (including non-AWD ones).

 

[CraigDolphin]

I hereby make the guarantee that no more than one VDS code from a uniqe model name/model year combo will ever be assigned to one SETR

Sworn and witnessed!

Just remember that the db doesn't know anything about VDS codes when counting up items received versus quantities requested for part numbers for model/year combinations. So FWD and AWD won't factor into those counts at all. If that's cool with you, then great.

(Now Adam has me counting words!)

 

[ajetrumpet]

(Now Adam has me counting words!)

Why!? Have you ever read one of my responses? They are all freakin' novels in their own right!

That's probably why noone here wants to talk to me anymore!

 

[Colin@Toyota]

Question:

If a form/subform can only be bound to a single recordset, what is my best option for using cascading combo boxes in subformSETRModels? I want to be able to select model in one, model year in the next, and VDS last. If (when) I normalize my VDS data, do I just include a field in tblSETRModels that will reference the VDS_ID? Or is there a way to do this by maybe changing the recordset of the subform to a query?

I'm just looking for advice on what the best way to handle this is.

Thanks!

 

[ajetrumpet]

Question:

If a form/subform can only be bound to a single recordset, what is my best option for using cascading combo boxes in subformSETRModels? I want to be able to select model in one, model year in the next, and VDS last.

Colin, if you're looking for some general advice, here are some facts on the issue that may come in handy...



**You can create cascading combos on any form, be it a subform or not, as long as you write the correct (reference) syntax.

**They (cascades) don't have to pull from a single table. They can pull from as many tables as you want, as long as those tables are joined properly, so the records from each can be related (compared) when Access reads the SQL.

**Creating a query to bring all the fields you need (for the combos) together is not necessary, but it may provide some clarity for you. Remember that queried data is still just the source data, linked to its source object, and thus can still be manipulated.

 

[CraigDolphin]

Just to be clear:

A form is bound to one recordset (table or sql)
A subform is bound to a different recordset (table or sql) that is usually related to the recordset of the main form by a key field.
A combobox stores one piece of information into a field in the recordset of the form. However, the items in the dropdown list of the combo can be generated using completely different sql, and different source table(s), than the form uses as long as the value field that you want stored is in the mix.

So, your combo for model name will use sql that queries/shows all the different model names from the tblModelNames table and stores the ModelNameID.

Your combo for model year will look up 'years' from your tblModels table where ModelNameID = cmboModelNameID as a criteria, but stores the ModelID.

Your final combo sql will depend on how you ended up storing the VDS information. Essentially, I remember suggesting that there should be a table for VDS codes (VDS_ID = pk) and another junction table to match ModelID to VDS_ID (ModelVDS_ID = pk)

Your sql for the vds combo will be based primarily on the junction table, but also show the VDSCodeName field from the tblVDS table. You will lookup tblModelVDS!VDS_ID values from the junction table where tblModelVDS!ModelID = cmboModelID, and store the tblModelVDS!VDS_ID field while showing the related tblVDS!VDSCodeName.

 

[Colin@Toyota]

So, your combo for model name will use sql that queries/shows all the different model names from the tblModelNames table and stores the ModelNameID.

Your combo for model year will look up 'years' from your tblModels table where ModelNameID = cmboModelNameID as a criteria, but stores the ModelID.

Your final combo sql will depend on how you ended up storing the VDS information. Essentially, I remember suggesting that there should be a table for VDS codes (VDS_ID = pk) and another junction table to match ModelID to VDS_ID (ModelVDS_ID = pk)

Your sql for the vds combo will be based primarily on the junction table, but also show the VDSCodeName field from the tblVDS table. You will lookup tblModelVDS!VDS_ID values from the junction table where tblModelVDS!ModelID = cmboModelID, and store the tblModelVDS!VDS_ID field while showing the related tblVDS!VDSCodeName.

I understand this in theory, I just cannot seem to make the SQL work. I know that in the first subform I need to include the SETRID to link to the parent form, as well as a link to the VDS junction table.

I also understand how to make the cascading combo boxes work. To help improve my understanding of it I created a temp db to try it out. I can get it to work on this one, but when I apply it to my SETR db it won't. Ideally, I would like to first select the ModelName, then the ModelYear (Since some vehicles like the Yaris have only been around for a couple years where other models go back to 2000), then finally have a filterer down selection of the VDSs that apply to that ModelYear of that ModelName.

Then, in the second subform I don't really need to change anything... I don't think... I still like the way a part number is associated with the ModelID (which is the combination of ModelName and ModelYear), since we are not tracking parts based on VDS (VDS is there simply as a reference for the warranty people).

Here is the most recent version... Once I get this part figured out, I just need to populate the db with the most current info about ongoing SETRs. (and a couple other little things... parts form, new VDS entry form, new engineer form, etc.)

Thank you so much Craig and Adam for all the help.

P.S.
Craig, I am working on the Prius for ya