Passing query parameters (in Tables) where the parameter varies (1 Viewer)

[David8]

I know how to do this with Forms. I'm also aware that it is bad practice to use lookup fields in Tables. But I like to learn by exploring, and by experimenting to find out how stuff works. Don't jump to the conclusion I am trying to use this technique for a finished job, I'm not. I'm trying to learn. Basically, PLEASE no 'don't use lookup fields in tables' lecture. I have read that on SO many posts, and I have got that point.

[Totally by-the-way, please don't let this hijack your response, but why do Microsoft offer lookups as fields in Tables, when so many experts are so vociferously against it?]

Anyway, if you do write a Select query in a table field with a WHERE clause, and within the WHERE criteria you reference a parameter, and you keep it numerical, it works.

But if you ask the query to take this parameter from another field in the same table (because this parameter should vary depending on the record), Access won't have it. It gives a parameter entry prompt.

It is as though when you reference another field for the parameter value, Access doesn't understand that the parameter varies, doesn't understand that the user wants it to look at the PARTICULAR RECORD for a PARTICULAR VALUE for the parameter. It seems to want either a general value for the parameter, or to prompt the user every time. Would there be a third way? There may something I don't understand here about how to code queries (there is a lot I don't understand), and if so I'd like to know what.

 

[boblarson]

[Totally by-the-way, please don't let this hijack your response, but why do Microsoft offer lookups as fields in Tables, when so many experts are so vociferously against it?]

That has many an Access MVP wondering as well and there have been many who have kept at them trying to get Microsoft to make things so that they don't circumvent best practices. But they persist.

But if you ask the query to take this parameter from another field in the same table (because this parameter should vary depending on the record), Access won't have it. It gives a parameter entry prompt.

That is because there is no mechanism for dependency upon another field for a value. It is somewhat similar for Validation Rules as you normally can't refer to another field within that rule. You have to use a TABLE LEVEL validation rule to do that.

So the main point is there are no built in mechanisms for doing parameter queries on table fields, which is good because at least that helps with being able to push people to normalization and not using lookups at table level because you can set dependencies (to an extent) on data at the form level. But if a field is dependent upon another within the same record for its value, then it violates the rules of normalization (3NF).

I hope that helps explain it.

 

[gemma-the-husky]

[Totally by-the-way, please don't let this hijack your response, but why do Microsoft offer lookups as fields in Tables, when so many experts are so vociferously against it?]

Its a similar thing, but I learned Pascal years ago, and Borland added loads of features to Turbo Pascal that were not in Niklaus Wirth original spec. These greatly added to the useability of the language, but once you used them, you were "locked in " to Borland. Good or bad?

So - Access is by no means an easy product to use well, and MS seem to be trying to increase the friendliness and useability for lay users.

But most experienced developers prefer to ignore the helpful extensions, as

a) they actually do not enhance useability for complex applications,
b) or the developers have already developed the same functionality.
c) and once you have used them, it limits your ability to migrate to different platforms.

The difference being that a DATABASE is not a proprietary product in the same way that Word or Excel is - its more just an application of a standard paradigm for managing data - and hence you need to have a good reason to depart from the standard.

eg - can anyone confirm what happens to these features if you upsize to SQL.

do you keep the (general pointless) + sign in tables
do you keep the table level lookups
are you able to upsize a DBS if you use table level multi valued fields?