Can I choose the columns queried from a form?

  • Thread starter Thread starter bobahendrix
  • Start date Start date
B

bobahendrix

Hi,
I have a query w/ many columns, but any of these columns might contain
useful data. I would like to have a form drive the query, such that the user
chooses a number of fieldnames from a few comboboxes, and this constructs the
query such that only those chosen columns are showed in the resulting query
datasheet. I've messed around w/ using FieldList as the rowsource on the
form comboboxes but can't get it to work...can this be done?
thanks!
 
Sounds to me that your table isn't set up properly. If you have columns with
similar data, such as June, July, August or other such series of similar
data, then your table is set up incorrectly and will cause you all kinds of
problems, such as you are experiencing now.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
No, the data is not similar, it all is 'related', and needs to be searchable.
Imagine a Query that is looking at many different tables; an example would
be patients and the various tests that have been run on their blood samples.
As different docs look in the database, each wants to know about a patient
and a few different factors, but these factors are not always the same. But
there are so many tests that the query is cumbersome; you have to scroll
along which medically minded people don't appreciate.
So I need a way to allow the docs to choose which fields show up in the
query, which is why I'm asking if that is possible.
 
It still sounds like you have tables with similar data, one for each test.
You should have a Patient, Test, and junction table. The junction table
would have fields for Patient, Test, DateDrawn, Results, etc.
What are these 'many different tables'?
 
So, it's an inter-departmental database; my department runs 6 different tests
on the blood (call them Test1 - Test6). A completely different department
scores the sample on different criteria. Another department enters
information about the patient. Each different department has their own
table, with the common link being the patient ID#.
So when all fields of all tables are queried, it creates a very long
datasheet, with very long records for each patient.
The 'powers that be' do not want to scroll back and forth through data,
they're lazy.
Nor do they have the skills to create their own queries.
So I was hoping to find a way that the doc can choose which columns of the
'giant' query are shown, with some kind of control on the interface form.
Right now my only option is to create a bunch of queries, each just a few
columns wide, and hope that the docs can find what they need from each one.
 
Ok, then you need to use an Union query to pull the tables together and then
use a select query. The union looks like this --
SELECT [Table 1].[Part Description], [Table 1].QTY
FROM [Table 1]
UNION ALL SELECT [Table 2].[Part Description], [Table 2].QTY
FROM [Table 2];
You would have fields as I said for Patient, Test, DateDrawn, Results, etc.

The select query would have criteria for Patient and between date window for
DateDrawn.
 
Back
Top