Filtering Form Data into a Report

[orbic1]

Hi! This may be a tough one. My knowledge is not sufficient enough to achieve it!

I have a Form linked to a Table. On this form I have several groups of fields seperated by tabs following the format:
GroupA_active (yes/no)
GroupA_notes (text)
GroupA_actions (text)
then on the next tab
GroupB_active (yes/no)
GroupB_notes (text)
GroupB_actions (text)
etc etc

What I am attempting to achieve is:
When the "Generate Report" button is pressed, if groupA_active = yes, then the report needs to include all groupA data. Same goes if groupb_active = yes. There are approximately 20 groups. Obviously there would be too much blank data on the report as only two or three groups would be "active" at any one time. However - all of these groups must exist in one place!

Thanks for any help that you can offer me!

 

[Kraj]

Well...first of all if I don't say it somebody will...

From how you've described your table it seems you may be better served by breaking out the groups into a seperate table. That said...

What I think you can do is to make 20, yes 20, queries; one on each 'group', selecting only the records that are 'active'. These queries should have the corresponding fields in the same order.

Then use a union query to bring the results of all 20 queries together.

BUT....

I am almost certain if you explain what the data is and how you are going to use it, a better solution can be found using multiple tables.

 

[orbic1]

Thanks for that! Right then, each point one by one!

The database is for use in a consultation scenario. Therefore, there are approx 20 different outcomes for any one consultation. Within that, any can be selected dependent upon what the consultation finds. By adding in the "active" check box, when generating data from the consultation, the report isn't bombarded with information.

This is how it works:

Client Selected -> Click on "Add Consultation" -> ID is copied from Client and added to the Consultation ID (for searching multiple consultations for each individual) -> Client talks to Consultant -> Consultant selects "Active" for each condition that is applicable (many may be) and fills out the information -> Consultant hits "Create Report" -> A report is formed only upon which conditions are active as a summary. (This will eventually be automatically exported to Word as a letter)

That's the process for each consultation! The "active" checkbox idea was the only way I could think of filtering the data effectively. Is there another, better way?

Also - could you go into a little more depth on how to implement your previous suggestion?

Many thanks!

 

[Kraj]

OK...here's the deal: First you need a new table. Right now you have 'Client' and 'Consultation' (and I assume 'Consultation' stores the client ID). Add a 'Condition' table. In here you'll have an autonumber ID, and you'll use ConsultationID. You'll also have three additional fields: Group, Notes, and Actions. This table will store the notes and actions for each group ONLY for groups that have data for each instance of Consultation. Make sense so far? The benefits of this system are having normalized tables, no wasted space from empty fields, and most importantly, to report on this data all you will need to do is query Conditions based on the consultation ID or current form and in your report use sorting/grouping by Group. Easy as pie.

Now the tricky part is passing the data from the form to the table. I'm not very good with code so you'll need additional help, but what you'll want is an IF statement that writes the group name, notes, and actions to the Condition table... IF 'Notes' and 'Actions' are not null. This will be a tricky process and it may seem more complicated than what you have now, but I guarantee once it works it will work beautifully and it will work better than the alternative.

While you work on this, I would suggest searching the VBA forum and perhaps starting a thread. Summarize your issue and link to this post. If there's anything else I can help with, just let me know.

 

[orbic1]

Many Thanks

Thank you! This has been of much help! As each criteria is different, this is what I've decided to do:

1) Each criteria of the consultation has now it's own Table and Sub Form, It's ID=Consultation ID
2) A Query is conducted on each where Consultation ID=Condition ID AND Active = True
3) A Report will be generated based upon each of these factors, once I get the VB Code up and running!

Does this sound correct? It does move all the data around a bit! However! Do you know if I need to query each in this manner?

Many thanks once again,

Ed

 

[Kraj]

I'm not sure it is neccessary to have a seperate table for each criteria, but if it works then it works and I'd go with it. You will have to query each table individually, though. But it shouldn't be that bad. Once you have a query on each table, make a final query that pulls all the fields from all the queries, then in SQL view put "UNION" <no quotes> before each "SELECT" and it should create a query that makes one continuous list that you can make the report on. Make sure each query has the same fields in the same order for this to work properly.

 

[orbic1]

Query

Okay - I'm doing what you've suggested. Just a couple of querues - do you suggest when doing each individual query, doing it for just the table data, or linking it to the original query too. Also - with the UNISON SQL statement, each criteria is very slightly different and holds different field names. Any thoughts? I'll let you know if I find a way around it!

Thanks again,

Ed