parameter Query to get field

[05me39]

Hello!

I have a table which has 8 fields. The task is to design a query which always shows the first two fields (or any other two specific fields) and any one of the remaining 6 fields based on the input from the user. I am using a simple form with combo boxes to get the choices from the user.

I hope I have made the statement clear. if any confusion kindly ask.
any help is welcome.

 

[Mihail]

From your description, you have a form with 3 columns and you like to select the control source for each one. Yes ????

 

[05me39]

thanks for tye reply.

yes that is the requirement of the problem.

 

[05me39]

as an example of the problem,

I have a patients database, where the patient'S medical reports (4 different reports) are stored with date. I want to be able to draw reports over time ( sugar level over time). I already have designed a query and drawn the trend. however I have to change the query criteria manually from the query design. I want to automate the report generation by using a form. also only one report at a time.

I hope I have made it quite clear.

 

[Mile-O]

Does your database, by any chance, have a field for each type of report? (Sensing a design issue.)

 

[Mihail]

??????????????????????

 

[05me39]

Does your database, by any chance, have a field for each type of report? (Sensing a design issue.)

yes it does have a field for each type of report. I am not well versed with the database design so there could be a design issue.
please tell the solution.

 

[05me39]

??????????????????????

Thanks Mihail, that is what I was looking for. I shall try that with my database. If I come across any issues, I shall share it here. However, I am very interested to know the issue in design of the database as mentioned by Mile-O.

Thanks once again.

 

[Mile-O]

Database tables are used as a kind of modelling, in that you are trying to best shape them to suit the purpose you are trying to achieve. The first thing to consider in your design is the idea of normalisation (for detail, look it up) and getting your tables to satisfy at the very least the notion of third normal form (3NF, look it up too, along with 1NF and 2NF).

Basically, your database design should ensure elements are atomic, dependent on the tables' keys, and non-calculated. Understanding this will come from understanding the normal forms if better explanations are read.

So, effectively, what you want to be doing, is thinking of your tables as representing objects within your project. So, rather than adding six fields to represent different reports, you want to have an actual table for reports, and another table for report types (to make new reports easier to add), one for patients, and one for reports-to-patients.

It may sound like a case of why-have-four-tables-when-I-have-two but a proper design makes it easier to manage and make changes in future. Say you had to add a new report type in future, what are you going to do? Add a new field to your patient table, which would necessitate making changes to your queries, forms, reports, and VBA to facilitate the new field? No. Properly designed, you'll just add a new report type to your report type table and it will be available through your whole database.

 

[05me39]

Thank you Mile-O for the excellent explanation. since your last post I have been thinking about this issue. it (as per your suggestion) sure seems to be a good design but would it not be difficult to have it in user form?
I have a form which collects the data for all the reports. and one form can get data for only one record (row). so, if I add a separate table for report type then how to add all the data conveniently?

 

[Mile-O]

Since I don't know what your form looks like, what data you are capturing, etc. then I'm not sure what to say for now, other than I suspect you may want to be looking at forms with subforms.

 

[05me39]

Since I don't know what your form looks like, what data you are capturing, etc. then I'm not sure what to say for now, other than I suspect you may want to be looking at forms with subforms.

I shall look for that option. Thanks for your suggestion.