displaying only one field in report - but unique situation

[russi]

Hi. Back again with a badly designed number of tables that I inherited.
Anyway...

I have 20 tables each with an ID field and a dozen-or-so text fields. - (I planned on using a master report with sub-reports, but perhaps I am ahead of myself here.)

I need the user to enter the ID and a value which would only appear in one of the dozen+ fields for that record, in that table.
I set up queries with parameters for the ID and the desired text value.
However, when run, I get the correct client record, but also all of the fields for that record... Not only the field with the desired value.

Is there a 'simple' way to do this knowing ho wmany possible fields froma ll of the tavbles would have to be searched? And not all tables have the same field names!

Russ

 

[KKilfoil]

It sounds to me that you have an access db with no proper structure at all.

If it were me, I would redesign the thing, complete with normalization.

It might sound like a lot of work, but I can almost guarantee that the effort will pay for itself many times over in future effort savings.

 

[The_Doc_Man]

Russi, you are going to drive yourself nuts if you don't rework that darned DB. I've watched your posts. OK, how to do what you really wanted...

The only thing that jumps out at me is to write some VBA code that scans the structure of your candidate search tables and dynamically does a DCount for records matching the value you talk about, one field at a time.

This has been described before. It is a nested loop that looks at the TableDefs collection to find tables and in the inner loop, look through the FieldDefs collection to get field names. Then dynamically generate the DCount to find matches to the field you wanted.

To display it, given the open-ended nature of your query, you might wish to display this through an unbound form, or you might wish to use the VBA code to first erase and then update a recordset that contains only the data you wanted. Then display the results through a bound form.

Nicklaus Wirth, the "father of the Pascal programming language," is often quoted as saying "90% of all programming problems are due to poor data design." I tend to agree.

I understand you are looking at something you didn't build. OK, well and good. But if this comes from somewhere else in your company, it is time to call out the blighters and tell them what pain they have inflicted on you. If this comes from another source, perhaps it is worth your while to convert it to something more normalized and never use the original data.

I understand the dilemma - you can bite your tongue and bit the bullet, or you can open your mouth and shoot yourself in the foot. But at what point will you find yourself painted into a corner?