Parameter Query Question (1 Viewer)

sneuberg

AWF VIP
Local time
Today, 03:15
Joined
Oct 17, 2014
Messages
3,506
I don't understand what roll the Primary OPCD plays in this? What is this? If you know the PFP why is it needed? Also what is the PNC?

If you pick either AT30AA or AT31AA and look in the FRF_WITH_PNC you find 14004 for both of them. If you take 14004 and to the PART_PNC_ADHOC table you get 179 PART_NOs for PNC 14004. All of these PART_NOs have 14002 as the first five digits. So at least in this example specifying the Primary OPCD does nothing for you or is there something I'm not seeing.

I need to get to bed now, but I'll take another look at this tomorrow.
 

casaler

Registered User.
Local time
Today, 05:15
Joined
Jul 15, 2016
Messages
24
It provides another filtered layer to isolate the best FRF match. In this example, take the FRF's from the FRF_WITH_PNC (14004) and drilling down on the FRF_MASTER_NIS table, we are left with fewer options; add the model year, emission and VIN Prefix, and we are down to a couple.
 

sneuberg

AWF VIP
Local time
Today, 03:15
Joined
Oct 17, 2014
Messages
3,506
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.

You would need to determine what would reduce the list of 700 without getting into a chicken-egg scenario. How is this op code related to the the other data. Looking at Query1 one might think that the selection of the op-code could be limited by the selection of the PFP. But the converse is also true which gets us into the chicken-egg thing. If you can't narrow the list anyway I think it would be better than having no validation at all.


I went ahead and wrote the CurrentDB.Execute versions of the of the append and delete queries and tested the speeds of both implementation. The winner is the VBA SQL over the stored queries by miles. The execution times for the VBA version ran from 15 - 24 milliseconds while the stored queries took about 568 - 600 milliseconds to run. I've attached the database has both versions so you can test it for yourself if you'd like.

This actually doesn't say anything about the execution times of stored queries versus VBA SQL. The reason why the stored append queries are so slow is that each one runs Query2 which has all those expressions in it. It's the evaluation of the expressions that is killing the time. So not only does the VBA SQL reduce the clutter and make things clearer but it also runs a lot faster.

One more thing. In the Update_Click code of the frmBuildInfo you open a recordset on the tblBuild table and then add or edit a record, but the frmBuildInfo form is bound to the tblBuild table. So why are you doing what Access does for you? If a form is bound to a table the control values are usually automatically saved to the table and if they are not just add the line
Code:
DoCmd.RunCommand acCmdSaveRecord
 

Attachments

  • GPT_TEST_SPEED_TEST.accdb
    960 KB · Views: 62

Users who are viewing this thread

Top Bottom