Hi, I am casaler (Robert) and self taught in most Office programs. I do have a question (need help on) regarding using a user's input data on a form, and it doesn't match a record in a table, then convert it to "-" which is on the table. Suggestions? If need be, I could provide my database(s). Thanks in advance.
Parameter Query Question (1 Viewer)
- Thread starter casaler
- Start date
MarkK
bit cruncher
- Local time
- Yesterday, 18:03
- Joined
- Mar 17, 2004
- Messages
- 8,181
I would use DCount() to check for the existence of a record.
Code:
If DCount("*", "table", <criteria>) then
[COLOR="Green"] 'the record or records exist[/COLOR]
Else
[COLOR="Green"] 'the record was not found[/COLOR]
End IfIs this close (Query1 result contains the record that I am trying to match on tblBuild which is from the user's input)?
If
(SELECT Query1.VIN, Query1.MDL_OP
FROM Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD]))
= 1 (yes)
Then
Do Nothing
Else
UPDATE Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN SET
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD]));
If
(SELECT Query1.VIN, Query1.MDL_OP
FROM Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD]))
= 1 (yes)Then
Do Nothing
Else
UPDATE Query1 INNER JOIN tblBuild ON Query1.VIN = tblBuild.VIN SET
WHERE (((Query1.MDL_OP)=[tblBuild].[POPCD]));
What if it doesn't match because of a typo in the user's input? Would you still want to convert it to a "-"? I suggest uploading your database with an explanation of where the problem lies so that we can suggest alternatives. Typically the valid values are made available in a combo box. You could design is so that combo box has a "Other" selection that would put the dash in the table if nothing else applied.
See this page for instructions on how to upload.
I upload my data database (GPT_Test) and a mini set of testing info. The goal of the database is to provide a percentage amount due based upon the repair type that is outside of the vehicle warranty coverage. I have started filtering out the possibilities, but I am stuck on how to add the Primary OP Code field; if the op code is "-" or not on the list, then make it "-", if it is, then use it. Query has the results and comes from the FRF_WITH_PNC table, MDL_OP field. Any thoughts, help is appreciated.
This is going to take some time. It's not easy to see what you are doing. Currently I'm going through the code documenting it like:
If you could summarize what this code does it might move things along.
Code:
stDocName = "qrdVINP_T" [COLOR="DarkGreen"] 'clear tblVINP[/COLOR]
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qrdEMTYPE" [COLOR="darkgreen"]'clear tblEMTYPE[/COLOR]
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra1VINP_T" [COLOR="darkgreen"]'Append VIN1: Mid([VIN],5,1) to tblVINP[/COLOR]
DoCmd.OpenQuery stDocName, acViewNormal, acEditIf you could summarize what this code does it might move things along.
All that is doing is decoding the VIN into various parts to query against the Master FRF table; the Emission delete and append is doing the same thing, but for emission data. All the queries work okay. The next section I am trying to incorporate is the model op code; my concern is if a user enters an op code that is not on the list (FRF_With_PNC) associated with the specific FRF code.
As I said before typically an input like the Primary OPCD would be a combo box that had the distinct values that were possible. You can try this if you want. You would:
If you now look in the drop down of the combo box you will see that along with AT31AA and AT30AA an empty string is a possibility. I noticed your system relies on them (empty strings) which isn't good. Since you can't see the difference between a Null and an empty string they will give you all kinds of headaches. I suggest you get rid of them. If you need a placeholder, then use something you can see.
While normally the Primary OPCD combo box would be the distinct values of the MDL_OP field this may not be enough and this appears to be constrained by the trip in Query1 in the circle from Query2.POPCD to FRF_WITH_PNC.MDL_OP then from FRF_WITH_PNC.PNC to PART_PNC_ADHOC.PNC and finally PART_PNC_ADHOC.Part_NO to PFP5. These tables don't have primary keys and I'm not sure what kind of results you can expect.
If you can give us an explanation of these table and their data I think we could be more helpful.
Also after studying this for hours I still don't understand your original question. Where and why would code put a dash in somewhere?
- Open the frmBuildInfo in design view
- Right click on the POPCD combo box
- In the menu click Change to and then Combo Box
- Goto the properties of the POPCD combo box
- In Data tab click on the Row Source and click on the ellipsis on the right
- In the Show Table click on the FRF_WITH_PNC and click Add
- Double click on the MDL_OP to add it as a field
- Switch to the SQL View (Right click on the title frmBuildInfo)
- Add the keyword DISTINCT after SELECT (See SQL Below)
- Close the query and save the form
Code:
SELECT DISTINCT FRF_WITH_PNC.MDL_OP
FROM FRF_WITH_PNC;If you now look in the drop down of the combo box you will see that along with AT31AA and AT30AA an empty string is a possibility. I noticed your system relies on them (empty strings) which isn't good. Since you can't see the difference between a Null and an empty string they will give you all kinds of headaches. I suggest you get rid of them. If you need a placeholder, then use something you can see.
While normally the Primary OPCD combo box would be the distinct values of the MDL_OP field this may not be enough and this appears to be constrained by the trip in Query1 in the circle from Query2.POPCD to FRF_WITH_PNC.MDL_OP then from FRF_WITH_PNC.PNC to PART_PNC_ADHOC.PNC and finally PART_PNC_ADHOC.Part_NO to PFP5. These tables don't have primary keys and I'm not sure what kind of results you can expect.
If you can give us an explanation of these table and their data I think we could be more helpful.
Also after studying this for hours I still don't understand your original question. Where and why would code put a dash in somewhere?
Last edited:
You could eliminate a lot of clutter by executing SQL in code using CurrentDb.Execute rather than doing it in stored queries. Below I show the code for the first four of those statements. I included the others as I already put comments in them that would help you if you want to do this.
I'm really curious as to how all these different parts of a VIN could ever be related to the single field VIN-PFX. Would you share that with us?
I'm really curious as to how all these different parts of a VIN could ever be related to the single field VIN-PFX. Would you share that with us?
Code:
'stDocName = "qrdVINP_T" 'clear tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "Delete tblVINP.VINP FROM tblVINP;", dbFailOnError
'stDocName = "qrdEMTYPE" 'clear tblEMTYPE
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "Delete tblEMTYPE.EMTYPE FROM tblEMTYPE;", dbFailOnError
'stDocName = "qra1VINP_T" 'Append VIN1: Mid([VIN],5,1) to tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "INSERT INTO [tblVINP] (VINP) VALUES ('" & Mid(Me.VIN, 5, 1) & "')", dbFailOnError
'stDocName = "qra2VINP_T" 'Append VIN10: Left([VIN],8) & " " & Mid([VIN],10,2) to tblVINP
'DoCmd.OpenQuery stDocName, acViewNormal, acEdit
CurrentDb.Execute "INSERT INTO [tblVINP] (VINP) VALUES ('" & Left(Me.VIN, 8) & " " & Mid(Me.VIN, 10, 2) & "')", dbFailOnError
stDocName = "qra3VINP_T" 'Append VIN2C: Mid([VIN],4,2) tp tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra4VINP_T" 'Append VIN3: Mid([VIN],5,3) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra5VINP_T" 'VIN3C: Mid([VIN],4,3) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra6VINP_T" 'Append VIN4: Mid([VIN],4,4) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra7VINP_T" 'Append VIN4C: Mid([VIN],4,2) & " " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra8VINP_T" 'Append VIN5A: Left([VIN],5) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra9VINP_T" 'Append VIN5: Left([VIN],4) & " " & Mid([VIN],6,1)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra10VINP_T" 'Append VIN6: Left([VIN],1) & " " & Mid([VIN],5,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra11VINP_T" 'Append VIN9: Left([VIN],7) & " " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra12VINP_T" 'Append VIN10: Left([VIN],8) & " " & Mid([VIN],10,2)
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra13VINP_T" 'Append VIN11: Left([VIN],11) to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qra14VINP_T" 'Append VIN12: "-" to tblVINP
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "qraEMTYPE" 'Append Emmision to EMTYPE in tblEMTYPE
DoCmd.OpenQuery stDocName, acViewNormal, acEditGood suggestion and I will give it (them) a try. Regarding primary keys, you are right and my thought was to add them once it figure out what, which tables I will finally need. Question, by changing the op code to combo box, will that refresh to the query results all the time since they will change based upon the values entered? Yes, the data is full of nulls or blanks and they is why updated the main tables to "-". On the clutter, I thought standard queries would be faster than VBA code.
A primary key has to be unique. For example in the table MDL_YR either field could be the primary key, but in the table PART_PNC_ADHOC none of the fields are unique and without knowing what the data is it's hard to even know what combination of fields are unique. Where does this data come from?
The combo box just allows the selection of the value for the POPCD field of the tblBuild table. Query1 will be based on that value as it gets it from the table. I'm probably not understanding your question. Could you elaborate?
Normally if data is unknown it is left as nulls. If you need to select rows where a certain field is null you can use IS NULL as criteria. If you are try to joining tables on null fields it is very likely that your design is flawed.
Yeah that's what I hear too. They say that stored queries are precompiled and that make them faster, but I wonder if it's worth the clutter. I suspect we are talking milliseconds of time saved. I might finish up the queries in your code and run a test. If I do I'll let you know.
My objective is to find the best coverage item code match and determine how many months &/or miles the vehicle is beyond those values. These items are found on the FRF_MASTER_NIS tables and are associated with the FRF_CD. To determine the FRF_CD, one needs 1) the first 5 characters of the primary failed part (PFP), 2) the PNC (both are on the FRF_WITH_PNC table) and 3) various variables like the model year, emission type, various characters from the VIN, etc. These last items are mainly found on the FRF_MASTER_NIS table and must be queried against to get the appropriate FRF.
My current logic to get this far has been to 1) determine the FRF pool, 2) filter the model year against the model start and end dates (FRF_MASTER_NIS table), 3) decode the VIN, 4) determine the emission type and 5) add in the model op code. I eventually need to incorporate the symptom & diagnostic codes to get the final best match. Also, I need to decide upon 1 FRF with the best months & miles values.
My current logic to get this far has been to 1) determine the FRF pool, 2) filter the model year against the model start and end dates (FRF_MASTER_NIS table), 3) decode the VIN, 4) determine the emission type and 5) add in the model op code. I eventually need to incorporate the symptom & diagnostic codes to get the final best match. Also, I need to decide upon 1 FRF with the best months & miles values.
The combo box just allows the selection of the value for the POPCD field of the tblBuild table. Query1 will be based on that value as it gets it from the table. I'm probably not understanding your question. Could you elaborate? <The tables are a small fraction of their production tables and I was wondering if all (over 700) op codes would be listed or only those that might be involved in determining the best FRF match from the FRF_WITH_PNC table. Ideally, I would only want to see those, if any, op codes that pertain to FRF determination.
Normally if data is unknown it is left as nulls. If you need to select rows where a certain field is null you can use IS NULL as criteria. If you are try to joining tables on null fields it is very likely that your design is flawed. <Agree, but my tables are not from the same source and when I ran NUll or Is NUll queries, I did not get any results. Hence I manually updated them to "-" ( and that wasn't easy either.>
I would love to have a nice relational database, but my tables are from different data sources and the FRF_MASTER_NIS is not a very "normalized" table. But, I can certainly try to add them.
Normally if data is unknown it is left as nulls. If you need to select rows where a certain field is null you can use IS NULL as criteria. If you are try to joining tables on null fields it is very likely that your design is flawed. <Agree, but my tables are not from the same source and when I ran NUll or Is NUll queries, I did not get any results. Hence I manually updated them to "-" ( and that wasn't easy either.>
I would love to have a nice relational database, but my tables are from different data sources and the FRF_MASTER_NIS is not a very "normalized" table. But, I can certainly try to add them.
